CDC (Capture Data Change)

Working with CDC (Capture Data Change)

•      Innovative new feature in SQL Server 2008.

•      Capture Data Change is similar to Change Tracking but with a major difference that is CDC capture change in every field of your table not only the primary key field.

•      Capture Data Change reads the log file (.ldf) of your table and captures all the changes activity (metadata + data) in a change Table.

How to enable CDC

•      The First step is to enable CDC at database level and then at Table level. In case if you are going to disable CDC then you have to first disable it at table level and the at Database level otherwise you will get a nice error.

•      Run the below query to see how many database having CDC enabled:

•      0-indicates that CDC is not enabled

Enabling CDC on Database.

Let’s enable it by using store Procedure:

Now let us again run the query and see the results.

For the Database My Test it is now enabled. i.e.—1

Enabling CDC on Table:.

First check all the tables as below and we will get to know that CDC in not enabled.

 So let us enable it.

First we will create a new table in My Test Database to track the CDC changes.

CREATE TABLE trackchange(empid INT PRIMARY KEY , NAME VARCHAR(50),age INT, ADDRESS VARCHAR(50))

The store proc is similar to the previous one. So we will execute Sp_cdc_enable_table in order to enable our tables for CDC.

Note

In order to enable CDC on table the SQL server Agent must be running otherwise you will receive a very nice error. i.e. “SqlServerAgent is not currently running so it cannot be notified of this action”

CDC OBJECTS:

 After enabling CDC few jobs and security structure will be established via CDC. To look at the Jobs navigate your SQL Server Agent in the Object Explorer and expand the Job folder and notice the two new jobs which CDC has created for you.

The Capture Job is the one who will read the data from your table’s log file and store all of the data and metadata in the change table i.e. trackchanges

The Cleanup Job runs once daily to clean your old data. You can customize these settings.

In security folder you will see a CDC user and cdc schema as follows:

In system Table we can also see that CDC has created several new tables. The change table for the trackchange table is called cdc.dbo_trackchange_CT as follows:

TRACKING DATA WITH CDC

 Now we will insert 2 records in trackchange table and then we will see the table cdc.dbo_trackchange_CT.

Let see the CDC table:

_$Operation;

1--- means  Delete Operation 

2--- Insert operation

3—value before update statement

4--- value after update statement

Now we are going to update the record in trackchange and we will see the capture data changes.

Now in below figure we can see that data changes have been captured by CDC.

Disabling CDC:

The steps to disable CDC are just reverse of enabling CDC. First we have to disable table level and after that to database level.

Now disable CDC at database as follows: