Change data capture (CDC) in sql server

CDC helps to track insert, update and delete activity on table’s data.

We can enable CDC in two levels.

Database level

Table level

When we enabling the CDC it create CDC schema, CDC User, Meta data tables and other objects (system stored procedure and jobs).

Step by Step enabling CDC on Database level

1. Open SSMS.
2. I am applying CDC on Test database.
3. Open the sql query editor.

EXEC sys.sp_cdc_enable_db

Run the stored procedure.

Once it executed successfully. You we get the message like command completed successfully.

Now CDC is enabled on the “Test” database.

Once CDC enabled on Database it create some tables on System tables.

Ø  Cdc.captured_column

Ø  Cdc.change_table

Ø  Cdc_ddl_history

Ø  Cdc.index_columns

Ø  Cdc.Isn_time_mapping

Ø  Dbo.systransschemas

And it also creates some system stored procedure.

Step by Step to enable on table.

For enabling CDC on table level we use below stored procedure

Exec sys.sp_cdc_enable_table @source_schema=N'dbo', @source_name=N'Table_Name', @role_name=NULL

# I am enabling CDC on emp table in Test database.

Execute the above sp.

It create a table on System table

cdc.dbo_emp_CT

Stored procedure also create sql server Agent job.

Ø  cdc.Test_capture

Ø  cdc.test_cleanup

Now we enable CDC on both level database as well as Table level.

Now it’s time to test.

Insert Operation

In Emp table now I have two records.

Now I am inserting one record on this table.

INSERT INTO EMP ([EmpID],[Name],[Address],[Mobile])   VALUES ('3','Kumar','Pune','8888802459')

Now we can see that in cdc.dbo_emp_CT one record inserted.

Update Operation

Now I am updating one record. I am changing the address of Kumar pune to Mumbai.

You are seeing two records. One is having old value and second cone having updated records.

Delete operation

Now I am deleting one record from the table.

See deleted record store on 4^th row.

Now you are seeing the different value on  _$operation column. It show the operation which is performed on table.

Number	operation
1	Delete statement
2	Insert statement
3	Value before update statement
4	Value after update statement

cdc.captured_columns Table

 It store the information columns of the table on which we enable CDC.

SELECT [object_id]       ,[column_name]       ,[column_id]       ,[column_type]       ,[column_ordinal]       ,[is_computed]   FROM [Test].[cdc].[captured_columns]

cdc. change_tables

It store the name of table (list of table name) on which we enables the CDC.

SELECT [object_id]       ,[version]       ,[source_object_id]       ,[capture_instance]       ,[start_lsn]       ,[end_lsn]       ,[supports_net_changes]       ,[has_drop_pending]       ,[role_name]       ,[index_name]       ,[filegroup_name]       ,[create_date]       ,[partition_switch]   FROM [Test].[cdc].[change_tables]

Hope this will help you. Thanks!!!