Change Data Capture - Microsoft Best Practice for capturing data changes?
We have some debate on our development team regarding the best way to capture changes in data in some of our tables. I am very impressed with Change Data Capture and believe it is the best way to capture this sort of data, and that this is what Microsoft designed CDC to do.
For those of you who've had to deal with this sort 开发者_如何学Goof thing, have you found CDC to be the Microsoft best practice way of capturing data changes in tables?
Thanks very much.
It's absolutely the easiest and most efficient; if the alternative is to create triggers and write to audit tables, I'd take CDC any day. It works very well and requires little setup.
Whether it would be the best for your scenario, that would require a little more info.
I've found that setting up and starting CDC is very simple, provides good performance and is flexible enough for my needs. However, it appears to be very complex and slow to query that data to get a resultset detailing the changes made.
I would love to see some different approaches for reporting on this data, anyone care to provide a good sample showing simple results indicating datetime of change, field changed, and old/new values?
CDC can be easily implemented in SQL Server.
SQL Server 2005 & above has features of executing ‘after update’, ‘after insert’ and ‘after delete’ triggers that can be used for data archiving and data capturing without any additional programming.
This link specifies how to implement CDC in SQL Server database.
精彩评论