How Do I Track Changes with Change Data Capture in SQL?

Answer

If you need to monitor changes to tables in SQL Server you can make use of a feature called Change Data Capture (CDC) which is currently available in SQL Server 2008, SQL Server 2008 R2, and SQL Server 2012.

Following are some excerpts from the CDC MSDN page:

What does CDC do?

“Change data capture records insert, update, and delete activityA specific step in a process.  It will be the user interface for that step (web form or a screen), for example, the Leave Application (screen interface or web form) will be the first Activity in the Leave Requisition process.  The Leave Application Authorization will be the second step or Activity in the Leave Requisition process.  One Process can have multiple activities. that is applied to a SQL Server table. This makes the details of the changes available in an easily consumed relational format.”

How do you consume this information?

“Table-valued functions are provided to allow systematic access to the change data by consumers.”

“The functionIs a Stream Objects that performs mathematical and statistical operations. that is used to query for all changes is named by prepending fn_cdc_get_all_changes_ to the capture instance name.”

How do I get started?

1) “…change data capture must be explicitly enabled for the database. This is done by using the stored procedure sys.sp_cdc_enable_db.”

USE AdventureWorks2012;
GO
EXECUTE sys.sp_cdc_enable_db;
GO

2) “When the database is enabled, source tables can be identified as tracked tables by using the stored procedure sys.sp_cdc_enable_table. When a table is enabled for change data capture, an associated capture instance is created to support the dissemination of the change data in the source table.

USE AdventureWorks2012;
GO
EXECUTE sys.sp_cdc_enable_table
@source_schema = N'HR'
, @source_name = N'Employee'
, @role_name = N'cdc_Admin';
GO

3) Now you can access information about changes to your table by using the function fn_cdc_get_all_changes_HR_Employee

USE AdventureWorks2012;
GO
DECLARE @from_lsn binary(10), @to_lsn binary(10)
SET @from_lsn = sys.fn_cdc_get_min_lsn('HR_Employee')
SET @to_lsn   = sys.fn_cdc_get_max_lsn()
SELECT * FROM cdc.fn_cdc_get_all_changes_HR_Employee(@from_lsn, @to_lsn, N'all');
GO

“The first five columns of a change data capture change table are metadata columns.

These provide additional information that is relevant to the recorded change.

The remaining columns mirror the identified captured columns from the source table in name and, typically, in type.

These columns hold the captured column data that is gathered from the source table.

Each insert or delete operation that is applied to a source table appears as a single row within the change table.

The data columns of the row that results from an insert operation contain the column values after the insert.

The data columns of the row that results from a delete operation contain the column values before the delete.

An update operation requires one row entry to identify the column values before the update, and a second row entry to identify the column values after the update.

Each row in a change table also contains additional metadata to allow interpretation of the change activity.

The column __$start_lsn identifies the commit log sequence number (LSN) that was assigned to the change. The commit LSN both identifies changes that were committed within the same transaction, and orders those transactions.

The column __$seqval can be used to order more changes that occur in the same transaction. The column __$operation records the operation that is associated with the change: 1 = delete, 2 = insert, 3 = update (before image), and 4 = update (after image).

The column __$update_mask is a variable bit mask with one defined bit for each captured column.

For insert and delete entries, the update mask will always have all bits set. Update rows, however, will only have those bits set that correspond to changed columns.”