Skip to main content
Feedback

SQL Server CDC configuration

Change data capture extraction

Data Integration'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 is used to simplify the extract, transform, and load processes. It also tracks data changes, such as inserts, deletes, and updates, but it provides more information than SQL Server Change Tracking.

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.
  • Data Integration does not support the @index_name option.

Compatible SQL Server versions and providers:

Compatibility Considerations

The CDC feature is currently not available for the following providers or versions:

  • Azure SQL Database (single instance)
  • Azure Synapse Analytics (SQL Data warehouse)
  • Azure Parallel DB
  • AWS RDS Express/Web
  • GCP SQL Server
  • SQL Server Express or Web editions
  • SQL Server prior version 2016 SP1.

Configuring CDC in your SQL Server

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

Before setting up SQL Server CDC, make sure that the SQL Server Agent on the database server is in the RUNNING state. This is disabled by default. Refer to the SQL Server documentation to determine whether the agent is running and how to start it.

Enable the CDC template:

To use the feature in SQL Server, you must enable CDC tracking in the database. Data Integration must have access to connect to the database as an Administrator 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 Data Integration 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 enable CDC in the database before using 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 turn CDC on or off on the database's tables.

  • Connect to the database as an Administrator 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 enables Data Integration 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, refer to the appendix on AWS docs.

Enable CDC for a specific table:

The configuration of CDC in SQL Server should be done for each table to be tracked by the CDC feature. Microsoft SQL Server only supports enabling the CDC for each table, so it is not possible to enable it 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 = 0

Description:

Data Integration’s SQL Server CDC feature requires support_net_changes = 0.

Tracks all changes.

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. You can configure "Change Data Capture (CDC)" on a SQL Server read replica.

  • Enable and configure Change Data Capture on the master node.
  • On the read replica, set the database.applicationIntent option to ReadOnly to allow read operations.
note

All CDC processes only run on the master node, not on the replica.

On this Page