Enable Change Data Capture (CDC) in SQL Server

Before you begin

Start the SQL Server Agent.

About this task

Use this procedure to enable CDC manually at the database and table level for Plant Applications configuration changes to take immediate effect.

Note: After you start the SQL Server Agent, enable the CDC, and then restart the cdc-service. The cdc-service is a docker container on the Enterprise setup and can be started using Portainer and on the Standard setup, it is a Windows service.

Procedure

  1. To enable CDC at the database level, do the following:
    • When server is on-premise and user has sysadmin access, then run the following query: exec sys.sp_cdc_enable_db.
    • When server is AWS RDS instance and user has master access, then run the following query: exec msdb.dbo.rds_cdc_enable_db 'database_name'.
  2. When CDC is enabled at the database level, then run the following queries to enable CDC at the table level: 
    EXEC sys.sp_cdc_enable_table @source_schema = N'dbo', @source_name=N'Products_Base', @role_name = NULL, @supports_net_changes = 1
EXEC sys.sp_cdc_enable_table @source_schema = N'dbo', @source_name = N'Prod_Units_Base', @role_name = NULL, @supports_net_changes = 1
EXEC sys.sp_cdc_enable_table @source_schema = N'dbo', @source_name = N'Prod_Lines_Base', @role_name = NULL, @supports_net_changes = 1
EXEC sys.sp_cdc_enable_table @source_schema = N'dbo', @source_name = N'Table_Fields_Values', @role_name = NULL,  @supports_net_changes = 1