Migrate Microsoft Azure SQL Database to Amazon Aurora

Increasingly, customers are looking to move databases from proprietary engines such as Oracle and Microsoft SQL Server to open source engines running on AWS. One of the preferred destinations for this data is Amazon Aurora. In this post, we walk through a migration of a Microsoft Azure SQL database to an Amazon Aurora MySQL cluster using AWS Database Migration Service (AWS DMS).

Prerequisites
This post assumes that you already have an existing Azure SQL database. You will, of course, need the connection information for this database, including the DNS endpoint, user name, and password. The user name and password provided must have adequate permissions to access the data in your Azure SQL database.

As for the target, we are going to create an Amazon Aurora cluster for the purposes of this post. AWS DMS can support various database engines as source and target, however customers often choose Amazon Aurora because its unique storage engine. This engine enables durability across three Availability Zones, automatic point-in-time backups, and up to 15 low latency read replicas.

If you have an Aurora cluster already built that you want to use for a target, feel free to use it. If you want to create a new Aurora cluster, see the detailed instructions in Creating an Amazon Aurora DB Cluster.

Set up the AWS DMS infrastructure
Now that you have a source to read from and a target to move to, let’s set up the AWS DMS infrastructure. Because AWS DMS is so flexible, it comes with many components. I prefer to use AWS CloudFormation so that I can group these components together into a single “stack” and reliably and repeatedly create and tear down these resources as a single atomic unit.

Start by opening the AWS CloudFormation console.

Note: For the purposes of this post, I am launching all my resources in the us-east-1 Region. Be sure to launch your resources in the same AWS Region as your target database.

The AWS CloudFormation template that is used in this post is available on GitHub. Download this file, and then choose the Choose File button. Select the file you just downloaded, and then choose Next.

On the Specify Details page, specify the following:

After completing the form, choose Next.

The values on the Options page are optional. Choose Next.

On the Review page, review the settings specified for your stack. If everything is as you expect, choose Create.

After you choose Create, a screen appears that shows the status as CREATE_IN_PROGRESS. This step takes about 5–10 minutes to complete. After your stack has been created, the status changes to CREATE_COMPLETE.

Now that your stack has been created, you can see what you built by choosing the Resources tab.

You created the following resources:

The following key setting in the AWS CloudFormation template is specifically relevant to performing an Azure SQL Database migration.

This setting is already pre-configured in the AWS CloudFormation template, but it is worth noting because it is not required when migrating from other engines.

Grant network access
Now that you have a target database instance and your AWS DMS infrastructure is up and running, you need to establish connectivity between the replication instance and the source and target endpoints.

Open the Amazon Aurora security group
In this step, you allow access to your Amazon Aurora cluster from the replication instance. While still on the AWS CloudFormation console, choose the Outputs tab, and note the value specified for ReplicationInstanceSecurityGroupId. This is the identifier of the security group that has been applied to your replication instance. You need this value to allow traffic to flow from the replication instance into your Amazon Aurora cluster.

Next, choose Instances in the left navigation to view a list of your instances. Then choose your target Amazon Aurora instance. After selecting the instance, choose the security group as indicated in the following image:

In this screen, choose the Inbound tab. Choose Edit, and add the security group identifier of your replication instance as shown in the following image. Then choose Save.

Open the Azure SQL Database firewall
For the AWS DMS replication instance to communicate with Azure SQL Database, you must allow an exception in the Azure SQL Database firewall. To do this, you need to know the public IP address of the replication instance. You can find this by choosing Replication instances and copying down the specified IP address.

You then enter this exception into the Azure SQL Database firewall settings.

Refresh the schemas
Now that you have enabled connectivity between the replication instance and both the source and target, you want to have the replication instance refresh the schemas. This does two things: First, it validates that the replication instance can talk to both the source and the target. Second, it gives you a list of schemas on the source so that you can select which ones you want to migrate.

To refresh the schemas, go to Endpoints, choose the first endpoint, and then choose Refresh schemas.

Choose your replication instance as shown here, and choose Refresh schemas.

To verify whether the test is successful, choose the Connections tab as shown in the following image:

Repeat this same process for the second endpoint.

Create the task
Now that you have established and verified connectivity between the source, the replication instance, and the target, the next thing to do is set up the actual replication task. To do this, choose Tasks in the left navigation.

Next, choose Create task.

You will use many of the default settings on this page. The values are provided in the following table.

Under Table mappings, choose a schema name. In this example, the SalesLT schema is selected.

For Table name is like, specify % (percent sign) to indicate a wildcard so that you select all the tables in that schema. Then choose Add selection rule.

Next, choose Create Task.

The task is being created, and it will run to completion.

At this point, all the tables that belong to the specified schema and their data have been transferred from your Azure SQL database into Amazon Aurora. You can verify that all the data has transferred by using your favorite MySQL client—for example, MySQL Workbench.

Clean up
Now that the tables have been successfully migrated from Microsoft Azure SQL Database to Amazon Aurora, it’s time to clean up.

The first thing you want to do is delete the task you just created. To do so, just choose Delete at the top of the screen.

Next, revoke the replication instance’s access to your Aurora cluster. To do this, go to the Instances page, and choose your Aurora cluster’s security group, as shown here.

Delete the entry in your security group, as shown in the following image.

Now that the security group access has been revoked, the only thing left to do is tear down the AWS CloudFormation stack. You can do this by going to Stacks, choosing your stack, and then choosing Actions, Delete stack.

Choose Yes, Delete.

Wait for the deletion to complete. This might take 5–10 minutes.

Final notes
You have now successfully created an Amazon Aurora cluster, launched the AWS Database Migration Service infrastructure by using an AWS CloudFormation template, and migrated a Microsoft Azure SQL database to Amazon Aurora.

For more information about the services used in this post, see the Amazon Aurora, AWS Database Migration Service, and AWS CloudFormation documentation.

About the Authors

Steve Abraham is a principal solutions architect for Amazon Web Services. He works with our customers to provide guidance and technical assistance on database projects, helping them improving the value of their solutions when using AWS.