SQL Server Log-Based Configuration
  • 31 Oct 2021
  • 2 Minutes to read
  • Dark
    Light
  • PDF

SQL Server Log-Based Configuration

  • Dark
    Light
  • PDF

Prerequisites

  • Permissions to execute CDC stored procedures upon the database(s) and table(s) desired for log-based extraction.
  • Running instance of a supported SQL Server version and provider (listed below).

Compatible SQL Server Versions and Providers:

Compatibility Considerations
Please be noticed that current the CDC feature is not available for the following providers or versions:
  • Azure SQL Database (single instance)
  • Azure Synapse Analytics  (SQL Datawarehouse)
  • Azure Parallel DB
  • AWS RDS Express/Web
  • GCP SQL Server
  • SQL Server Express or Web editions
  • SQL Server prior version 2016 SP1 

Configure CDC in your SQL Server

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

SQL Server CDC requires the SQL Server Agent to be in a RUNNING state on the database server. In some cases, it isn't running by default.
In order to check if the agent is running, and start it if it isn't, please refer to the next SQL Server's documentation.

Enabling the CDC template

In order to use the feature in SQL Server,  CDC tracking must first be enabled in the database.
Rivery needs to access the database using an admin user, and run this SQL command (change the <MyDB> placeholder to the desired database):

USE <MyDB>
GO
EXEC sys.sp_cdc_enable_db
GO

This allows Rivery to use the sp_cdc_enable_table command to enable the CDC logging for each of the desired tables.

Enabling the CDC template - AWS RDS

Before you use CDC with your Amazon RDS DB instances, you will need to enable it in the database.
You must have master user privileges to enable CDC in the Amazon RDS DB instance.
After CDC is enabled, any user who is db_owner of that database can enable or disable CDC on tables in that database.
Connect to the database using an admin user, and run this SQL command (change the 'database_name' placeholder to the desired database):

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.

Enabling CDC for a specific table

Configuring CDC in SQL Server Per table
Please note that the configuration of CDC in SQL Server below should be done for each table that is desired to be tracked by the CDC feature.

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

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 can use the change data capture (CDC) on Always-On read-only replica. However, it is not enabled by default at the server level. Therefore, there is a need to set the server configuration:

  • Change data capture is configured and enabled on the master node. SQL Server does not support CDC directly on replicas.
  • The configuration option database.applicationIntent must be set to ReadOnly. This is required by SQL Server. 



Was this article helpful?