SQL Server Change Data Capture also know as CDC, was introduced in SQL Server 2008 version. It is used to capture the changes made to the SQL table.
It provides a solution for SQL Server aduit as it can be easily enabled on SQL Server tables and helps us to keep track of all changes such as Insert, update, or delete that are made to the SQL table also provides historical information about the values before the modification process and provides detailed information about the data modification process.
CDC is available only for Enterprise, Developer, Enerprise Evaluation, and Standard editions of SQL Server. You may encounter with following error on enabling CDC on SQL Server Express Edition.
If you do not know, how check the current installed SQL Server instance then you can use SQL Server configuratio function @@Version tthat returns system and build information for the current installation of SQL Server.
Lets check what instance of SQL Server we are using, and you can see we are using a Developer Edition(64-bit). which supports CDC.
Here e have a table SalesDetails created under database Prod_Db as shown below.
Following are the scripts for creating table and insert some sample data into table.
USE [Prod_Db] GO CREATE TABLE dbo.SalesDetails ( SalesId INT NOT NULL, SalesPerson VARCHAR(50), Product VARCHAR(150), Quantity INT, Price NUMERIC(9,2), [Date] DATE ) INSERT INTO dbo.SalesDetails (SalesId, SalesPerson, Product, Quantity, Price, [Date]) VALUES (1,'Raj Kishor', 'Samsung Tablet', 5, 75000, '12-09-2019'), (2,'Raj Kishor', 'Samsung Tablet', 3, 41000, '08-12-2019'), (3,'Raj Kishor', 'Iphone 5S', 2, 36000, '05-01-2020'), (4,'Suraj Singh', 'Samsung Tablet', 2, 20000, '07-01-2020'), (5,'Suraj Singh', 'Iphone 7', 2, 55000, '12-24-2019'), (6,'Suraj Singh', 'Samsung NoteBook', 2, 35000, '01-25-2020'), (7,'Raj Kishor', 'Samsung Tablet', 6, 85000, '02-11-2020'), (8,'Mahesh Kumar', 'Iphone 6', 2, 49000, '02-21-2020'), (9,'Mahesh Kumar', 'Iphone 5S', 3, 45000, '03-03-2020'), (10,'Mahesh Kumar', 'Iphone 6', 2, 49000, '03-21-2020'), (11,'Raj Kishor', 'Iphone 6', 3, 65000, '03-25-2020'), (12,'Raj Kishor', 'Iphone 5S', 2, 36000, '03-27-2020'), (13,'Mohit Singh', 'Samsung Tablet', 3, 48000, '02-21-2020')
Enabling Change Data Capture (CDC) on DATABASE Level
Lets enable CDC on table SalesDetails to keep track of all changes that are made to SalesDetails table.
Before enable it for table, CDC should be enabled first at the database level.
Lets enable the CDC on database Prod_Db first.
USE [Prod_Db] GO EXEC sys.sp_cdc_enable_db GO
As you can see above command completed successfully. Once you enable CDC on database level, five tables are created inside system tables, usually called as Change data capture tables.
You can see below screenshot showing CDC tables which are created automatically when CDC enabled on Database Level.
Change Data Capture Tables
Lets see what are the purpose of these tables in CDC.
CDC.captured_columns is a table that contains the list of captured column.
CDC.change_tables is a table that contains the list of tables which are enabled for capture.
CDC.ddl_history is a table that keeps the history of all the DDL changes as capture data enabled.
CDC.index_columns is table that contains all the indexes that are associated with change table.
CDC.lsn_time_mapping is a table that is used to map the log sequence number commit values with the time the transaction committed.
You can also verify whether the CDC is enabled on the database using following T-SQL statement.
SELECT name, is_cdc_enabled FROM sys.databases
You can see for database Prod_Db, CDC status =1 that means it is enabled on database Prod_Db.
Enabling Change Data Capture (CDC) on table
Lets enable the CDC on table.
USE [Prod_Db] GO EXEC sys.sp_cdc_enable_table @source_schema = N'dbo', @source_name = N'SalesDetails', @role_name = NULL, @filegroup_name = NULL, @supports_net_changes = 0 GO
Lets check the system folders, you will see a table with format cdc.<capture instance name>_CT is created there.
For us this table name is cdc.dbo_SalesDetails_CT, this table will hold all the changes that are made in SalesDeteails tables.
When the name of the change table is not specified at the time the source table is enabled, the name is derived. The format of the name is cdc.capture_instance_CT where capture_instance is the schema name of the source table and the source table name in the format schema_table.
Table dbo.SalesDetails in the Prod_Db database is enabled for change data capture, so derived change table name would be cdc.dbo_SalesDetails_CT, if not specified.
You can also provided the change table name of your choice at the time you enable cdc on table.
For this you can modify above query by passing one more parameter @capture_instance =<provide name for change table > . But that is not necessary.
USE [Prod_Db] GO EXEC sys.sp_cdc_enable_table @source_schema = N'dbo', @source_name = N'SalesDetails', @capture_instance=<specify name for change table> @role_name = NULL, @filegroup_name = NULL, @supports_net_changes = 0 GO
Also you will see SQL Server Agent jobs such as capture and cleanup jobs associated to CDC enabled table are created as shown below.
After enable CDC on table, Lets perform Insert, update, and Delete operation on Salesdetails table.
INSERT INTO dbo.SalesDetails (SalesId, SalesPerson, Product, Quantity, Price, [Date]) VALUES (14,'Raj Kishor', 'Samsung Tablet', 2, 35000, '12-11-2019') UPDATE dbo.SalesDetails SET Quantity =3 WHERE SalesId =14 DELETE FROM dbo.SalesDetails WHERE SalesId =14
Lets Check the system change table, to seee the track of data changed in SalesDetails.
SELECT * FROM [cdc].[dbo_SalesDetails_CT]
You can see the output of change table, to identifies DML changes in table you can see the [__$operation] value as follows:
- To see the records which are inserted into source table recently, just query change table for [__$operation] value = 2.
- To see the Column data has row values before executing the update statement, just query change table for [__$operation] value = 3.
- To see the Column data has row values after executing the update statement, just query change table for [__$operation] value = 4.
- To see the deleted records, just query change table for [__$operation] value = 1.
----Insert SELECT * FROM [cdc].[dbo_SalesDetails_CT] WHERE [__$operation] = 2 ----Update old Values SELECT * FROM [cdc].[dbo_SalesDetails_CT] WHERE [__$operation] = 3 ----Update new values SELECT * FROM [cdc].[dbo_SalesDetails_CT] WHERE [__$operation] = 4 ----Delete SELECT * FROM [cdc].[dbo_SalesDetails_CT] WHERE [__$operation] = 1
Following are the output that are captured by change table when Insert, update, and delete operation are performed in SalesDetails table.
Lets understand the data captured by change table.
As soon you insert a record in salesDetails table, change table capture the record that you can see in the output returned from change table by first query.
After that you update a quantity value to 3 from 2 for SalesId =14.
As soon record is updated in sales table, a change table capture the two rows one for values that were before updating and second for values after updating values, that you can see in the output returned from change table by second and third query.
In second query output you can see quantity value is still 2Â for SalesId =14 that is an old values before updating the record, while in third query output you can see now quantity value is 3 for SalesId =14 that is new value, a value after updating record in table.
And in the last, fourth query output is when you delete a record for SalesId =14 from table SalesDetails.
Disable SQL Server Change Data Capture
Disable Change Data Capture on table
For Disabling CDC on table, you need capture instance (Schema.table_name), If you do not know then you can use following query to get the caputre instance name.
USE [Prod_Db] GO EXEC sys.sp_cdc_help_change_data_capture GO
You can see, capture instance name is dbo_SalesDetails
Lets disable the CDC on SalesDetails table.
USE [Prod_Db] GO EXEC sys.sp_cdc_disable_table @source_schema = N'dbo', @source_name = N'SalesDetails', @capture_instance = dbo_SalesDetails GO
As you can see, commands completed successfully that means CDC is disabled.
Lets check the capture instance name again by executing same sp again.
USE [Prod_Db] GO EXEC sys.sp_cdc_help_change_data_capture GO
And this time you will see, it returns blank data that means CDC is disabled on table successfully.
You can also verify same, by checking change table inside system tables. You will see change table is deleted from system tables.
Disable Change Data Capture on Database
As you have disabled CDC on table, now you can disable CDC on database as shown below.
USE [Prod_Db] GO EXEC sys.sp_cdc_disable_db
Now you can verify whether CDC is disabled on Database or not, Just check system tables, and you will see remaining five tables are removed from system tables.
Also Read..
