Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Change Data Capture vs Change Tracking

Make sure to check out the comments on this one!

A short conversation on Twitter Monday night reminded me of this topic. SQL 2008 introduced two new features with similar names and similar-sounding functionality, which are actually quite different. I had learned about these once upon a time and then largely forgotten the details until I took a recent course with Paul Randal (blog | twitter) and Kimberly Tripp (blog | twitter), so I’m guessing others could use a little refresher as well.

So here is a quick taxonomy of Change Data Capture and Change Tracking. I’m going to keep this short, but if you really want all the details, skip to the links at the bottom for complete information.

Change Tracking (CT)

Change Tracking is a synchronous mechanism which modifies change tracking tables as part of ongoing transactions to indicate when a row has been changed. It does not record past or intermediate versions of the data itself, only that a change has occurred. It is recommended to use snapshot isolation with Change Tracking! (See the links below for details on why.)

Change Data Capture (CDC)

Change Data Capture is asynchronous and uses the transaction log in a manner similar to replication. Past values of data are maintained and are made available in change tables by a capture process, managed by the SQL Agent, which regularly scans the T-Log. As with replication, this can prevent re-use of parts of the log.

If you suffer from excessive log reuse waits, you can post a comment on the SQLAgentMan blog and ask for your log back.

Comparing Editions, Methodology, and Notes…

The following recap is largely a paraphrasing of the prior two paragraphs, with some Edition info thrown in:

Feature Change Tracking Change Data Capture
Supported Editions Express, Workgroup, Web, Standard, Enterprise, DataCenter DataCenter, Enterprise
Summary Tracks when a row has changed. Which columns changed can also be tracked. Tracks when data has changed and includes the values as well. Entire table or subset of columns can be captured.
Methodology Synchronous with DML, records change tracking info as part of transaction Asynchronous from Transaction Log
Recommendations Use with snapshot isolation. Note: places some limitations on DML (see links below) Use esp for large scale data warehouse applications. Note: can be used in a DB which is also a replication publisher if need be.

Links/More Information

Comments

No comments.

Leave a Comment

Please register or log in to leave a comment.