SQL Server CDC Configuration

What is Change Data Capture Extraction?

Rivery's Change Data Capture (CDC) extraction mode provides a real-time stream of any changes made to the databases and tables configured, eliminating the need to implement and maintain incremental fields or retrieve data via select queries. This mechanism was designed to make the extract, transform, and load processes easier to manage. It also tracks data changes like inserts, deletes, and updates, but it provides more information than SQL Server Change Tracking.

Follow the steps below to enable CDC extraction:

Prerequisites

• Permissions to run CDC stored procedures on the desired database(s) and table(s) for CDC extraction.
• A running instance of a supported SQL Server version and provider (listed below).
• A CDC table must have a Primary Key to be tracked by the CDC mechanism in SQL Server, as required by SQL Server. 
• Rivery does not support the @index_name option.

Compatible SQL Server Versions and Providers:

• Microsoft SQL Server 2016 SP1 or later - Standard or Enterprise editions only.
• Azure SQL Database Managed Instance.
• Azure SQL Virtual Machine.
• AWS RDS SQL Server - Standard or Enterprise editions only.

Configure CDC in your SQL Server

• Make sure the SQL Server Agent is running on the database server:

SQL Server CDC requires that the SQL Server Agent on the database server be in the RUNNING state. It is not always enabled.

Please consult the SQL Server documentation. to determine whether the agent is running and, if not, how to start it.

• Enable the CDC template:

To use the feature in SQL Server, you must first enable CDC tracking in the database.

Rivery must connect to the database as an admin user and execute the following SQL command (change the <MyDB> placeholder to the desired database):

USE <MyDB>
GO
EXEC sys.sp_cdc_enable_db
GO

This enables Rivery to enable CDC logging for each of the desired tables using the  sp_cdc_enable_table  command.

• Enable the CDC template - AWS RDS:

You must first enable CDC in the database before you can use it with your Amazon RDS DB instances.

To enable CDC in the Amazon RDS DB instance, you must be a master user.

After CDC is enabled, any user who is the database's DB owner can enable or disable CDC on the database's tables.

Connect to the database as an admin user and execute the following SQL command (replace the 'database_name'  placeholder with the desired database name):

exec msdb.dbo.rds_cdc_enable_db 'database_name'

This allows Rivery to use the sp_cdc_enable_table command to enable the CDC logging for each of the desired tables.
For more information about AWS RDS CDC on SQL Server, read the appendix on AWS docs.

• Enable CDC for a specific table:

Microsoft SQL Server only supports turning on the CDC for each individual table, so there is no way to turn it on for the entire database at once.
Run the following command (change the <MyDB> to the database enabled above) for each table desired for CDC:

USE <MyDB>
GO
EXEC sys.sp_cdc_enable_table
       @source_schema = N'dbo',     -- The table schema
       @source_name   = N'MyTable',  -- The tracked table name
       @role_name     = N'MyRole', -- Set a role name 
       @supports_net_changes = 0 -- Always 0
GO

supports_net_changes command

@supports_net_changes = 0

Description:

Tracks every individual change to a row. Supports “Hard Delete” in “Change Tracking” mode.

Advantages:

• Detailed change history
• Ensures data integrity
• Better for auditing and compliance
• Aids in debugging and troubleshooting.

Disadvantages:

• Higher data volume
• Increased complexity
• Performance overhead
• Requires more storage.

@supports_net_changes = 1

Description:

Tracks only the net effect of multiple changes to a row. Does not support “Hard Delete” in “Change Tracking” mode.

Advantages:

• Reduced data volume
• Simplified processing
• Improved performance
• Less storage required.

Disadvantages:

• Loss of detailed change history
• Potential data integrity issues
• Limited use in detailed analysis
• Hinders effective debugging.

Check if the CDC enablement was successful:

-- This query result should NOT be empty if CDC was enabled successfully
EXEC sys.sp_cdc_help_change_data_capture;
GO

Configure CDC on SQL Server Read Replica

SQL Server supports CDC in environments with Always-On read-only replicas, but CDC must be enabled on the master node since it cannot run directly on read-only replicas. To configure Change Data Capture (CDC) on a SQL Server read replica, follow these steps:

1. Enable and configure Change Data Capture on the master node.

2. On the read replica, set the database.applicationIntent option to ReadOnly to allow read operations. 

Please note that all CDC processes will still only run on the master node, not on the replica.