Change Data Capture In SQL Server

Overview

As a part of any organization, we always need a tracking mechanism to track the changes in the respective tables and changes can be an inserted, updated or deleted in the database. Yes, it can be done by coding methods also, by inserting log files and keeping the log files on the Server, and you can track the necessary changes taking place in the database.

When you see on the DB SQL Server side, there is a change in the data capture mechanism where you can specify the tables which you want to keep a track of. Due to which we are able to see and  cross check  which process is happening. So let’s start. 

Introduction

This mechanism was introduced in SQL Server 2005 and higher, but there were some drawbacks in CDC environments as these drawbacks are being taken care of; from SQL Server 2008 and so on.

Just consider, when you enable Change Data Capture (CDC) on the database, it creates tables under the system tables and with the same structure of the original table source.

Any DML (Data Manipulation Language ) existing on the database SQL Server, Change Data Capture reads a SQL Server transaction log and captures it in their tables i.e. CDC tables .

Let’s Start

  • Open SSMS

    SSMS

On Master, type a query

  • SELECT * FROM sys.sysdatabases

    query

Here, you will get an output which includes DBname, dbit,Path, creation date and so on.

  • Let’s see on which database CDC is enabled.

  • Type command 

    SELECT name,database_id,is_cdc_enabled from sys.databases.

    Command

You will get an output and see the list of the databases in which you see the “is_cdc_enabled ” column and you will see LDAPAuth database value is 1, which means CDC is enabled .

So let’s take some other database for CDC testing

  • We will see the command to enable CDC,

    command

    command
    1. USE BloodBankManagement  
    2. GO  
    3. EXEC sp_cdc_enable_db  
    4. GO  
  • To disable CDC on the database, the following operation needs to be performed:

    command
    1. USE BloodBankManagement  
    2. GO  
    3. EXEC sp_cdc_disable_db  
  • Let’s check in the database, whether CDC is enabled or not

    Go to BloodBankManagement -> Tables -> System Tables

    You will see the screenshot, shown below:

    tables

Tables are created and are listed below:

  • Cdc.captured_columns- It captures any columns from the original source tables.
  • Cdc.change_tables- To see, what change is happening, DML changes in the original source table.
  • Cdc.ddl_history- It contains DDL changes
  • Cdc.index_columns- It contains index changes.
  • Cdc.lsn_time_mapping- It maps the LSN number LSN is nothing nut an LAST sequence number . SQL server generates ab unique LSN number- It is used for each transactional log .
  • Now let’s see how to enable CDC on the table, in order to track the changes.

Create table

  1. CREATE TABLE TESTCDC (ID INT IDENTITY(1,1) PRIMARY KEY,  
  2. NAME VARCHAR(10) NOT NULL,  
  3. ADDRESS VARCHAR(20) NULL,  
  4. CITY VARCHAR(20) NULL);  
create 
  • Now, let’s insert some values.
    1. INSERT INTO TESTCDC(NAME,ADDRESS,CITY)  
    2. VALUES('AK','xyz','MUM')  
    insert

  • Insert a minimum of 10 records.

    Insert

  • Let’s check the data.

    Data

  • Let's enable CDC on the table.
    1. EXEC sp_cdc_enable_table  
    2. @source_schema=N'dbo',  
    3. @source_name=N'TESTCDC',  
    4. @role_name=NULL  
    table

You will see the output

  • Refresh the system table view.

    view

You will see

table

  • CDC on the table is enabled, as you can see in the highlighted section.

  • Select the CDC table.

    table

You will see no data. Now, let's update a row and see whether it captures or not.

  • Update a Value

    Update

  • Select CDC Table

  • You will see

    code

  • Now, let’s differentiate between New Value and Old Value.

Refer to the screenshot shown below, as you can see _$operation field,

  • 3 denoted that it is before the update and Name column contains Value zz.
  • 4 denotes after the update and updated value is SS in Name column.

    value

Conclusion

This was CDC capturing data in SQL Server and keeping track. I hope you found  this article helpful. If you have any doubts related to this article, feel free to ask.

Up Next
    Ebook Download
    View all
    Learn
    View all