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 ...