How can I use AWS DMS to migrate from an Amazon RDS DB instance that is running SQL Server?

4 minute read

I want to use AWS Database Migration Service (AWS DMS) to migrate from an Amazon Relational Database Service (Amazon RDS) DB instance. The DB instance is running SQL Server.

Short description

First, use the lead user of the RDS for SQL Server instance to configure the database and the tables. Then, use the AWS DMS console or the AWS Command Line Interface (AWS CLI) to create your source endpoint. For more information, see Capturing data changes for self-managed SQL Server on-premises or on Amazon EC2.

Note: If you receive errors when running AWS CLI commands, make sure that you're using the most recent AWS CLI version.


Configure the database and tables

AWS DMS offers two methods to capture ongoing changes: MS-Replication and MS-CDC.

Note: Amazon RDS doesn't support Sysadmin privileges. This means that Amazon RDS instances that run SQL Server as the source must use MS-CDC.

1.    To turn on MS-CDC at the database level, run this command as the lead user:

EXEC msdb.dbo.rds_cdc_enable_db 'DBName';

2.    To turn on CDC at the table level, run this command for each table:

EXECUTE sys.sp_cdc_enable_table @source_schema = N'SchemaName', @source_name =N'TableName', @role_name = NULL;

3.    To increase the retention period for the transactions in the T-Log, run this command:

EXEC sys.sp_cdc_change_job @job_type = 'capture' ,@pollinginterval = 3599;

When you configure ongoing replication for a SQL Server instance, it's a best practice to set pollinginterval to retain changes for one day (86,400 seconds). Make sure that there's enough storage space for the T-logs during the specified retention period. For example, if your retention period is 24 hours, adjust the values of maxscans and maxtrans in the capture job. maxtrans x maxscans must equal the average number of table events that are generated that AWS DMS replicates from the source database for each day. Compare this calculated 24 hour total T-log size with your available storage space and decide if you have enough storage space for transaction processing.

There is a known issue with some versions of SQL Server. If the value of pollinginterval is set to more than 3599 seconds, then the value resets to the default of five seconds. When this happens, the T-Log entries are purged before AWS DMS reads them. For information on this issue, see KB4459220 - FIX: Incorrect results occur when you convert "pollinginterval" parameter from seconds to hours in sys.sp_cdc_scan in SQL Server on the Microsoft website.

After you create an AWS DMS task, monitor the status of your migration task. If you stop the task and resume the task after one hour, then it might fail. The T-Log is truncated and AWS DMS doesn't have the required log sequence numbers (LSNs) because of the issue mentioned earlier. To prevent this issue from occurring, complete these steps:

1.    To stop the capture job, run this command:

use [DBName]
exec sys.sp_cdc_stop_job;

2.    Stop the AWS DMS task, and wait for all remaining activities to stop.

3.    Resume the DMS task and monitor the AWS DMS task's source latency to wait for it to sync.

4.    To restart the capture job, run this command:

use [DBName]
exec sys.sp_cdc_start_job;

Create your source endpoint

To create your source endpoint, follow the steps in How do I create source or target endpoints using AWS DMS?

Related information

Troubleshooting issues with Microsoft SQL Server

Migrating Microsoft SQL Server databases to the AWS Cloud

Recommended settings when using Amazon RDS for SQL Server as a source for AWS DMS

How can I troubleshoot T-Log full issues on my Amazon RDS for SQL Server source when CDC is turned on for my AWS DMS task?

AWS OFFICIALUpdated 9 months ago