Change Data Capture in SQL Server

Hello Friends, have you ever thought of tracking the changes that happen to your data in SQL? This can be achieved with different techniques one of them is with SQL triggers but the method that I am going to demonstrate is a bit simple and does not involve SQL triggers. So without wasting time let’s get started.

In this blog, we will see what is change data capture, what are its prerequisites, and how to implement it on SQL Server.

What is change data capture?

SQL Server CDC or 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. Change Data Capturing (CDC) is asynchronous by default. It can be applied when building caches, messaging, search engines, backups, and as part of a larger solution to alleviate system failures.

Prerequisites:

1. To enable CDC functionality for the database you are planning to track, you must have “sysadmin” privileges.

2. You should ensure that you’re running SQL Server Developer, Enterprise, or Standard Edition. CDC functionality is not supported on the Web and Express Editions.

3. Change Data Capture requires that a SQL Server Agent is running on a SQL Server instance.

4. There must be a primary key present for in a table to uniquely identify a record.

Now let’s jump on the actual implementation of CDC.

Step 1:

Open SQL Server management studio and connect database engine with SQL server authentication.

Make sure the SQL Server is running on the instance. You can scroll down in the left section pane (object explorer) where you see your tables and database. If the agent icon has a red spot on its icon that means it is installed but not started. Just right-click on it to open the action box and click on start.

NOTE: The changes we make on tables are tracked by this agent so it is very important to start this agent else tracking is impossible through CDC.

Step 2:

Grant system admin privilege to your user- Type the following command in the query window

EXEC sp_changedbowner ‘sa’ GO

This is a system-defined stored procedure to make your SQL server user admin of the database.

Step 3:

Enable CDC on a database

EXEC sys.sp_cdc_enable_db GO

This is a system-defined stored procedure to enable CDC on a specific table.

To check whether the CDC is enabled or not you can run the following query.

SELECT name, is_cdc_enabled FROM sys.databases

Output:

1 = 1 CDC enabled.

Step 4:

Now since our Database is enabled for CDC let’s implement CDC on a specific table in that particular database.

If you haven’t created a table yet, now is the time :D

drop table if exists users

CREATE TABLE Users

(

ID int NOT NULL PRIMARY KEY,

FirstName varchar(30),

LastName varchar(30),

Email varchar(50)

)

If you have your desired table then skip the above step.

EXEC sys.sp_cdc_enable_table

@source_schema = ‘dbo’,

@source_name = ‘Users’,

@role_name = NULL,

@supports_net_changes = 1

(Source name is your table name)

Note: If you run this query more than once it will throw an error saying CDC already enabled for this table.

Error: Could not create a capture instance because the capture instance name ‘dbo_Users’ already exists in the current database. Specify an explicit unique name for the parameter @capture_instance.

To check whether the CDC is enabled or not you can run the following query.

EXECUTE sys.sp_cdc_help_change_data_capture

@source_schema = ‘dbo’,

@source_name = ‘Users’;

GO

Output:

Step 5:

All the configurations are now done let’s check whether the CDC is working or not on our table by inserting a row in our Users table.

INSERT INTO Users Values (1, ‘Jorge’, ‘Ramos’, ‘ramos@yahoo.com’)

All the tracked records are stored in the system table which is located in the system tables folder with CT as a postfix

Step 6:

To check whether our change is logged into the table perform a select query on the dbo_Users_CT table (If you have a different name do on that)

SELECT * FROM [cdc].[dbo_Users_CT]

Output:

The _$operation column signifies which operation is performed on that record. Here it is 2 which means it was an insert operation.

Operation ID’s

1. Delete

2. Insert

3. Updated row before the change

4. Updated row after the change

Note: Delete operation takes few more seconds to get logged into CT table as compared to update and create operations so be patient

In this way, you can implement the change data capture in the SQL server.