Skip to main content

SqlServer Configuring CDC

Note: SqlServer has supported CDC (Change Data Capture) since version 2008, this article is based on SqlServer 2017.

1. Query SqlServer database version​

SQL: SELECT @@VERSION Result.

2. Query the current user permissions, must be a member of sysadmin fixed server role to allow the CDC (Change Data Capture) feature to be enabled on the database​

SQL: exec sp_helpsrvrolemember 'sysadmin' Result.

3. Query whether the database has enabled CDC (Change Data Capture) function​

SQL: select is_cdc_enabled, name from sys.databases where name = 'tudou' Result.


0: not enabled; 1: enabled

4. Enable CDC (Change Data Capture) for database databases​

SQL.

USE
tudou
    GO  
EXEC sys.sp_cdc_enable_db  
GO

Repeat step 3 to make sure the database is enabled for CDC (Change Data Capture).

5. Query whether the table has enabled CDC (Change Data Capture) function​

SQL: select name,is_tracked_by_cdc from sys.tables where name = 'test'; Result.

0: not enabled; 1: enabled

6. Enable CDC (Change Data Capture) for tables    ​

SQL.

EXEC sys.sp_cdc_enable_table 
@source_schema = 'dbo', 
@source_name = 'test', 
@role_name = NULL, 
@supports_net_changes = 0;

source_schema: the name of the schema where the table is located source_name: the name of the table role_name: the name of the access control role, where null does not set access control supports_net_changes: whether to generate a net change function for the capture instance, 0: no; 1: yes

Repeat step 5 to confirm that the table has CDC (Change Data Capture) enabled.

At this point, the table test starts the CDC (Change Data Capture) function configuration is complete.

7. Verify that the CDC agent is started properly​

EXEC master.dbo.xp_servicecontrol N'QUERYSTATE', N'SQLSERVERAGENT'
If the above status is displayed, you need to start the corresponding agent.

Windows environment operation to enable CDC agent Click on the following location to enable the agent

Restart the database

Check the agent status again to confirm the status change to running

At this point, the table test starts the CDC (change data capture) function configuration is complete.

docker environment operation to turn on CDC agent

Enable agent service for mssql-server_

docker exec -it sqlserver bash
/opt/mssql/bin/mssql-conf set sqlagent.enabled true
docker stop sqlserver
docker start sqlserver

Reference reading: https://docs.microsoft.com/zh-cn/sql/relational-databases/track-changes/enable-and-disable-change-data-capture-sql-server?view=sql-server-2017