Migrating SQL Server to Amazon RDS with Near-Zero Downtime Using AWS DMS

Introduction

AWS DMS handles the data sync for SQL Server migrations to RDS, but its full-load mode skips schema objects like stored procedures, views, and functions. The fix is to do a native SQL Server backup, restore that to RDS (which brings the schema objects with it), and then use DMS in CDC-only mode to replicate ongoing changes from the moment of the backup. You get near-zero downtime with the schema fully intact.

Works for any size database. The source stays available to the application during migration, so you can validate the target while data is replicating. That validation phase is usually where the real time savings show up.

Logical Overview

1. Backup Database
2. Copy Database to S3 bucket
3. Restore Database to RDS
4. Configure DMS for continuous replication

Prerequisites

• AWS Account
• AWS IAM User with adequate permissions
• AWS CLI installed and configured
• SQL User with adequate permissions
• S3 Bucket
• AWS RDS for Microsoft SQL Server with SQLSERVER_BACKUP_RESTORE option in RDS Option Group
• AWS DMS
  • Replication Instance
  • Source Endpoint
  • Target Endpoint

Tasks - Source Microsoft SQL Server

• Backup SQL Server Database
  • Run TSQL Command to backup your database
    • BACKUP DATABASE [myAwesomeDatabase] TO DISK = 'C:\backups\myAwesomeDatabase.bak' WITH NOFORMAT, NOINIT, NAME = 'myAwesomeDatabase Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
• Obtain Log Sequence Number
  • SQL creates a Log Sequence Number (LSN) to maintain the sequence of transaction records in the database. We will use this LSN from the time of the backup (when you ran the TSQL command above) so that we can tell the AWS DMS to start replication from that LSN going forward for the AWS RDS. To capture the LSN, run the TSQL command:
    • SELECT [Current LSN], [Begin Time], Description FROM fn_dblog(NULL, NULL) Where [Transaction Name] = 'Backup:CommitDifferentialBase'
  • The LSN is made of three sections: virtual log file sequence number, starting offset of a log block, and slot number
• Copy the backup file to the S3 bucket
  • We will copy the myAwesomeDatabase.bak file to a S3 bucket (e.g. mysqlbackups). We will use this bucket and .bak file to restore the database to the AWS RDS.
    • aws s3 cp C:\backups\myAwesomeDatabase.bak s3://mysqlbackups/

Tasks - AWS:RDS

• Restore the database from the backup file in S3 to AWS RDS
  • exec msdb.dbo.rds_restore_database @restore_db_name=[myAwesomeDatabase], @s3_arn_to_restore_from='arn:aws:s3:::mysqlbackups/myAwesomeDatabase.bak';

Tasks - AWS:DMS

• Find the arn of source endpoint(s)
  • aws dms describe-endpoints --filters Name=endpoint-type,Values=source --query 'Endpoints[*].EndpointArn' --output text
• Find the arn of target endpoint(s)
  • aws dms describe-endpoints --filters Name=endpoint-type,Values=target --query 'Endpoints[*].EndpointArn' --output text
• Find the arn for the replication instance
  • aws dms describe-replication-instances --query 'ReplicationInstances[*].ReplicationInstanceArn'
• Create DMS Task for Continuous Replication
  • Below i am creating a task called 'myreplicationtask'
  • my source endpoint arn and target endpoint arn from the above commands
  • my replication instance arn from the above command
  • migrate only changes from the source (e.g. migration-type cdc)
  • start from the LSN of 123456:76:1 (obtained from the TSQL command above)

aws dms create-replication-task \
    --replication-task-id myreplicationtask \
    --source-endpoint-arn arn:aws:dms:us-east-1:123456789012:endpoint:my-source-endpoint \
    --target-endpoint-arn arn:aws:dms:us-east-1:123456789012:endpoint:my-target-endpoint \
    --replication-instance-arn arn:aws:dms:us-east-1:123456789012:rep:my-replication-instance \
    --migration-type cdc \
    --cdc-start-position "{\"CustomCDCStartPosition\":{\"StrStartPosition\":\"123456:76:1\"}}" \
    --table-mappings '{
        "rules": [
            {
			"rule-type": "selection",
			"rule-id": "923298803",
			"rule-name": "923298803",
			"object-locator": {
				"schema-name": "%",
				"table-name": "%"
			},
			"rule-action": "include",
			"filters": []
			}
			]
		}'

Final Tasks

• You can monitor the changes captured and accounted for on the target RDS by going to the Table Statistics in the DMS section, specifically the Migration Tasks section, of the AWS console.
• Once the continuous replication is implemented, you can point your application to the RDS instance for testing and schedule a time to do the final cutover.

Troubleshooting

Issue	Possible Cause	Solution
RDS restore fails with "Access Denied"	IAM role for S3 access not configured	Ensure the RDS option group has SQLSERVER_BACKUP_RESTORE with an IAM role that has s3:GetObject permission on the backup bucket.
DMS task fails to start CDC	LSN format incorrect or LSN no longer in transaction log	Verify LSN format matches DMS requirements. If the transaction log has been truncated, you may need to take a new backup and restart.
"CDC start position is invalid" error	LSN captured incorrectly or database recovery model issue	Ensure the source database is in FULL recovery mode. Verify the LSN by querying fn_dblog() again.
Replication lag keeps increasing	DMS instance undersized or network bandwidth	Monitor DMS CloudWatch metrics. Consider scaling up the replication instance or optimizing network path between source and DMS.
Stored procedures not migrated	DMS only replicates data, not schema objects	This approach uses native backup/restore specifically to preserve schema objects. Verify the restore completed successfully before starting CDC.