SQL Server Log-Based Configuration

What is Log-Based Extraction?

Rivery's Log-Based extraction method 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 called Change Data Capture (CDC), and it 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 Log-Based extraction:

Prerequisites

• Permissions to run CDC stored procedures on the desired database(s) and table(s) for log-based extraction.
• A 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 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

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 change data capture (CDC) on an 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.