This may be an old topic for many who worked with SQL Server, but, As I was doing a proof of concept on CDC recently for one of my projects, thought of sharing about CDC for those who is yet to explore this feature in SQL Server.
What is Change Data Capture?
Change Data Capture or CDC in SQL Server is introduced to track the DML changes and records insert/update/delete operations in respective change tables. The source of truth is Transaction Logs in SQL Server. Capture process jobs will be picking up the required table information from the Transaction Log and records in the Change tables. As these jobs are asynchronous, you may see a latency ideally between the commit and population of change tables. However, this has a performance advantages compared to Trigger based solutions. Similarly there is another clean up job to prune the data (older) from the change tables.
Supporting Editions: Enterprise/Developer/Standard.
How do we implement CDC on a database?
Step 1: : Let us create a database and table to see the proof of Concept
--Create a Test Database Create Database CDCORigin; --Create a Table create Table TestCDC(FirstName Varchar(100),SecondName Varchar(100)) --Insert values into tables Insert into TestCDC Values('Ron','David'),('Chirs','Martin')
Step 1: : Enable CDC (Need two steps as below)
EXEC sys.sp_cdc_enable_db EXEC sys.sp_cdc_enable_table @source_schema = N'dbo', @source_name = N'TestCDC', @role_name = NULL EXEC sys.sp_cdc_add_job @job_type = N'capture'; EXEC sys.sp_cdc_add_job @job_type = N'cleanup';
Step 3: : Verification of CDC configuration
Once, CDC has set up, we can see the CDC specific tables and Changes tables in Object explorer as below.
In addition, we can also verify the data changes using below scripts.
--Check the CDC tables Select * From [cdc].[dbo_TestCDC_CT] --No data Insert into TestCDC Values('Reddick','Pitt') --Check the CDC tables Select * From [cdc].[dbo_TestCDC_CT] Update TestCDC Set SecondName = 'Pi' Where FirstName='Chirs' --Check the CDC tables Select * From [cdc].[dbo_TestCDC_CT] Delete From TestCDC Where FirstName='Ron' --Check the CDC tables Select * From [cdc].[dbo_TestCDC_CT]
Hope you enjoyed this post, and I am excited to write more about CDC with my real client experience in next posts. Until then, happy and continuous learning!