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:

Migration

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

Migration

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:

Migration

Migration

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

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

Migration

  • Connect to Source database:

Migration

  • Connect to target:

Migration

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

Migration

  • Create conversion scripts:

Migration

  • 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:

Migration

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.

Migration

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

Migration

  • Click on Create Replication instance:

Migration

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

Migration

Step 5: Create source endpoint:

Migration

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

Migration

Please always test the endpoint (recommended):

Migration

Step 6: Create target endpoint:

Migration

Target endpoint will be AWS Aurora database:

Migration

Again test the target endpoint:

Migration

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

Migration

Step 7: Create and run Database Migration tasks:

Migration

  • Create migration task:

Migration

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

Migration

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:

Migration

and checked my target AWS Aurora database:

Migration

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.

 

9 thoughts on “SQL Server Database Migration to AWS Aurora MySQL

Add yours

  1. Thanks Chandan as I mentioned above I am not endorsing AWS Aurora over SQL Server but it all depends what your customer wants and application requirements. It is all about bringing agility and faster time to market in the business through technology. You can choose options as per your requirements but that option should be driven by business need and application requirements not by emotions.

    Like

  2. I am a fan of your migration posts.
    Really helps me present use case to my customer. I really appreciate your hard work, makes our task so much easier.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by WordPress.com.

Up ↑

%d bloggers like this: