Revolutionize Your Database: Effortlessly Migrate Your SQL Server to Amazon RDS with the Power of AWS DMS

Introduction

If you’re running mission-critical workloads on a Microsoft SQL Server database that’s on-premises and want to move to AWS while maintaining the same database engine and minimize/eliminate downtime, AWS Database Migration Service maybe your answer. This post offers a solution for transferring your on-premises SQL Server database to Amazon RDS for SQL Server using the SQL Server backup and restore technique alongside AWS Database Migration Service (AWS DMS), in order to achieve minimal to zero downtime. This approach is helpful when migrating database objects, such as views, stored procedures, and functions, as part of the database migration (as opposed to letting the AWS DMS run the copy and full load sync, which does not take into account the before mentioned objects). It works with SQL Server databases of any size and keeps the database available to the application during migration, and allows for target database validation while the data is being replicated from source to target, thereby saving time on data validation.

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.

Conclusion

In conclusion, this post provided a guide on migrating an on-premises SQL Server database to an Amazon RDS for SQL Server database. By leveraging SQL Server backup, Amazon S3, Amazon RDS for SQL Server restore, and AWS DMS, this migration approach ensures near-zero downtime for your mission-critical on-premises databases. With these tools, you can seamlessly migrate your database to Amazon RDS for SQL Server in the AWS Cloud, allowing you to take full advantage of the benefits of AWS.