Rajendra Gupta
Upload the backup file in the AWS S3 bucket

Migrating your on-premises SQL databases to AWS RDS SQL Server using AWS DMS

January 25, 2021 by

There are multiple ways to deploy the databases in the AWS RDS SQL Server. Suppose you want to migrate your on-premises SQL Server database to AWS managed relational database service and you have a critical application and require minimal (almost zero) downtime. For this purpose, you can use the AWS Database Migration Service (AWS DMS) in a combination of SQL Server native backups.

You can use the on-premises SQL Server as a source database; however, for demonstration of the steps, I will use the SQL Server on EC2 instance.

Configuring SQL Server on EC2 instance as a source database

For the demonstration, we use the Amazon Machine Image (AMI) preconfigured with the SQL Server 2019. Log in to your AWS console and search for SQL Server 2019 AMI.

It highlights all available AMI for SQL Server 2019. I choose the Microsoft Windows Server 2019 with SQL server 2019 standard.

Configure the SQL Server on EC2 instance as a source database

You can go through the remaining steps and build the SQL Server on an EC2 instance. Refer to the article for detailed steps on AWS SQLShack for detailed instructions.

An EC2 instance is in a running state, and it takes some time to perform the 2/2 status check.

EC2 instance status

Open the EC2 instance properties, and click on Connect.

EC2 instance properties

In the Connect to the instance page, click on RDP client. From here, download the remote desktop file.

RDP Client

Click on Get Password, input your PEM secret key, and it displays the password on the screen. You require this password for an RDP session on the EC2 Windows server.

Get password for Windows RDP

Specify your administrator password and get an RDP session, as shown below.

Specify your administrator password

This EC2 instance is having preinstalled SQL Server 2019. Therefore, for a sample database, browse to Microsoft docs and download the [AdventureWorks2019.bak] file.

download [AdventureWorks2019.bak] file

Restore the [AdventureWorks2019] database in SQL Server on EC2 instance with the following script.

Restore the [AdventureWorks2019] database

Before starting the database migration, you can take a Full database backup for the [AdventureWorks2019] database. We will restore this backup on the AWS RDS SQL Server.

Take DB full backup

SQL Server uses the log sequence number for the transaction log records in the database. We can use this LSN in the database migration task configurations.

Execute this script on the [AdvenutreWorks2019] database and note-down the LSN value.

transaction log records LSN

Restore the full database backup on the AWS RDS SQL Server

In this step, we will restore the full backup taken earlier in the AWS RDS SQL Server instance. Before you move forward, you should have the following prerequisites.

  • AWS S3 bucket: Here, I use the S3 bucket [SQLShackDemo17112020]
  • RDS SQL Server: Here, I use the RDS instance [myrdssql] with SQL Server 2019

AWS RDS SQL Server

Both the S3 bucket and RDS instance should be in an AWS region.

Upload the backup file in the AWS S3 bucket

Open the AWS S3 bucket [SQLShackDemo17112020] and upload the full backup of the [AdventureWorks2019] database.

Upload the backup file in the AWS S3 bucket

Restore database backup from S3 to RDS SQL

We need to restore the backup stored from the S3 bucket to AWS RDS SQL Server. To enable the restoration, do the following tasks.

  • Create an IAM policy and role to integrate S3 bucket and RDS
  • Create an option group with SQLSERVER_BACKUP_RESTORE, modify the RDS instance to use the new option group

You can refer to the article, AWS RDS SQL Server migration using native backups for detailed instructions.

In the below script, specify the ARN for backup stored in AWS S3 bucket and execute on the RDS instance.

It initiates a database restoration task and returns the task id.

Restore database backup from S3 to RDS SQL

Track the database restoration task status using the following script.

As shown below, the database restoration task is successful for the [AdventureWorks2019] database. RDS task status

Creating an AWS DMS replication instance

We need to configure an AWS DMS replication instance for database migration. In the AWS Web Console, launch Database Migration Service. It gives the following AWS DMS dashboard.

AWS DMS replication instance

Click on Create replication instance. On the replication instance page, enter the following information.

  • DMS instance name
  • Descriptive Amazon resource name (Optional)
  • Description
  • Choose an instance class
  • AWS DMS version: You can go with the prepopulated DMS version

AWS RDS SQL Server Create Replication instance

  • VPC: Select the Amazon Virtual Private cloud for your replication instance
  • Multi-AZ: If you want to configure the high availability for the DMS instance, choose the Multi-AZ configuration
  • Publicly accessible: If you want to assign a public IP address to your AWS DMS instance, put a tick on the checkbox

We can go with the default configuration in the remaining options. Click on Create.

VPC configuration

It creates the AWS DMS instance in a few minutes. Its status should be Available before we proceed to the next step.

AWS DMS instance status

Configure the Source and Target endpoints

We need endpoints for both the source (On-premise or SQL Server on EC2 instance) and Target (RDS SQL Server) for the database migration task.

Configure the Source and Target endpoints

In the AWS DMS, click on the Endpoints.

Source Endpoint
  • Endpoint type: Source endpoint
  • Endpoint identified: Give a unique name for your source endpoint
  • Descriptive Amazon Resource Name( optional)
  • Source engine: Choose Microsoft SQL Server
  • Server name: Specify the public DNS name of your AWS EC2 instance or the FQDN of your on-premise SQL Server.
  • Port: Specify the SQL Server network port. The default port is 1433.
  • User name and password: Enter the credentials for connecting to SQL Server instance. It should have sysadmin permissions so that DMS can configure the replication or Change data capture(CDC) process.
  • Database name: Enter the source database name that we want to migrate to the AWS RDS SQL Server.

Source Endpoint

Specify VPC for your AWS account and click on Create endpoint.

VPC

Open the source endpoint and go-to connection. Click on Test Connection; its status should be Successful, as shown below.

Test Connection,

Target Endpoint

Similar to the source endpoint, we need to create the target endpoint for AWS RDS SQL Server.

  • RDS instance: Put a check on Select RDS DB instance and choose your target RDS from the drop-down

The remaining configurations are similar to the source endpoint.

Target Endpoint

Target Endpoint configuration

Once the target endpoint for the RDS instance is created, its test connection should be successful, as shown below.

Test connection status

Create a database migration task

In the AWS DMS, navigate to database migration tasks. We do not have any existing DMS task. Therefore, it shows the blank screen.

Click on Create database migration task.

Create a database migration task

In the database migration task, do the following configurations.

  • Task identifier: Give a unique name for the DMS task
  • Descriptive Amazon Resource Name (optional)
  • Replication instance: Select the DMS replication instance from the drop-down
  • Source database endpoint: Choose the source database endpoint from the drop-down
  • Target database endpoint: Choose the target database endpoint from the drop-down value
  • Migration type: We already restored the full backup from the source to RDS SQL Server. Therefore, choose the option – Replicate data changes only

    DB migration task status

  • In the CDC state mode, choose option Specify log sequence number and enter the LSN in the System change number box
  • In the Target table preparation mode, choose to Do Nothing
  • Put a check on Enable Data Validation. Using this option, AWS DMS compares data at source and target databases. It also ensures that data migration is successful and consistent

    AWS RDS SQL Server CDC start mode

  • In the Selection rules, we can filter the objects for specific schema, table or pattern. Here, we did not specify any specific schema or pattern. Therefore it replicates complete data changes

Selection rules

Click on the Create task button.

Migration task startup

It starts configuration for database migration task.

Status for DMS task

Its status changes to replication ongoing after a few minutes.

Replication ongoing

You can click on table statistics. It populates all tables. Here, you can monitor the inserts, delete, updates, DDL, total rows once the table is validated on both source and target. It takes a few minutes for populating the information depending upon your database size.

Table validations

You can also go back to your source instance and view that a local publication exists. The AWS database migration service configures it for populating data between the source and destinations.

Publication on source instance

AWS DMS uses the replication and CDC for the database migrations.

  • SQL Server replication for capturing data changes for tables with primary keys. AWS DMS automatically configures the replication if you provide an endpoint user with the sysadmin permissions
  • Change Data Capture ( CDC) for tables without primary keys. You need to enable the MSCDC at the database level and add the tables manually

Refer to the article, Using a Microsoft SQL Server database as a source for AWS DMS for more details.

It continuously replicates the data from the source to the target AWS RDS SQL Server. You can stop data replication during the cut-off time and point your application to connect with RDS endpoints.

Conclusion

In this article, we explored the AWS database migration service for migrating a database from source on-premise or SQL Server on EC2 instance to AWS RDS SQL Server with zero downtime. It is suitable for critical databases where we cannot afford much downtime.

Rajendra Gupta
AWS, AWS RDS

About Rajendra Gupta

As an MCSA certified and Microsoft Certified Trainer in Gurgaon, India, with 13 years of experience, Rajendra works for a variety of large companies focusing on performance optimization, monitoring, high availability, and disaster recovery strategies and implementation. He is the author of hundreds of authoritative articles on SQL Server, Azure, MySQL, Linux, Power BI, Performance tuning, AWS/Amazon RDS, Git, and related technologies that have been viewed by over 10m readers to date. He is the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups. Based on his contribution to the SQL Server community, he has been recognized with various awards including the prestigious “Best author of the year" continuously in 2020 and 2021 at SQLShack. Raj is always interested in new challenges so if you need consulting help on any subject covered in his writings, he can be reached at rajendra.gupta16@gmail.com View all posts by Rajendra Gupta

5,265 Views