Fast and secure migration of Azure SQL to cloud-native database Amazon Aurora

When migrating a Microsoft Azure SQL Database, you typically need to use database tools to extract data from the Azure SQL Database, make the necessary schema changes to the target database, and then import the data into the target database. This typically involves manual steps and the source application needs to be down for an extended period of time. What if there was a way to quickly and safely migrate from Azure SQL to Amazon Aurora MySQL Compatible while minimizing application downtime and user intervention efforts? This blog post describes how to quickly, safely and automatically migrate from Microsoft Azure SQL Database to Amazon Aurora MySQL Compatible , and minimize downtime for applications that rely on Microsoft Azure SQL Database. In the following, we assume that you have an existing Microsoft Azure SQL database that needs to be migrated to Amazon Aurora MySQL Compatible. You will need administrator access to the Azure SQL Database, as well as connection information, username and password. You will also need to modify the Azure SQL Database firewall to allow access from Amazon Database Migration Service (Amazon DMS) and the Amazon Elastic Compute Cloud (Amazon EC2) instance hosting the Amazon Schema Conversion Tool (A hat man SCT). For the target database, we will use Amazon Aurora MySQL Compatible.

We use Amazon EC2 Windows instances for Amazon SCT deployments. SCT can be installed natively on any Windows or popular Linux platform.

The dataset we will be working with is a sample dataset that can be selected when creating an Azure SQL database (SalesLT). Figure 1 below illustrates the Azure/Amazon services used for migration.

Figure 1. Azure/Amazon services used

Schema transformation

In heterogeneous database migration, transforming database schema and code objects is often the most time-consuming operation. Amazon SCT is an easy-to-use application that you can install on an on-premises server, on-premises laptop/desktop, or an Amazon EC2 instance. SCT helps simplify heterogeneous database migrations by examining the source database schema and automatically converting most database code objects, including views, stored procedures, and functions, into a format compatible with the target database. Any objects that SCT cannot convert automatically will be marked with details that you can use to convert the object manually.

data migration

After the schema transformation is done, you need to move the data itself. For production databases, you may not be able to afford any downtime during migration. To achieve near-zero migration downtime, you may need to ensure that the source and target databases are in sync before switching applications to the new target database.

Amazon Database Migration Service (Amazon DMS) helps you easily and securely migrate data from a source database to a target database. Amazon DMS supports round-trip data migration between the most widely used commercial and open source databases. In our example, the source database is in Azure SQL and the target database will be hosted on Aurora MySQL Compatible. Additionally, the source database remains fully operational during the migration, minimizing downtime for applications that depend on it.

Migration process

Migrating an Azure SQL Database to an Aurora MySQL-compatible database involves the following steps:

1. Create an Amazon Aurora MySQL Compatible Cluster
2. Create an Amazon EC2 instance for installing Amazon SCT
3. Connect Amazon SCT to Azure SQL Database and import the schema
4. Convert Azure SQL Database schema to MySQL schema
5. Create an Amazon DMS Replication instance
6. Defining an Amazon DMS Endpoint
7. Create an Amazon DMS migration task

Step 1: Create an Amazon Aurora MySQL Compatible Cluster

If you already have an Aurora MySQL cluster that you want to use as a migration target, you can use it out of the box. If you are creating a new Aurora MySQL cluster, see the detailed instructions in Creating an Amazon Aurora DB Cluster .

Step 2: Create an Amazon EC2 instance for installing the Amazon Schema Conversation Tool (Amazon SCT)

When creating an EC2 instance, select the t3.medium instance type and Windows Server 2016 or later as the operating system. For an introduction to how to create an EC2 instance, see the Amazon Elastic Compute Cloud Windows Instances User Guide .

For information about how to install Amazon SCT, see Installing, Verifying, and Updating Amazon SCT .

Step 3: Connect Amazon SCT to Azure SQL Database and Import Schema

Open the SCT console and select File, New project wizard, as shown in Figure 2.

Figure 2. Open a new schema transformation project

Create a new database migration project, as shown in Figure 3.

Figure 3. Create a new database migration project

Type the desired project name in the Project Name field.

If not already selected, select Azure SQL Database for Source Engine.

Select Next and fill in the Azure SQL database connection details as shown in Figure 4.

Figure 4. Connecting to the source database

Select Test Connection and if the test is successful, the Next button will be highlighted in blue. Select Next. If the test is unsuccessful, resolve any errors and re-select Test Connection.

Note: Remember to add the Amazon EC2 instance public IP to the Azure SQL Database firewall as shown in Figure 5 below.

Figure 5. Configure AzureDB Firewall to allow access

The system will confirm the database, as shown in Figure 6.

Figure 6. Successful connection to the source database

Select Next and SCT will run the database migration assessment. The assessment will provide details on the complexity of the migration schema between different database engines, as shown in Figure 7 below.

Figure 7. SCT assessment report

Choose Next, and type the following details for the Aurora MySQL-compatible database:

Check the Store Password check box to store the password in the SCT, as shown in Figure 8.

Figure 8. Connecting to the target database

Now that you have created a new database migration project, the next step is to convert the source database's Azure SQL database schema to an Amazon Aurora (MySQL) schema.

Select the View button, and then select the Assessment Report view, as shown in Figure 9.

Figure 9. Database Migration Assessment Report

A summary of the schema migration is displayed. Select the Action Items tab to view the individual items, as shown in Figure 10.

Figure 10. Summary view of action items

Expand the rows to see the suggested SQL changes, as shown in Figure 11. The SQL code can be edited to update as needed.

Figure 11. Detailed view of each action item

Figure 11 Legend:

• Green - automatic conversion
• Blue – requires investigation and manual conversion

Step 4: Convert Database Schema to MySQL Schema

Select the source database (SalesLT), as shown in Figure 12 below, right-click and select Convert Schema. If you receive the message These objects might already exist in the target database.Replace?, choose Yes.

Figure 12. Converting the source database

Select the target database (testdb_SalesLT) as shown in Figure 13 below, right-click and select Apply to database. If you receive the message You chose to apply the schema definition for testdb_SalesLT. Are you sure?, select Yes.

Figure 13. Applying the transformation to the target database

At this stage, the schema is now applied to the target database. Expand the right menu (Target) and you can now see the converted schema.

Step 5: Create an Amazon DMS Replication Instance <br>Navigate to the Amazon Cloud Technology Management Console, log in to your Amazon Cloud Technology account, and enter Database Migration Service in the search bar. Select the Database Migration Service result and you will be taken to the Amazon DMS page (see Figure 14 below) where you will select Create replication instance and the information in the table below will guide you through the setup :

After completing the configuration (as shown in Figure 14), select the CREATE button.

Figure 14. Creating a replication instance

Notes:
After creating the replication instance, change the security group from Default (as originally set) to the desired security group. For testing purposes, it can be left at Default (the original setting).

Add the DMS instance public IP to the Azure SQL Database firewall to allow access.

Step 6: Define Amazon DMS Endpoints <br>After logging into the Amazon Cloud Management Console, navigate to Amazon DMS and choose Endpoints, then create the following source endpoints:

Be sure to replace the example values in bold italics above with the values associated with your specific AzureSQL database.

Figure 15. Create source endpoint with AzureSQL connection details

After the configuration is created, expand Test endpoint connection (optional) as shown in Figure 15. Select the VPC and replication instance, then select RUN TEST, as shown in Figure 16.

Be sure to replace the bold italic example values above with the values associated with your specific VPC and replication instance.

Figure 16. Test the connection to the source endpoint

After the endpoint connection test is successful, select the CREATE ENDPOINT button in Figure 15.

Once logged into the Amazon Cloud Management Console, navigate to Amazon DMS, select Endpoint, and create the following target endpoint:

Be sure to replace the example values in bold italics above with the values associated with your specific Amazon Aurora MySQL-compatible database.

Figure 17. Create target endpoint with Amazon Aurora MySQL compatible connection details

After creating the configuration in Figure 17, expand Test endpoint connection (optional). Select the VPC and replication instance, then select RUN TEST, as shown in Figure 16.

Be sure to replace the bold italic example values above with the values associated with your specific VPC and replication instance.

After the endpoint connection test is successful, select the CREATE ENDPOINT button in Figure 17.

Step 7: Create an Amazon DMS Migration Task

Once logged into the Amazon Management Console, navigate to Database Migration Task and select CREATE TASK to create the following task, as shown in Figure 18.

Figure 18. Create Database Migration Service

Complete the Database Migration Service configuration by updating the Table Mappings section, as shown in Figure 19.

Figure 19. Creating source/target table mappings in Amazon Database Migration Service

Select CREATE TASK.

After the task is created, the migration of the Azure SQL database will start automatically, and the migration progress will be displayed in the console, as shown in Figure 20. Select Identifier and select the Table statistics option block to view migration statistics for individual tables, as shown in Figure 21.

Figure 20. Data Migration Status

Figure 21. Table Migration Statistics

At this point, all tables and their data belonging to the specified schema have been transferred from the Azure SQL Database to the Amazon Aurora MySQL-compatible database. You can use your favorite MySQL client, such as MySQL Workbench, to verify that all data was transferred.

in conclusion

In this blog post, we demonstrated how to use the Amazon Schema Conversion Tool and Amazon Data Migration Service to migrate database schema and data from AzureSQL to an Amazon Aurora MySQL-compatible database. Try it !

Author of this article

Ravin Matoora

Ravin Matoora is a Solutions Architect at Amazon Cloud Technologies. He works with clients to migrate and modernize workloads with sustainability as a guiding design principle.

Mark Brealey

Mark Brealey is a Senior Migration Solutions Architect assisting Amazon Cloud Technology partners in the EMEA region. He works with Amazon Cloud Technologies customers to design scalable, secure, high-performance, and reliable architectures on the cloud.