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
- I really meant it above! This Technet article by Paul Randal is your friend: Tracking Changes in Your Enterprise Database
- Tuning the Performance of Change Data Capture in SQL 2008, SQL Server Best Practices Article by contributors Sanjay Mishra, Gopal Ashok, Greg Yvkoff, Rui Wang.
- Basics of Change Data Capture from BOL – note the “Handling Changes to Source Tables” section.
- How Change Tracking Handles Changes to a Database from BOL
- Comparing Change Data Capture and Change Tracking from BOL
