SQL Server Change Data Capture (CDC) – Introduction

In the previous part of this series, How to read SQL Server Change Tracking results, we described SQL Server Change Tracking – its features, how to use it, and how to read the results. We also showed examples of the captured records. If you need to know is whether the row has been changed or not, the type of the last change, and which column was changed, without the details (old and new values, who, and when) about the change, then SQL Server Change Tracking is not the right auditing solution for you

In this article, we will present another method for tracking SQL Server data changes – SQL Server Change Data Capture

SQL Server Change Data Capture

Same as SQL Server Change Tracking, SQL Server Change Data Capture was introduced in SQL Server 2008 to make the extract, transform, and load processes easier. It also captures information about data changes – inserts, deletes and updates, but it provides more details than SQL Server Change Tracking. The mechanism used for capturing and the information captured are different

In Change Data Capture, the information is retrieved by periodic querying of the online transaction log. The process is asynchronous. Database performance is not affected; performance overhead is lower than with other solutions (e.g. using triggers)

As Change Data Capture reads committed transactions from the online transaction log, it uses the transaction commit time, so there are no problems in determining the sequence of long-running and overlapping transactions

Change Data Capture is a process that can delay log truncation

“Even if the recovery mode is set to simple recovery the log truncation point will not advance until all the changes that are marked for capture have been gathered by the capture process. If the capture process is not running and there are changes to be gathered, executing CHECKPOINT will not truncate the log.” [1]

SQL Server Change Data Capture requires no schema changes of the existing tables, no columns for timestamps are added to the tracked (source) tables, and no triggers are created. It captures the information and stores it in tables called change tables. For reading the change tables, Change Data Capture provides table-valued functions

Like with Change Tracking, there is a built-in clean-up solution that removes old captured information after a specified time

Both Change Tracking and Change Data Capture can be enabled on the same database at the same time

Change Data Capture vs. Change Tracking features

We will show the difference for historical data tracking in an example. The same changes were tracked using both Change Data Capture and Change Tracking. Let’s say a record was inserted and then deleted. Change Data Capture shows 2 transactions – an insert (the row with _$operation = 2) and a delete (_$operation = 1)

Change Tracking shows only the last change for the record. In this example, the last change is a delete, and that’s the only transaction the Change Tracking feature shows for the row

Another important difference mentioned in the feature comparison table above, are the record values captured for each change

Change Tracking captures only the primary key of the changed row (the AddressID column in this example) and the change type (shown in the ChOp column – U is update, I -insert, D – delete). The AddLine1_Changed and AddLine2_Changed columns indicate whether the specific columns have been changed, 0 shows no changes, otherwise it’s 1. As shown in the example, Change Tracking shows only that specific columns in the specific row were inserted, updated, or deleted, but it doesn’t show what was inserted, updated, or deleted

The ChCrVer column shows the Change Creation Version, i.e. the version number of the initial change, if there was more than one. The version number is incremented after each change. ChVer is the change version number of the last change, the one that is shown

Change Data Capture presents more information for each change. For INSERT (_$operation = 2) and DELETE (_$operation = 1) statements, Change Data Capture captures the whole row that was inserted/deleted. For UPDATE statements, Change Data Capture captures two rows – the row before (_$operation = 3) and the row after (_$operation = 4) the update, so both old and new values are available

In the example below, you can track the update history for the row with AddressID = 16, for all the columns that were changed – AddressLine1 and City. The columns that were not changed are also captured – AddressLine2, StateprovinceID and PostalCode

Change Data Capture is another SQL Server feature for tracking data changes. We’ve shown its similarities and differences with SQL Server Change Tracking.

The Change Data Capture feature shows more details than Change Tracking and the complete history of the changes. It provides the records that were inserted, deleted, or updated. For updates, it shows the old and the new values. It is an asynchronous process, so there is a delay in capturing the changes, unlike with Change Tracking. The information that neither of these two features provide is who, when, and how made a change

In the next part of this series, How to enable and use SQL Server Change Data Capture, we’ll show how to set up Change Data Capture, what database and SQL Server instance objects are added, and how to read the captured results

All articles in this series:

1. What is SQL Server Change Tracking and how to set it up?
2. How to read SQL Server Change Tracking results
3. SQL Server Change Data Capture (CDC) – Introduction
4. How to enable and use SQL Server Change Data Capture
5. How to analyze and read Change Data Capture (CDC) records
6. SQL Server Audit feature – Introduction
7. How to set up and use SQL Server Audit
8. How to analyze and read SQL Server Audit information
9. Auditing SQL Server data changes – the centralized solution

References:

[1]MSDN – About Change Data Capture (SQL Server)
[2]MSDN – Track Data Changes (SQL Server)

Useful resources:

MSDN – Overview of Change Data Capture
MSDN – Change Data Capture and Other SQL Server Features
MSDN – Enable and Disable Change Data Capture (SQL Server)