How Change Data Capture Can be Used

Introduction
Change Data Capture or CDC is a new feature included in SQL Server 2008. There are several excellent introductionary articles on sql-server-performancesql-server-performance that cover what is CDC, how to configure CDC and where CDC data is captured.

This article discusses how we can use CDC in our day to day wok.

Uses of CDC
There three ways you can use CDC:

1. As your data Auditing feature
2. To monitor DDL changes
3. Load your data warehouse with net changes

Auditing
Auditing is a very important feature to have for your database systems. There are now a lot of legislations in placed around the world such as Data Protection Law, Sarbeny-Oxley.  These are just some of the laws which require you to audit your databases.

The following image shows how to use CDC for auditing in SQL Server 2008.


 
Source: Books On line, SQL Server 2008

After configuring CDC, you can see that you won’t get modified date and time in the CDC tables. If you do a select, you can see that there is a column called _$start_lsn which contains a hex number. LSN stands for Log Sequence Number. If you have a close look at cdc tables, you can see that there is a table called cdc.lsn_time_mapping. This table is used to map between log sequence number (LSN) commit values and the time the transaction committed. Entries may also be logged for which there are no change tables entries. This allows the table to record the completion of LSN processing in periods of low or no change activity.

Rather than querying the CDC tables directly, there are two functions to get relevant data from these tables. sys.fn_cdc_map_lsn_to_time and sys.fn_cdc_map_time_to_lsn are the two functions and these functions will be created in the database upon enabling the CDC.

DECLARE @max_lsn binary(10);
SELECT @max_lsn = MAX(__$start_lsn)
FROM cdc.dbo_Products_CT
SELECT sys.fn_cdc_map_lsn_to_time(@max_lsn);
GO

The above script will return the mapping time for the maximum LSN number for the Products_CT table(Products_CT is the CDC table for Products).

Next, is to include this function in a standard select query.

SELECT sys.fn_cdc_map_lsn_to_time(__$start_lsn) TransactionTime,
__$operation Operatio,
ID,
ProductName ,
Date
 FROM cdc.dbo_Products_CT

The above query will return following results: