Change data capture (CDC) is a new feature in SQL Server 2008 designed to capture insert, update, merge, and delete activities applied to SQL Server tables and to avail those changes in an easy-to-understand format.
Conventionally, detecting changes in a source database to transfer these changes to a data warehouse required any of the following:
- Special columns in the source tables (time stamps, row versions).
- Triggers that capture changes.
- Comparison of the source and the destination systems.
The above methods can have significant disadvantages: special columns require a change in the source database schema, and in many cases, a change in the application logic. Triggers must be implemented manually, and this can lead to significant additional overhead on DML commands to the source system. Comparing the source and target databases can also place a heavy load on both systems.
CDC offers a win-win alternative that allows the transfer of changes without forcing the DBA to either place a heavy load on the server or add the above change-detection steps. CDC enables the capture of changes by asynchronously reading the transaction log of the source database and then recording the changes in those logs in special change tables for uploading later into the target database. Therefore, no comparison between the source and target systems is needed for changes.
Download Script of Change Data Capture (CDC)
Reference: Pinal Dave (http://blog.sqlauthority.com)