Configure change data capture parameters on Amazon RDS for SQL Server

AWS Database Migration Service (AWS DMS) is a managed migration and replication service that helps you move your database and analytic workloads to AWS quickly and securely. The source database remains fully operational during the migration, minimizing downtime to applications that rely on the database. AWS DMS can migrate data to and from most widely used commercial and open source databases.

SQL Server is a relational database developed by Microsoft. Amazon Relational Database Service (Amazon RDS) for SQL Server makes it straightforward to set up, operate, and scale SQL Server deployments in the cloud. Amazon RDS supports data replication via change data capture (CDC), and having CDC enabled is one of the prerequisites for using Amazon RDS for SQL Server with AWS DMS. CDC captures changes that are made to the data in the tables. It stores metadata about each change, which you can access later.

In this post, we do a deep dive on CDC parameters and explain their impact when configuring AWS DMS, in addition to discussing a few best practices.

Prerequisites

To follow along with this post, you should have familiarity with the following AWS services:

• AWS DMS
• Amazon RDS for SQL Server

Additionally, you need an AWS account with sufficient privileges to launch the resources necessary for this solution.

How AWS DMS works with Amazon RDS for SQL Server

For Amazon RDS for SQL Server, AWS DMS reads the transaction log (T-log) using Microsoft functions and gets the top 50,000 events by default. AWS DMS starts by querying the database log on the specific partition IDs that relate to the tables defined in the AWS DMS task. Partition IDs are read during each table reload, task restart, and task resume during both full load and CDC. AWS DMS retrieves the object IDs and obtains the data partition IDs corresponding to those object IDs. After you get the partition IDs, you fetch the relevant partitions from the T-log. This cycle runs in intervals of every second.

The following diagram illustrates the architecture. In this example, we use Amazon RDS for SQL Server as a source. The target for the AWS DMS task can be any supported endpoint.

AWS DMS reads the T-log using Microsoft functions, and requires CDC to be enabled on the source database and the tables that will be in scope of the AWS DMS task.

Why is CDC required for AWS DMS?

When CDC is enabled on a table, SQL Server creates a table in the cdc schema for this table. The changed table is populated with the change data and is assigned a name based on the schema and table being tracked. For instance, if you have a table called customer under the dbo schema, a table named cdc.customer_CT would be created on the cdc schema to record all changes against the dbo.customer table.

AWS DMS doesn’t read from the change tables. AWS DMS requires CDC to be enabled to make sure enhanced logging is captured in the T-log for AWS DMS to read the changes. As explained in the previous section, AWS DMS uses Microsoft functions to read the T-log. Consider the following table on the source:

CREATE TABLE [dbo].[dmstest](
 [id] [int] NOT NULL,
 [name] [varchar](50) NULL,
CONSTRAINT [PK_dmstest] PRIMARY KEY CLUSTERED
(
 [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

If you issue an UPDATE statement on this table and update the [name] column, you can see the difference between the [RowLog Contents 0] and [RowLog Contents 1] captured with and without CDC. For the sake of brevity, we have included a snippet of the following query that AWS DMS runs on the source:

select top 50000 [Current LSN], [operation], [RowLog Contents 0], [RowLog Contents 1] -- After Image

The output of the query shows the complete information captured in the T-log in the second record (the UPDATE statement was issued after enabling CDC).

Understanding CDC parameters

For CDC, two jobs are created:

• Capture job – Scans through the T-log file to read the changes and pushes those changes to the change tracking tables
• Cleanup job – Cleans up the records in the change tracking tables which exhausted the retention period

The following are the CDC parameters that pertain to AWS DMS:

• max_trans – Maximum number of transactions to process in each scan cycle
• max_scans – Maximum number of scan cycles to run in order to extract all rows from the log
• continuous – Indicates whether the capture job is to run continuously (1) or only once (0)
• polling_interval – Number of seconds between log scan cycles
• retention – Number of minutes that change rows are to be retained in change tables

Although AWS DMS doesn’t read the change tables, you must tune the CDC parameters to control the retention of changes in the T-log.

In the next section, we explain how the parameters max_trans, max_scans, and polling_interval help in retaining the log records in the T-log and how to tune them so that changes are retained for sufficient duration for AWS DMS to capture changes.

CDC parameters in action

We walk through the following steps to illustrate these parameters:

1. Create a database called dmscdc and a table called dmstestcdc under the database:

   create database dmscdc;
   
   use dmscdc;
   
   CREATE TABLE dbo.dmstestcdc(n INT NOT NULL PRIMARY KEY);
   

2. Enable CDC on the database dmscdc and the table dmstestcdc:

   exec msdb.dbo.rds_cdc_enable_db 'dmscdc';
   
   use dmscdc;
   exec sys.sp_cdc_enable_table
   @source_schema = 'dbo',
   @source_name = 'dmstestcdc' ,
   @role_name = 'CDCRole',
   @supports_net_changes = 1;

   You have to tune the CDC parameters to make sure that the log records are retained for sufficient period so that AWS DMS will be able to query the transaction record—that is, the specific log sequence number (LSN) it is looking for in the transaction log file of the source database. These are subjected to the following factors:

   • How many transactions is the target behind compared to the source?
   • What is the polling interval, specifically the frequency with which the CDC job runs?
   • What is the value of the Maxtrans and Maxscans? These parameters determine how many transactions CDC will process on each run.
3. Configure the capture job as follows. You must stop and start the CDC job every time you change the capture job parameters, which in our case is pollinginterval:

   EXECUTE sys.sp_cdc_change_job
   @job_type = N'capture',
   @pollinginterval = 3599;--Setting the polling interval for 1 hour
   
   EXEC sys.sp_cdc_stop_job @job_type = N'capture';
   EXEC sys.sp_cdc_start_job @job_type = N'capture';

4. Confirm the CDC parameters by running the following command:

   exec sys.sp_cdc_help_jobs;

   job_id	job_type	job_name	maxtrans	maxscans	continuous	pollinginterval	retention	threshold
   A49487C5-BF3C-4A8C-9385-6AFA7A3541B9	capture	cdc.dmscdc_capture	500	10	1	3599	0	0
   17511020-59D2-4C9E-BEA9-0578C0D23B11	cleanup	cdc.dmscdc_cleanup	0	0	0	0	4320	5000

   With the preceding setting, the capture job will process 5,000 records (maxtrans * maxscans) with a frequency of 1 hour.

5. Insert a few records into the table dmstestcdc to confirm this:

   DECLARE @max AS INT, @min AS INT;
   SET @max = 100000;
   SET @min = 1;
   
   WHILE @min <= @max
   BEGIN
   INSERT INTO dbo.dmstestcdc VALUES(@min);
   SET @min=@min+1;
   END

   The capture job reads the preceding transactions from the T-log and marks those as replicated, which is 100,001 records in our case. When the CDC job runs, the capture job will mark those transactions as done.

6. Check the CDC sessions by running the following query, which should fetch 10 rows. The query will tell us how many records CDC processed, which is 5,000 in our case.

   SELECT tran_count,start_time,end_time, scan_phase from sys.dm_cdc_log_scan_sessions where scan_phase<>'Aggregate' order by end_time desc

The preceding records will be purged from the T-log when the backup of the T-log happens on Amazon RDS for SQL Server, typically every 5 minutes, which helps in maintaining the size of the T-log and moving the LSN forward. The remaining records (95,001) will be picked up by the subsequent run of the capture job.

SQL Server doesn’t flush the T-log until after the transactions have been read by CDC. You need to strike a balance between how many records you’re retaining in the T-log and the AWS DMS replication lag. In this case, we make the capture job parameters aggressive by defining a shorter polling interval; then there can be a scenario that the LSN might be missing from the T-log. To avoid T-log truncation and make sure changes are retained in the T-log for sufficient duration, we recommend setting the polling interval to 1 day by running the following command:

use dbname

EXEC sys.sp_cdc_change_job @job_type = 'capture' ,@pollinginterval = 86399

exec sp_cdc_stop_job 'capture'

exec sp_cdc_start_job 'capture'

Capture historical information of CDC

To monitor the historical information of the capture job, you can query the sys.dm_cdc_log_scan_sessions table. The table contains one row for each log scan session in the current database. It contains up to 32 scan sessions. Run the following query to get the latest 10 records:

SELECT session_id, start_time, end_time, duration, scan_phase,
error_count, tran_count,command_count,last_commit_cdc_time, latency, empty_scan_count, failed_sessions_count
FROM sys.dm_cdc_log_scan_sessions order by end_time desc OFFSET 0 ROWS
FETCH NEXT 10 ROWS ONLY;

The following is a sample output.

Best practices and known issues

In this section, we discuss some best practices and considerations pertaining to the CDC parameters.

T-Log records truncated on failover in Multi-AZ instances

Always make sure that when the CDC parameters are changed on the primary instance, they are retained on the failover by running the rds_set_configuration command.

For instance, you can run the following sample command on the database dms_test to set the maxtrans and pollinginterval parameters:

USE dms_test;

EXEC sys.sp_cdc_change_job
@job_type = 'capture',
@maxtrans = 10000,
@pollinginterval = 6000;

Run the following commands to make sure these values are retained post-failover:

EXEC rdsadmin..rds_set_configuration 'cdc_capture_maxtrans' , 10000;
EXEC rdsadmin..rds_set_configuration 'cdc_capture_pollinginterval' , 6000;

Planned failovers or maintenance of the AWS DMS replication instance

For Amazon RDS for SQL Server, you need to make sure that every time the AWS DMS task is stopped for any maintenance activity on the source or during a planned scaling of the related AWS DMS replication instance, the capture job doesn’t run. When the capture job runs, the scanned events will be purged from the T-log when the T-log backup happens on Amazon Simple Storage Service (Amazon S3) every 5 minutes.

1. Stop the capture job by running the following command:

   exec sp_cdc_stop_job 'capture'

2. Stop the AWS DMS task.
3. Finish your desired maintenance.
4. Resume the AWS DMS task.
5. Wait for the source latency to be 0.
6. Start the capture job by running the following command:

   exec sp_cdc_start_job 'capture'

The AWS DMS task will fail with the following error message if the aformentioned sequence of steps are not followed:

2023-10-06T15:02:05 [SOURCE_CAPTURE ]E: Failed to access LSN '0000019f:00007fff:0008' in the backup log sets since BACKUP/LOG-s are not available. [1020465] (sqlserver_endpoint_capture.c:813)

If you observe that the LSN is getting truncated on the source after stopping the capture job, there might not be any CDC events in the active T-log that could have prevented the truncation. This can arise when the database is idle or has fewer transactions. In this scenario, the sequence of steps is as follows:

1. Stop the capture job by running the following command:

   exec sp_cdc_stop_job 'capture'

2. Make sure there are some transactions or changes in the CDC-enabled database before stopping the AWS DMS task. You can run a script that runs DML statements every second. If you want to create a test script, you can follow the instructions given later in this section.
3. Stop the AWS DMS task.
4. Finish your desired maintenance.
5. Resume the AWS DMS task.
6. Wait for it to sync by monitoring the source latency.
7. Stop the script that you set up in Step 2.
8. Start the capture job by running the following command:

exec sp_cdc_start_job 'capture'

Follow these instructions to set up a script to run the test script mentioned in Step 2. In the following script, you create a table called test_table under the dbo” schema and then enable CDC on the test_table table. You then set up a SQL Server agent job that will insert a record and delete the record into the aforementioned table. This makes sure there are changes in the T-log that need to be picked up by the CDC job and therefore will prevent T-log truncation.

1. Create the test table:

   create table dbo.test_table (id int not null PRIMARY KEY);

2. Add the new table to CDC:

   use dmscdc;
   exec sys.sp_cdc_enable_table
   @source_schema = 'dbo',
   @source_name = 'test_table' ,
   @role_name = 'CDCRole',
   @supports_net_changes = 1;

3. Create a SQL Server agent job in Amazon RDS to insert or delete a record every 1 minute. Use the appropriate owner_login_name and database_name values in your agent job:

   USE [msdb]
   
   GO
   
   /****** Object: Job [aws_dms_traffic_to_test_table] Script Date: 10/9/2023 4:17:28 PM ******/
   
   BEGIN TRANSACTION
   
   DECLARE @ReturnCode INT
   
   SELECT @ReturnCode = 0
   
   /****** Object: JobCategory [[Uncategorized (Local)]] Script Date: 10/9/2023 4:17:28 PM ******/
   
   IF NOT EXISTS (SELECT
   name FROM msdb.dbo.syscategories WHERE
   name=N'[Uncategorized (Local)]' AND category_class=1)
   
   BEGIN
   
   EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
   
   IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
   
   END
   
   DECLARE @jobId BINARY(16)
   
   EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'aws_dms_traffic_to_test_table', 
   
    @enabled=1, 
   
    @notify_level_eventlog=0, 
   
    @notify_level_email=0, 
   
    @notify_level_netsend=0, 
   
    @notify_level_page=0, 
   
    @delete_level=0, 
   
    @description=N'No description available.', 
   
    @category_name=N'[Uncategorized (Local)]', 
   
    @owner_login_name=N'admin', @job_id = @jobId OUTPUT
   
   IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
   
   /****** Object: Step [generate_traffic] Script Date: 10/9/2023 4:17:28 PM ******/
   
   EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'generate_traffic', 
   
    @step_id=1, 
   
    @cmdexec_success_code=0, 
   
    @on_success_action=1, 
   
    @on_success_step_id=0, 
   
    @on_fail_action=2, 
   
    @on_fail_step_id=0, 
   
    @retry_attempts=0, 
   
    @retry_interval=0, 
   
    @os_run_priority=0, @subsystem=N'TSQL', 
   
    @command=N'insert into dbo.test_table values (30);
   
   delete from dbo.test_table where id = 30;
   
   ', 
   
    @database_name=N'dmscdc', 
   
    @flags=0
   
   IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
   
   EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
   
   IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
   
   EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'schedule for running DML statements for generating user_traffic', 
   
    @enabled=1, 
   
    @freq_type=4, 
   
    @freq_interval=1, 
   
    @freq_subday_type=4, 
   
    @freq_subday_interval=1, 
   
    @freq_relative_interval=0, 
   
    @freq_recurrence_factor=0, 
   
    @active_start_date=20231006, 
   
    @active_end_date=99991231, 
   
    @active_start_time=0, 
   
    @active_end_time=235959, 
   
    @schedule_uid=N'84a2b2ab-4234-40a3-add4-c04d561ad88f'
   
   IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
   
   EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
   
   IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
   
   COMMIT TRANSACTION
   
   GOTO EndSave
   
   QuitWithRollback:
   
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
   
   EndSave:
   
   GO
   

4. On the AWS DMS console, exclude this table from your AWS DMS task using mapping rules if you’re using any wildcards (%) in the table selection rules of your AWS DMS task that would replicate this table:

   {
   "rule-type": "selection",
   "rule-id": "1",
   "rule-name": "1",
   "object-locator": {
   "schema-name": "dbo",
   "table-name": "test_table"
   },
   "rule-action": "exclude",
   "filters": []
   },

Planned restart or failover of the RDS for SQL Server instance

The RDS for SQL Server agent service restarts whenever there is a reboot of the RDS for SQL Server instance or a failover and this causes the CDC job to rerun after the reboot or failover. To avoid the truncation of the T-log, follow these steps:

1. Stop the AWS DMS task.
2. Capture the current maxtrans and maxscans values, which you will revert after the failover:

   sys.sp_cdc_help_jobs;

3. Change the CDC configuration to set maxtrans and maxscans to 1:

   EXEC sys.sp_cdc_change_job @job_type = 'capture' ,@maxtrans = 1, @maxscans = 1
   exec sp_cdc_stop_job 'capture'
   GO

4. Run the following statement so that the CDC parameters are retained after the failover:

   EXEC rdsadmin..rds_set_configuration 'cdc_capture_maxtrans' , 1;
   EXEC rdsadmin..rds_set_configuration 'cdc_capture_maxscans' , 1;

5. Restart the RDS for SQL Server instance.
6. Resume the AWS DMS task.
7. Restart the captured job with the restored configuration. In the following script, we have assumed maxtrans of 500 and maxscans of 10, but you should use the values that were captured in Step 2:

   EXEC sys.sp_cdc_change_job @job_type = 'capture', @maxtrans = 500, @maxscans = 10
   exec sp_cdc_stop_job 'capture'
   exec sp_cdc_start_job 'capture'
   GO

8. Run the following statement so that the CDC parameters are retained after the failover:

   EXEC rdsadmin..rds_set_configuration 'cdc_capture_maxtrans' , 500;
   EXEC rdsadmin..rds_set_configuration 'cdc_capture_maxscans' , 10;

Clean up

To avoid incurring recurring charges, clean up your resources:

1. On the AWS DMS console, delete any AWS DMS task that you set up.
2. Drop the database by running the following command:

   EXECUTE msdb.dbo.rds_drop_database N'dmscdc'

Conclusion

In this post, we shared the importance of configuring CDC parameters when using Amazon RDS for SQL Server as a source for configuring AWS DMS tasks, and also discussed some best practices. If you have any feedback or questions, leave them in the comments.

About the Authors

 Suchindranath Hegde is a Data Migration Specialist Solutions Architect at Amazon Web Services. He works with our customers to provide guidance and technical assistance on data migration to the AWS Cloud using AWS DMS.

Abhishek Chaturvedi is a Sr. Database Engineer on the Amazon Web Services DMS team.

Mahesh Kansara is a Database Engineering manager at Amazon Web Services. He closely works with development and engineering teams to improve the migration and replication service. He also works with our customers to provide guidance and technical assistance on various database and analytical projects, helping them improving the value of their solutions when using AWS.

Junu Thankappan is a Senior Database Engineer with Amazon Web Services. He works with the AWS RDS team, focusing on commercial database engines and SQL Server.