SQL Server Database Migration to AWS Aurora MySQL

It is not easy to convince a SQL DBA to migrate database from MS SQL to non-MS SQL specially when you have spent years with Microsoft SQL Server and believe me it took me some time to realize that this is the right way moving forward to reduce operational cost. Please see I am not endorsing AWS Aurora over SQL Server, however if application allows we can try to move some non-critical applications to Aurora which will subsequently reduce the operational cost.

As I mentioned in my previous migration post, that every migration should be carried out with utmost planning and attention so same applies here. You can see all the different migration phases here.

Using this blog post,  you will learn how to migrate a MS SQL Server database to an AWS Aurora with MySQL compatibility database using the AWS SCT (Schema Conversion Tool) and AWS DMS (Database Migration Service).

You can find the absolutely important prerequisites in the official AWS document which can be referred from below location:

https://docs.aws.amazon.com/dms/latest/sbs/CHAP_SQLServer2Aurora.Prerequisites.html

Resources required for the demo:

I have created an EC2 windows instance:

Created two RDS instances: MS SQL Server as source and AWS Aurora Database as target:

Step-by-Step Migration of MS SQL Server Database to AWS Aurora

The following steps provide instructions for migrating a Microsoft SQL Server database to an Amazon Aurora MySQL database.

Step 1: Install the Database Drivers:

• Download the JDBC driver for Microsoft SQL Server.

• Download the JDBC driver for Aurora MySQL. Amazon Aurora MySQL uses the MySQL driver.

Step 2: Install AWS SCT and the required JDBC drivers.

• See Installing and Updating the AWS Schema Conversion Tool in the AWS Schema Conversion Tool User Guide, and choose the appropriate link to download the AWS SCT.

Step 3: Start AWS SCT and create a new project:

• Connect to Source database:

• Connect to target:

• Now create a migration assessment report: this feature will assess SQL Server schema and provide a report for what SQL Database code will need to be changed. SCT is very powerful tool you can generate report, scripts, setup DMS etc. In this demo, I had simple set of tables and stored procedure so report did not highlight anything, however this could be the biggest impediment and may require a lot of development efforts.

• Create conversion scripts:

• On clicking yes, it will convert the schema and you need to click on “apply to target database” then it will deploy the schema in target Aurora database:

Step 4: Create an AWS DMS Replication Instance

After assessing and resolving issues related to the schema structure between source and target databases, we would need to configure DMS to migrate existing and ongoing data. The following illustration shows a high-level view of the migration process.

• Sign in to AWS Console and open AWS DMS:
• Click on Replication instances:

• Click on Create Replication instance:

• Click on create and then you will see replication instance in creating status:

Step 5: Create source endpoint:

In our case, the source database is MS SQL Server:

Please always test the endpoint (recommended):

Step 6: Create target endpoint:

Target endpoint will be AWS Aurora database:

Again test the target endpoint:

Now both the endpoints are created and you can see endpoint as below:

Step 7: Create and run Database Migration tasks:

• Create migration task:

This will create a migration task to copy existing and on-going data from MS SQL Server database to AWS Aurora database:

We have now setup DMS to migrate data from MS SQL Server to AWS Aurora Database. We can plan cut off from SQL Server Database to AWS Aurora database.

For a test I inserted a row in SQL Server as below:

and checked my target AWS Aurora database:

You can see the row has been replicated through DMS.

This is how you can migrate a MS SQL Server Database to AWS Aurora database. Please leave a comment if you have any query or feedback.

 

Advertisement