Change Data Capture in SQL Server

In this article we will see how to enable Change Data Capture (CDC) in SQL Server for a database and table.

Introduction

Change Data Capture (CDC) captures the data of insert, update and delete activity. When you insert or delete the data in the table it maintains a record for the same data. When you update the data it maintains records for before updating the data and after updating the data.

To understand the change data capture we go through the following process.

Process

process

Step 1: Create DB 

  1. CREATE DATABASE CDC_DEMO  
  2. GO  
Step 2: Create Table

Create one table in the preceding database.

Execute the following query and the "CDC_DEMO_TABLE1" table is created. 

  1. USE CDC_DEMO  
  2. GO  
  3.   
  4. CREATE TABLE CDC_DEMO_TABLE1  
  5. (   
  6.     ID      INT         IDENTITY(1,1) PRIMARY KEY,  
  7.     Name        VARCHAR(50)     NOT NULL,  
  8.     Age     INT         NOT NULL,  
  9. );  
  10. GO  
You can check the table in the Object Explorer. 

object explorer

Step 3: Insert Rows

Insert some rows into the table "CDC_DEMO_TABLE1".

Here we inserted two rows into the table. 

  1. USE CDC_DEMO  
  2. GO  
  3.   
  4. INSERT INTO CDC_DEMO_TABLE1 (Name,Age) VALUES ('Akshay',34)  
  5. GO  
  6. INSERT INTO CDC_DEMO_TABLE1 (Name,Age) VALUES ('Kaushal',38)  
  7. GO  

insert rows

Step 4: Enable CDC on DB

We have a database, table and some rows in the table, now we need to enable CDC on the database.

  1. Execute the following query and it will show whether CDC is enabled or not for the database.
    1. USE CDC_DEMO  
    2. GO  
    3.   
    4. SELECT name, database_id, is_cdc_enabled    
    5. FROM SYS.DATABASES  
    6. WHERE name = 'CDC_DEMO'  
    cdc enabled

    "is_cdc_enabled" has the value "0", that means it is not enabled for the database.

  2. Execute the following query to enable CDC on the database. We need to execute the "sys.sp_cdc_enable_db" Stored Procedure to enable CDC on the database. It is necessary to execute it before we know any tables are enabled for the CDC.
    1. USE CDC_DEMO  
    2. GO  
    3. EXEC sys.sp_cdc_enable_db  
    4. GO  
    This will create some system tables.

    system tables

  3. Check again and verify that CDC is enabled on the database.
    1. USE CDC_DEMO  
    2. GO  
    3.   
    4. SELECT name, database_id, is_cdc_enabled    
    5. FROM SYS.DATABASES  
    6. WHERE name = 'CDC_DEMO'  
    is cdc enabled

    Now "is_cdc_enabled" has the value 1, in other words it is enabled.
Step 5: Enable CDC on Table

Enable CDC on the "CDC_DEMO_TABLE1" table.

  1. Before enabling CDC, we need to check whether it is enabled already or not. Execute the following query and we have a list of all tables with CDC status.
    1. USE CDC_DEMO   
    2. GO   
    3. SELECT [name], is_tracked_by_cdc  FROM SYS.TABLES   
    4. GO    
    cdc demo table

    The value of "is_tracked_by_cdc" is "0" for the "CDC_DEMO_TABLE1" table, in other words CDC is not enabled for this table.

  2. Execute the following query to enable CDC on the table.
    1. USE CDC_DEMO;  
    2. GO  
    3. EXECUTE sys.sp_cdc_enable_table  
    4.   @source_schema = N'dbo'  
    5.   , @source_name = N'CDC_DEMO_TABLE1'  
    6.   , @role_name = NULL  
    7. GO  
    8.           
    We can check in the Object Explorer that one more table is created under the system tables, "cdc.dbo_CDC_DEMO_TABLE1_CT".

    cdc demo table ct

  3. Check again and verify that CDC is enabled on the table.
    1. USE CDC_DEMO   
    2. GO   
    3. SELECT [name], is_tracked_by_cdc  FROM SYS.TABLES   
    4. GO    
    check cdc enabled

    Now "is_tracked_by_cdc" has the value 1, that represents that CDC is enabled for the table.
Step 6: Insert Operation

We have enabled CDC for the database and table. Now let's check where SQL Server persists the change log when we insert the data in the table.

Execute the following query to insert one row into the table. 

  1. USE CDC_DEMO  
  2. GO  
  3.   
  4. INSERT INTO CDC_DEMO_TABLE1 (Name,Age) VALUES ('Jignesh',35)  
  5. GO  
Open the table "CDC_DEMO_TABLE1" and we can see that one row is inserted with the ID 3. 

id3 inserted

The change log is captured in the table "cdc.dbo_CDC_DEMO_TABLE1_CT". You can see the entire row that we have created. One more thing you can observe here is that the _$operation value is 2, in other words for Insert values.

demo table1 ct

Step 7: Update Operation

Now let's check by updating any of the rows in the table. Execute the following script that will update the value of the name field where id = 3.

  1. USE CDC_DEMO  
  2. GO  
  3.   
  4. UPDATE CDC_DEMO_TABLE1  
  5. SET Name = 'Jigi'  
  6. WHERE id = 3  
  7. GO  
Open the table and verify that the value is changed. 

name value changed

Open the "cdc.dbo_CDC_DEMO_TABLE1_CT" table and you can see that the update data is captured in two rows. One is with operation 3 and the other with operation 4. Operation value 3 means before updating and value 4 means after updating.

updated data

Step 8: Delete Operation

To check the captured data after the delete operation, execute the following script that deletes the record with id=3. 

  1. USE CDC_DEMO  
  2. GO  
  3.   
  4. DELETE FROM CDC_DEMO_TABLE1  
  5. WHERE id = 3  
  6. GO  

Open the table and verify that the record is deleted from the table.

record deleted

Open the "cdc.dbo_CDC_DEMO_TABLE1_CT" table and you can see that the deleted row is captured with operation value 1.

deleted row captured

We have seen change data capture for insert, update and delete operations and for those only one system table is used, "cdc.dbo_CDC_DEMO_TABLE1_CT". But there are more than six tables that were created when enabling CDC on the database. So let's see the schema and values for those tables:

  1. Cdc.captured_columns

    Provides the information of columns that are tracked for the changed data capture.

    columns info

  2. Cdc.change_tables

    Provides the information of the table. It shows the default value for "capture_instance" since we have not provided a parameter when enabling CDC on the table.

    capture instance

  3. Cdc.ddl_history

    Provides the information for any schema changes. Currently this table doesn't have any value since we did not change any schema for the table. So let's change the schema and check the values. Execute the following query to change the schema for the table:
    1. USE CDC_DEMO  
    2. GO  
    3.   
    4. ALTER TABLE CDC_DEMO_TABLE1  
    5. ALTER COLUMN Name VARCHAR(100) NOT NULL  
    6. GO  
    We have changed the datatype from varchar(50) to varchar(100) for the name field.

    datatype changed

    Open the "cdc.ddl_history" table and we can see that the ddl_command is captured as in the following:

    ddl command captured

  4. Cdc.index_columns

    Provides the information if any of the index column is changed.

     index column

  5. Cdc.Isn_time_mapping

    Provides the information about the start and end time for the operation done for changes.

    operations

  6. Cdc.systranschemas

    Provides the information for the schema changes.

    schema changes
Step 9: Disable CDC on Table

Execute the following query to disable CDC on the table.

  1. USE CDC_DEMO;  
  2. GO  
  3.   
  4. EXECUTE sys.sp_cdc_disable_table  
  5.     @source_schema = N'dbo',  
  6.     @source_name = N'CDC_DEMO_TABLE1',  
  7.     @capture_instance = N'dbo_CDC_DEMO_TABLE1'  
  8. GO  
We can observe in the Object Explorer that one table is removed under the system tables, "cdc.dbo_CDC_DEMO_TABLE1_CT". That means CDC is disabled for this table.

check cdc disabled

Step 10: Disable CDC on Database

Execute the following query to disable CDC on the database. 

  1. USE CDC_DEMO  
  2. GO  
  3. EXEC sys.sp_cdc_disable_db  
  4. GO   
We can observe in the Object Explorer that all the tables are removed under the system tables. That means CDC is disabled on the database. 

cdc disabled