Setting up Change Data Capture in SQL Server 2008
July 30, 2009[From Builder AU]
Auditing database data is no easy task, but its absolutely required for most industries. In a previous column, I showed how you can use some native SQL Server functionality to do a decent job of capturing data changes.
The inherent problem with capturing these data changes is that it requires triggers on the base table. These triggers can either immediately enter the data into auditing tables or use Service Broker functionality to capture the data at a later time. In either event, the initial capture has to be done in the scope of the original transaction. In SQL Server 2008, the Change Data Capture (CDC) feature allows you to capture data changes much more easily and without the use and overhead of triggers.