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.
Read white paper of Tuning the Performance of Change Data Capture in SQL Server 2008
Download Script of Change Data Capture (CDC)
Introduction to Change Data Capture (CDC)
Reference: Pinal Dave (https://blog.sqlauthority.com)
5 Comments. Leave new
Hi Pinal,
Its great article,being a developer, i found it got very simple to understand.
i do have one doubt, i don’t whether it makes sense or not. what would be performance impact whether it take more time to insert data that of normal without cdc enabled.
What about Change data capture from Oracle or DB2? Does MS has a plans to support log-based CDC like Goldengate or Wisdomforce and read redo logs directly?
Regards, George
How can we setup CDC to other database ?
Means capture from db1 and put log on db2.
can we put capture log in other database?
Means, I have db1 which tables are audited but log will be in db2.
Hey Pinal,
Will there be any performance impact on the existing source system where the CDC is enabled ?
I have a DW DB of 5-6TB of 999+Tables and want to enable CDC on almost 800+ tables . want to understand the risks involved in tracking so many tables on a DW DB of this size.
Thanks,
J