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

Short description

You can migrate data from one or more SQL Server DB instances using AWS DMS into any of the supported target DB engines.

First, be sure to use the master user of the RDS SQL Server instance to configure the database and the tables. Then, create your source endpoint using the AWS DMS console or using the AWS Command Line Interface (AWS CLI).

For more information about how SQL Server uses change data capture (CDC) and the prerequisites and requirements for using ongoing replication with SQL Server DB instances, see Using ongoing replication (CDC) from a SQL Server source.

Resolution

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

AWS DMS offers two methods for capturing ongoing changes: MS-Replication and MS-CDC.

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

Run the following command using the master user to enable MS-CDC at the database level:

EXEC msdb.dbo.rds_cdc_enable_db 'DBName';
GO

Then run the following command for each table to enable CDC at the table level:

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

Then run the following command to increase the retention period for the transactions in the T-Log:

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

When configuring ongoing replication for a SQL Server instance, it's a best practice to set the pollinginterval to retain changes for one day (86400 seconds). However, there is a known issue with some versions of SQL Server, so if the value of pollinginterval is set to more than 3599 seconds, then the value resets to the default, which is five seconds. When this happens, the T-Log entries are purged before AWS DMS reads them. To see which versions are still impacted by this issue, see the Microsoft documentation for Incorrect results occur when you convert "pollinginterval" parameter from seconds to hours in sys.sp_cdc_scan in SQL Server.

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

1.    Stop the capture job:

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 wait for it to sync by monitoring the AWS DMS task’s source latency.

4.    Restart the capture job:

use [DBName]
exec sys.sp_cdc_start_job;

Related information

Troubleshooting Microsoft SQL Server specific issues

Migrating Microsoft SQL Server databases to the AWS Cloud