SQL SERVER – Tuning the Performance of Change Data Capture in SQL Server 2008

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 (http://blog.sqlauthority.com)

About these ads

6 thoughts on “SQL SERVER – Tuning the Performance of Change Data Capture in SQL Server 2008

  1. 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.

    Like

  2. 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

    Like

  3. Pingback: SQL SERVER – A Quick Look at Logging and Ideas around Logging « SQL Server Journey with SQL Authority

  4. Pingback: SQLAuthority News – Birds-of-a-Feather (BOF) Lunch – SQL PASS Summit, Seattle, 2009 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s