Post
Ask Question

Change Data Capture in SQL Server

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 of the same data. When you update the data it maintains records 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 

CREATE DATABASE CDC_DEMO  
GO  

Step 2. Create a Table

Create one table in the preceding database.

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

USE CDC_DEMO  
GO  
  
CREATE TABLE CDC_DEMO_TABLE1  
(   
    ID      INT         IDENTITY(1,1) PRIMARY KEY,  
    Name        VARCHAR(50)     NOT NULL,  
    Age     INT         NOT NULL,  
);  
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. 

USE CDC_DEMO  
GO  
  
INSERT INTO CDC_DEMO_TABLE1 (Name,Age) VALUES ('Akshay',34)  
GO  
INSERT INTO CDC_DEMO_TABLE1 (Name,Age) VALUES ('Kaushal',38)  
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.

    USE CDC_DEMO  
GO  
  
SELECT name, database_id, is_cdc_enabled    
FROM SYS.DATABASES  
WHERE name = 'CDC_DEMO'  

    cdc enabled

    "is_cdc_enabled" has the value "0", which 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.

    USE CDC_DEMO  
GO  
EXEC sys.sp_cdc_enable_db  
GO  

    This will create some system tables.

    system tables

  3. Check again and verify that CDC is enabled on the database.

    USE CDC_DEMO  
GO  
  
SELECT name, database_id, is_cdc_enabled    
FROM SYS.DATABASES  
WHERE name = 'CDC_DEMO'  

    is cdc enabled

  4. 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.

    USE CDC_DEMO   
GO   
SELECT [name], is_tracked_by_cdc  FROM SYS.TABLES   
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. 
    USE CDC_DEMO;  
GO  
EXECUTE sys.sp_cdc_enable_table  
  @source_schema = N'dbo'  
  , @source_name = N'CDC_DEMO_TABLE1'  
  , @role_name = NULL  
GO  
          

    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.

    USE CDC_DEMO   
GO   
SELECT [name], is_tracked_by_cdc  FROM SYS.TABLES   
GO    

    check cdc enabled

    Now "is_tracked_by_cdc" has the value 1, which 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 in the change log when we insert the data in the table.

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

USE CDC_DEMO  
GO  
  
INSERT INTO CDC_DEMO_TABLE1 (Name,Age) VALUES ('Jignesh',35)  
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.

USE CDC_DEMO  
GO  
  
UPDATE CDC_DEMO_TABLE1  
SET Name = 'Jigi'  
WHERE id = 3  
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 updated 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. 

USE CDC_DEMO  
GO  
  
DELETE FROM CDC_DEMO_TABLE1  
WHERE id = 3  
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 a change in 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 in 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:

    USE CDC_DEMO  
GO  
  
ALTER TABLE CDC_DEMO_TABLE1  
ALTER COLUMN Name VARCHAR(100) NOT NULL  
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 columns are changed. 

    index column 

  5. Cdc.Isn_time_mapping

    Provides 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.

USE CDC_DEMO;  
GO  
  
EXECUTE sys.sp_cdc_disable_table  
    @source_schema = N'dbo',  
    @source_name = N'CDC_DEMO_TABLE1',  
    @capture_instance = N'dbo_CDC_DEMO_TABLE1'  
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.  

USE CDC_DEMO  
GO  
EXEC sys.sp_cdc_disable_db  
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

Conclusion

This article taught us how to enable Change Data Capture (CDC) in SQL Server for a database and table.


Similar Articles