How do I use AWS DMS to migrate from an Amazon RDS DB instance that runs SQL Server?

Short description

First, use the primary user of the Amazon RDS for SQL Server instance to configure the database and the tables. If you use a DMS specific user other than primary user in RDS, then provide the minimum permissions required. 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 you run AWS CLI commands, then see Troubleshooting errors for the AWS CLI. Also, make sure that you're using the most recent AWS CLI version.

Resolution

Note: In the following resolution, the primary user is the master user.

Configure the database and tables

1. AWS DMS uses MS-CDC to capture ongoing changes. Run the following command as the primary user to turn on MS-CDC at the database level:

   EXEC msdb.dbo.rds_cdc_enable_db 'DBName';
   GO

2. Run the following command for each table to turn on CDC at the table level:

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

   Note: If you use AWS DMS version 3.4.7 or later and you don't use a read-only replica, then you can skip step 2. Instead, set the SetUpMsCdcForTables extra connection attribute (ECA) to true to have the AWS DMS automatically set up MS-CDC for all your tables. For information about ECAs, see Endpoint settings when using SQL Server as a source for AWS DMS.

3. 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

   Note: AWS DMS version 3.5.3 and later support reading from log backups. If a transaction is backed up before AWS DMS reads it from the active transaction log, then the task reads from the RDS backup logs on demand until it catches up to the active transaction log. To use this feature, set the RDS automated backup retention period to at least one day.

For AWS DMS 3.5.2 or earlier, configure the pollinginterval value.

Configure the pollinginterval value

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

There's a known issue with some versions of RDS for 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.

Stop and resume a DMS task

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 the T-Log might be truncated and the required log sequence numbers (LSNs) might no longer be available. For AWS DMS version 3.5.3 or earlier, the task fails because it can't read the required LSNs from the T-log. For AWS DMS version 3.5.3 or later, the task experiences increased latency because AWS DMS must read the changes from backup logs, To prevent this issue from occurring, complete these steps:

1. Run the following command to 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 monitor the AWS DMS task's source latency to wait for it to sync.

4.Run the following command to restart the capture job:

use [DBName]
exec sys.sp_cdc_start_job;

Note: Start the job that truncates SQL Server transaction logs. Otherwise, storage on your SQL Server instance might fill up.

Create your source endpoint

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

Related information

Troubleshooting issues with Microsoft SQL Server

Modernize SQL Server databases

Using a Microsoft SQL Server database as a source for AWS DMS

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