SQL Server Log-Based Configuration

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:

• 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 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

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.