Change Data Capture in SQL Server 2008

For audit purposes, if developers want to track all the modifications of tables then it's mandatory to create insert, update and delete triggers for the DML operation. So for tracking purposes Change Data Capture (CDC) is a very asynchronous process. In this feature of DML operations introduced in SQL Server 2008, all change tracking has been automated. It is useful with those tables in SQL Server that have a primary key. Although the asynchronous process has a minimal impact on performance.

It is available only in SQL Server 2008 Enterprise, Developer, and Evaluation editions.

------ Create a table named "Customer" in database "CDC" --------

The Change Data Capture (CDC) Flag is disabled (0) by default for databases and tables, as in the following.

Let's enable the Change Data Capture (CDC) Flag for a database (CDCTrack) on a table (Customer) from the scripts given below.

Once the script above is run, it creates the following jobs, as in the following:

cdc.CDCTrack_capture : When this job is performed, it automatically runs the system Stored Procedure sys.sp_MScdc_capture_job. This procedure determines the scan job parameters and calls sys.sp_cdc_scan. This system SP enables SQL Server Agent, which in fact enables the Change Data Capture feature.

Note: This procedure cannot be executed explicitly when a Change Data Capture log scan operation is already active or when the database is enabled for transactional replication.

cdc.CDCTrack_cleanup : This job performs the system Stored Procedure sys.sp_MScdc_cleanup_job. This is very beneficial as it prevents the change table from growing uncontrollably and enables the cleanup of the database change tables.

When we enable the Change Data Capture (CDC) Flag on a table then seven new tables are created in the system table as in the following.

When we enable Change Data Capture (CDC) Flag on table then some CDC tables are created under the System Tables folder as in the following.

cdc.captured_columns <= where I added the new columns
cdc.change_tables
cdc.dbo_myTable_CT <= table where change data was being captured
cdc.ddl_history
cdc.index_columns
cdc.lsn_time_mapping
dbo.systranschemas

When the Change Data Capture (CDC) Flag is enabled (1) on databases and tables, as in the following.

Once this is tested, we can check the system tables and find a new table named "cdc.dbo_Customer_CT". This table will henceforth contain all the DML changes performed on dbo_Customer_CT table. Once you expand the "Columns" of this table, we will find all the columns from the "Customer" table as well as an extra 5 columns.

As we will see, there are five additional columns to the mirrored original table; they are:

_$start_lsn : Start Log Sequence Number
_$end_lsn : End log Sequence Number
_$seqval : Sequence value used to order row changes within a transaction
_$operation : Contains values corresponding to DML operations. The Value list is as follows:
1. = Delete
2. = Insert
3. = old value
4. = new value
  3,4 = update
_$update_mask : Provides details about the column that was/were updated in inserts, updates, and deletes task.

First SELECT from both tables and see what is in them:

Example of Change Data Capture

Now we will check the CDC feature by doing INSERT, UPDATE and DELETE processes on the "Customer" table.

----insert oprations----

INSERT INTO Customer(ID,Name,[Address])
SELECT 4,'Rahul','faridabad'
UNION
SELECT 5,'riya','chandigarh'
GO

---delete oprations----

DELETE FROM Customer WHERE ID= 2
GO

---update oprations----

UPDATE Customer
SET [Address]='NCR'
WHERE ID= 3
GO

Output

Now let's check the changes in our 2 tables (Customer and cdc.dbo_Customer_CT):

Disabling Change Data Capture on a table following the script.

Disable Change Data Capture Feature on a database and a table then run the following script.

USE [CDCTrack]
GO
EXEC sys.sp_cdc_disable_table
@source_schema = N'dbo',
@source_name = N'Customer',
@capture_instance = N'dbo_Customer';
GO
USE [CDCTrack]
GO
EXEC sys.sp_cdc_disable_db
GO