AWS Database Blog

Migrate Azure SQL Database to Amazon Aurora using Azure Data Sync Agent and AWS DMS

Increasingly, customers are looking to break free from their legacy database (e.g., Oracle and Microsoft SQL Server) and move to a cloud-native database such as 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 PostgreSQL-Compatible Edition or Amazon Aurora MySQL-Compatible Edition cluster using AWS Database Migration Service (AWS DMS) and Azure Data Sync with a minimal migration window.

Our solution uses Azure Data Sync Agent and other AWS services to facilitate a seamless migration of Azure SQL databases into Aurora PostgreSQL with low downtime. The solution also comes with a deployment option using AWS CloudFormation, which automates deployment of AWS DMS and an Aurora PostgreSQL instance with Multi-AZ. This migration strategy is applicable to migrate Azure SQL databases to Aurora PostgreSQL and Aurora MySQL engines. For an option using CloudBasic, see Migrating SQL Server databases from Microsoft Azure to AWS in near-real time with CloudBasic.

Cost of the solution

The pricing is based on the AWS Pricing Calculator, which provides an estimate of your AWS fees (not including any taxes that might apply). Your actual fees depend on a variety of factors, including the actual usage of AWS services.

The estimated cost of running this environment on an on-demand pricing strategy monthly is $411.54. The estimation cost of running the environment per hour is $0.57. For this post, all our services are in the US East (N. Virginia) Region. Let’s break down the costs further:

  • Our Aurora PostgreSQL-compatible database (a 1-node cluster) has 100 GB of storage, instance type db.r5.large, and On-Demand pricing, totalling $222.75.
  • We use Amazon Elastic Compute Cloud (Amazon EC2) as a staging SQL Server instance with the Data Sync Agent. It uses Windows Server, On-Demand instances, 65 GB of storage, and instance type m5.large, for a monthly total of $143.74.
  • We use Amazon Virtual Private Cloud (Amazon VPC) to create a NAT Gateway, with a monthly cost of $37.35.
  • We also use a NAT Elastic IP address with two EC2 instances and 1 Elastic IP per instance, for a monthly cost of $7.30.
  • Lastly, we use AWS Secrets Manager to store an Aurora secret. Our number of API calls is 30 per month, and the average duration of the secret is 30 days. The monthly cost is $0.40.

Prerequisites

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

Also, you need to have already converted the schema of SQL Server to Aurora PostgreSQL using the AWS Schema Conversion Tool. AWS SCT is an excellent tool to migrate schemas with minimal effort. For more details, see Converting SQL Server to PostgreSQL.

Additionally, make sure the following AWS Identity and Access Management (IAM) roles are available in the account where you’re deploying the CloudFormation template:

  • DMS-VPC-ROLE
  • DMS-CLOUDWATCH-LOGS-ROLE

If these roles don’t exist, create them before you deploy the CloudFormation template.

Solution overview

Data migration from an Azure SQL database to an Aurora cluster is a staged migration process, in which we first pull the data from the Azure SQL database using the Data Sync Agent to a local SQL Server instance hosted on Amazon EC2 as a staging environment. From this point, AWS DMS moves the data from SQL Server on Amazon EC2 to an Aurora cluster using change data capture (CDC) as illustrated in the following diagram.

In this migration, we connect the Azure SQL database to a staging SQL Server instance deployed on Amazon EC2 using the Data Sync Agent, which starts the data transfer from the Azure SQL database to the EC2 instance. Then we configure and start the AWS DMS replication task to transfer the data from SQL Server on Amazon EC2 to the Aurora PostgreSQL cluster.

Create the sync job at the Azure SQL database source

To create your sync job at the source database (Azure SQL), complete the following steps:

  1. Log in to your Azure portal and navigate to the Azure SQL database you want to migrate to Aurora.
  2. On the database page, choose Sync to other database.
  3. For Sync Group Name, enter a name (for example, AzureAuroraSyncGroup).
  4. Turn Automatic Sync to ON.
  5. For Sync Frequency, enter a replication frequency.You can schedule replication as per your requirements. For this post, we use a 30 seconds frequency for replication.
  1. For Conflict Resolution, choose Hub win.
  2. Choose OK.
  3. Under Hub Database, enter a username and password for the Data Sync Agent (the same as your Azure SQL database credentials).
  4. For Membership Database, choose Add an On-Premises Database.
    You use these credentials in a later step.
  5. In the Sync Gateway agent section, select Create a new agent.
  6. For Agent name, enter a name.
  7. Create and generate an agent key.

You need to use this key if you reregister or reinstall the agent in the Amazon EC2 staging environment.

Build the infrastructure on AWS

To build your infrastructure, complete the following steps:

  1. Sign in to the AWS Management Console and deploy the CloudFormation template.
    AWS CloudFormation deploys the SQL Server along with the Data Sync Agent installed on Amazon EC2 (our staging environment). It also deploys an AWS DMS instance and Aurora PostgreSQL cluster.
  2. Provide values to the stack parameters as shown in the following table.
    Parameter Value Populated Purpose
    AgentKey Nothing Internal Internal AWS CloudFormation working.
    AmiID /aws/service/ami-windows-latest/Windows_Server-2019-English-Full-SQL_2017_Standard Internal Pulls the latest AMI for Microsoft SQL Server 2017 Standard edition.
    AzureSQLDBCIDR 0.0.0.0/0 User Azure SQL database IP address for inbound connectivity from Azure to the staging SQL Server on Amazon EC2.
    AzureSyncStageDBName AzureSyncStageDB Internal Staging database on Amazon EC2 SQL Server.
    EnvironmentType Prod Internal –
    KeyPairName KeyPairName User Select the key pair that you have access to. This key pair is used to connect to the staging Amazon EC2 SQL Server.
    MsSqlPort 1433 Internal Staging SQL Server port to allow communication between the AWS DMS instance and staging SQL Server.
    PostgresPort 5432 Internal PostgreSQL port to allow communication between the AWS DMS instance and the Aurora PostgreSQL cluster.
    Pramas Yes Internal –
    RdpPort 3389 Internal Allows you to use the staging SQL Server security group from your remote IP.
    ReplicationInstanceAllocatedStorage 50 Internal Storage for the AWS DMS replication instance.
    ReplicationInstanceClass dms.C5.large Internal The AWS DMS replication instance class. You can choose another class as needed for your use case.
    StagingSQLPassword **** Staging SQL Server password.
    deployCluster Yes –
    rdpAccessCIDR 0.0.0.0/0 Your remote IP to access the staging SQL Server over RDP.
  3. After the CloudFormation template is successfully deployed, go to the Outputs tab on the stack details page.
    The following table summarizes your outputs.

    Key Value Description
    AuroraCluster aurora-cluster Elastic IP assigned to Amazon EC2
    DBPassword xxxxxx Staging SQL Server user password
    KeyPair xxxxx Name of key pair in use
    SQLServerElasticIP xxxxxxx Elastic IP assigned to Amazon EC2
    SQLServerPrivateDNS xxxxxx Private DNS of EC2 instance
    SQLServerPublicDNS xxxxxx Public DNS of EC2 instance
    clusterEndpoint xxxxxx Aurora cluster endpoint
    clusterName xxxxxx Cluster name
    secretArn xxxxxx Database credentials secret ARN
    vpcId xxxxxx Environment VPC
  4.  Note the value for the staging environment instance ID.
  5. Get the Elastic IP for this instance and add that on the Azure database firewall rule to allow inbound connections.
  6. Log in to the Amazon EC2 staging environment.
  7. Log in to the local SQL Server using SSMS to check if AzureSyncStageDB exists.
  8. Check if the SQL Server login azuresqlagent exists.
  9. Test the login for azuresqlagent on the local SQL Server using the password from the CloudFormation stack outputs (the value for StagingDBPassword).
  10. Configure the Data Sync Agent with the agent key along with the Azure SQL database username and password you captured earlier.
  11. Configure the SQL data target as the local instance by choosing Register and logging in as azuresqlagent.

Sync the data from the Azure SQL database to the staging environment

For this step, return to the Azure portal session you had running when you created the sync job.

  1. Choose Add on-prem database.
  2. Select Existing agents.
  3. Choose the agent you registered earlier.
  4. For Sync Member Name, enter a unique name.
  5. For On-premises databases connected to this agent, choose your database.
  6. For Sync Directions, choose From the Hub.
  7. For Select a database, choose Hub Database.
  8. Select all the tables you want to replicate (all the tables should have a primary key).
  9. Choose Save.

A success message should appear in the portal and data should start flowing to your AWS staging database hosted on Amazon EC2.

Start the sync to Aurora via AWS DMS

For this step, return to the Outputs tab of the AzureDataSyncAurora stack and get the AWS DMS instance details. We log in to the AWS DMS instance and confirm that AWS CloudFormation deployed the following components:

  • Replication instance
  • Source endpoint
  • Target endpoint
  • Replication task—can be full load or ongoing replication (CDC)
  1. On the AWS DMS console, choose Endpoints in the navigation pane.
  2. On the Endpoints page, confirm the source endpoint (SQL Server pointing to the staging environment) and target endpoint (Aurora PostgreSQL pointing to the new Aurora cluster) are active.
  3. In the navigation pane, choose Database migration tasks.
  4. Verify that the source and target endpoints are pointing at the respective endpoints noted in the stack outputs.
  5. On the Actions menu, choose Restart/Resume to start the job.
  6. Verify the full load is complete and the job has moved to ongoing replication mode.

Now that the job is in ongoing replication, your data changes from Azure to Aurora PostgreSQL flow automatically (as per the sync schedule you set up for the Data Sync Agent).

Cut over

You’re now ready to cut over to Aurora PostgreSQL and test your application.

  1. Stop the application traffic to the Azure SQL database.
  2. Wait about 30 seconds to allow in-flight transactions to catch up to the staging environment.
  3. Stop the Azure SQL Data Sync Agent.
  4. Allow the migration task to catch up to the final changes from the Azure SQL database to Aurora PostgreSQL (as per the sync schedule you specified).
  5. Stop and remove the AWS DMS migration task.
  6. Point your application to Aurora PostgreSQL.

You’re now live with Aurora PostgreSQL.

Clean up the resources

Now that the tables have been successfully migrated from the Azure SQL database to Aurora, it’s time to clean up by deleting your migration task, replication task, and EC2 instance.

  1. On the AWS DMS console, choose Database migration tasks.
  2. Select your migration task and on the Actions menu, choose Stop.
  3. On the Actions menu, choose Delete.
  4. Choose Delete again to confirm.
  5. In the navigation pane, choose Replication instances.
  6. Select your replication instance and on the Actions menu, choose Delete.
  7. On the Amazon EC2 console, stop and delete your instance.

Advantages of this solution

This solution uses native tools from Azure and AWS services, which means you have support for all components of the solution.

It provides near-real-time replication and near-real-time migration with CDC, which gives you an option to cut over from Azure to AWS with minimal downtime.

The AWS service deployment is fully automated. You can start the migration and use the CloudFormation template for future enhancement if you choose to continue with the same platform.

Conclusion

In this post, I showed you how to combine Azure Data Sync and AWS DMS to migrate your Azure SQL database to Aurora with minimal downtime. The step-by-step guide helps you get hands-on experience migrating an Azure SQL database to Aurora PostgreSQL and Aurora MySQL using the Azure Data Sync Agent along with AWS SCT and AWS DMS.

If you’re looking modernize SQL Server or Oracle from on premises, refer to the following:

Leave your feedback in the comments so we can continue to improve upon this solution.

About the authors

Asif Mujawar is a Senior Specialist Database Solutions Architect based in London. A database professional with broad expertise and experience across the different industry verticals where he has been seen as a trusted advisor in the database space. In his role he helps customers to unlock opportunities by facilitating their cloud migration journey with an indexed focus on modernisation and democratisation of data.