Microsoft SQL Server 2012 Bible

Working with Change Data Capture

It isn't difficult to work with change data capture. The trick is to understand the transaction log's log sequence numbers.

Assuming AdventureWorks2012 has been freshly installed, the following scripts make some data changes, so there will be some activity in the log for CDC to gather:

INSERT HumanResources.Department (Name, GroupName) 
 VALUES ('CDC New Row', ‘SQL Rocks'),
     ('Test Two' , ‘CDC Rocks ‘);   

UPDATE HumanResources.Department         
 SET Name = ‘Changed Name'
 WHERE Name = ‘CDC New Row';

INSERT HumanResources.Department (Name, GroupName) 
 VALUES ('Row Three', ‘PBM Rocks'),
     ('Row Four' , ‘TVP Rocks');

UPDATE HumanResources.Department
 SET GroupName = ‘T-SQL Rocks'
 WHERE Name = ‘Test Two';

DELETE FROM HumanResources.Department
 WHERE Name = ‘Row Four';

With five transactions complete, there should be some activity in the log. The following DMVs can reveal information about the log:

SELECT *
 FROM sys.dm_cdc_log_scan_sessions 

SELECT *
 FROM sys.dm_repl_traninfo

SELECT *
 FROM sys.dm_cdc_errors

Examining the Log Sequence Numbers

The data changes are organized in the change tables by log sequence number (LSN). Converting a given date time to LSN is essential to working with CDC. The sys.fn_cdc_map_time_to_lsn function is designed to do just that. The first parameter defines the LSN search (called LSN boundary options), and the second parameter is the point in time. Possible searches are as follows:

• Smallest greater than
• Smallest greater than or equal
• Largest ...