SQL Server Change Data Capture

Introduction

SQL Server Change Data Capture(SQL Server CDC) is a process of capturing changed data when any operation like( INSERT, UPDATE OR DELETE ) occurs in a SQL Table. Here in this blog, we will discuss what is SQL Server CDC and how to enable it using SQL Server Management Studio(SSMS).

Getting Started

The SQL Server CDC is introduced in SQL Server 2008 for tracking changes in data, it works the same as SQL Trigger that introduction in SQL Server 2005, but inside the sql trigger, you have to write extra code for record data.

The CDC allows SQL Server developers to deliver SQL Server data archiving and capturing without any additional programming by recording INSERT, UPDATE, and DELETE activity that is applied to a SQL Server table.

When CDC is enabled on the SQL table, it creates a mirror table with the structure of the same column of the source tables and records detailed information about the change on the database table when any operation ( INSERT, UPDATE, and DELETE) happens.

Enable CDC SQL Server

There are two steps to enable CDC in the sql server, the first step is to enable CDS in the database label and the second step is to enable in Table label. The below two examples describes how to enable or disable CDS in the SQL server in the database and table label.

Enable Change Data Capture for a Databas

 -- ====   
 -- Enable Database for CDC template    
 -- ====   
 USE MyDB   
 GO   
 EXEC sys.sp_cdc_enable_db   
 GO  
 -- =======   

Enable Change Data Capture for a Table

 -- =========   
 -- Enable a Table Without Using a Gating Role template    
 -- =========   
 USE MyDB   
 GO   
 EXEC sys.sp_cdc_enable_table   
 @source_schema = N'dbo',   
 @source_name  = N'MyTable',   
 @role_name   = NULL,   
 @supports_net_changes = 1   
 GO  

Disable CDC SQL Server

Like enable CDC, you can disable the CDC SQL Server in both database label and table label, these below example describes how to disable CDS SQL in using SQL Query.

Disable Change Data Capture for a Databas

 -- Disable Database for Change Data Capture template    
 -- =======   
 USE MyDB   
 GO   
 EXEC sys.sp_cdc_disable_db   
 GO  

Disable Change Data Capture for a Table

 -- =====   
 -- Disable a Capture Instance for a Table template    
 -- =====   
 USE MyDB   
 GO   
 EXEC sys.sp_cdc_disable_table   
 @source_schema = N'dbo',   
 @source_name  = N'MyTable',   
 @capture_instance = N'dbo_MyTable'   
 GO  

Summary

SQL Server CDC is required, when old record requires for tracking the changes in database, we discussed here what is CDC and how to enable the CDC. I hope you have enjoyed it a lot.

Thanks