Sql Server

SQL | Change Data Capture (CDC)

SQL | Change Data Capture (CDC)

Change Data Capture in Sql Server

What is CDC?
Ans: SQL Server CDC (Change Data Capture) is the process of capturing and recording changes made to the Microsoft SQL Server database. CDC records INSERT, UPDATE, and DELETE operations performed on a source table and then publishes this information to a target table. Microsoft SQL Server CDC (Change Data Capture) is asynchronous by default. This feature is present in SQL Server 2008.
Enable CDC (Change Data Capture) on Databasse: Enable Microsoft SQL Server CDC (Change Data Capture) for the entire database by executing the following query:

USE <db_name>  
EXEC sys.sp_cdc_enable_db  
-- Check that CDC is enabled on the database
SELECT name, is_cdc_enabled
FROM sys.databases WHERE  is_cdc_enabled=1;

After enabling CDC at the database level, we can enable it to track and audit the DML changes on the database tables, by a member of db_owner fixed database role, using the sys.sp_cdc_enable_table system stored procedure, with the ability to enable it to track changes on list of columns specified by the @captured_column_list parameter, and create change tables on a separate filegroup specified by the @filegroup_name parameter,

Enable CDC (Change Data Capture) on a Table:

EXEC sys.sp_cdc_enable_table  
@source_schema = N'dbo',  
@source_name   = N'Employee',  
@role_name     = NULL,  
@filegroup_name = NULL,  
@supports_net_changes = 0

Check that CDC is enabled on that table, we will query the sys.tables DMV for all tables under the current database with CDC enabled

SELECT name, is_tracked_by_cdc
FROM sys.tables WHERE  is_tracked_by_cdc=1;

Once CDC is enabled on the table, a number of system tables will be created under the CDC schema of the database to store the CDC related information. These tables include the following
1. CDC.captured_columns table that contains the list of captured column
2. CDC.change_tables table that contains the list of tables that are enabled for capture
3. CDC.ddl_history table that records the history of all the DDL changes since capture data enabled
4. CDC.index_columns table that contains all the indexes that are associated with change table
5. CDC.lsn_time_mapping table that is used to map the LSN number with the time and finally one change table for each CDC enabled table that is used to capture the DML changes on the source table, as shown below:

And the SQL Agent jobs associated to CDC enabled table, the capture and cleanup jobs, will be created like below:

Disabling CDC
The Change Data Capture can be easily disabled on a specific table using the sys.sp_cdc_disable_table system stored procedure

EXEC sys.sp_cdc_disable_table
@source_schema = N'dbo',  
@source_name   = N'Employee',  
@capture_instance = N'dbo_Employee'

Can disabled completely at the database level, without the need to disable it on CDC enabled tables one by one, using the sys.sp_cdc_disable_db system stored procedure

USE <db_name>  
EXEC sys.sp_cdc_disable_db  

Auditing DML Changes
After enabling CDC on the database table, let us perform the below three database DML changes, INSERT, UPDATE then DELETE, on that table and check how these changes will be audited using CDC feature,

-- Insert new record
(Name, DepartmentName,Salary,Gender,Age,City)
SELECT 'Deepak', 'Full Stack Developer', 200000, 'Male', 47, 'New Delhi'

-- Remove an existing record
DELETE FROM Employee WHERE ID = 1009

-- update an Existing Record
Update Employee set Salary=60000 where id=1008

Check your Microsoft SQL Server CDC (Change Data Capture) table by executing the following query:

SELECT * FROM [cdc].[dbo_Employee_CT] GO

Related Post

About Us

Community of IT Professionals

A Complete IT knowledgebase for any kind of Software Language, Development, Programming, Coding, Designing, Networking, Hardware and Digital Marketing.