Migrating SQL Server databases from AWS RDS to Azure SQL Database using the Azure Database Migration Service


Amazon Web Services Relational Database Service is a well-known platform that several organizations use to host various RDBMS offerings, including Microsoft SQL Server. Customers, after an initial foray into the cloud using RDBMS on platform such as AWS RDS, decide to move to a PaaS (Platform as Service) RDBMS offering, such as Microsoft Azure SQL Database because it provides minimized administration costs, high availability out-of-the-box with scalable service plans for multiple needs and budgets, and offers easy set up for elasticity to accommodate variable service workloads.

The purpose of this blog posting is to demonstrate way to migrate a database hosted in an AWS RDS instance to Microsoft Azure SQL Database using the Azure Database Migration Service (Azure DMS), which is currently in public preview. Rather than other, well known migration methods such as BacPac,  we are using Data Migration Assistant to migrate Schema and Azure DMS to migrate data because it is a more robust, cloud-based solution for performing migrations at scale.

Note: For a more detailed explanation of the various migration options provided by Microsoft, see the blog posting Differentiating Microsoft Database Migration Tools and Services.

  1. In the AWS console, create RDS instance running SQL Server.
  2. After setting up and verifying connectivity to the AWS RDS SQL Server instance from the outside and from Azure, migrate the sample AdventureWorks2014 database that is hosted in that instance.
  1. Before you can migrate data from SQL Server instance to Azure SQL Database, you need to assess the SQL Server database for any blocking issues that might prevent  migration.Use DMA v3.4.3981 or later to complete the  database assessment by following the steps described in the article Performing a SQL Server migration assessment.  Since you are using the sample AdventureWorks2014 database and have performed an assessment and reviewed the results, you can confidently move on to next stages of the activity.
  1. Before proceeding further, you need to create a target Azure SQL Database instance in Microsoft Azure. For details on how to accomplish this, see the article Create an Azure SQL database in the Azure portal. There is also great video by Scott Hanselman that shows how to complete this process. Referring to these resources, create a target Azure SQL Database instance named AdventureWorks2014 in the Premium pricing tier (P1).
  1. After creating the target database, be sure to set up firewall rules on the virtual Azure SQL Server so that the necessary client IP ranges can connect to the Azure SQL Database instance.
  1. Next, use DMA to migrate the AdventureWorks2014 database schema from the RDS source to the Azure target:
    1. In DMA, select the New (+) icon, and then under Project type, select Migration.
    2. Next, specify a project name, in the Source server type text box select SQL Server, and then in the Target server type text box, select Azure SQL Database.
    3. Under Migration Scope, select Schema only.After performing the previous steps, the DMA interface should appear as shown in the following graphic:
    4. Select Create to create the project.Next, use DMA to migrate the AdventureWorks2014 database schema from the RDS source to the Azure target.
    5. In DMA, specify the source connection details for the AWS RDS SQL Server, select Connect, and then select the AdventureWorks2014 database.
    6. Under Connect to target server, select Next, specify the target connection details for the Azure SQL Database instance, select Connect, and then select the AdventureWorks2014 database that was pre-provisioned in Azure SQL Database.
    7. Select Next to advance to the Select objects screen, which is used to specify the schema objects in the AdventureWorks2014 database that need to be deployed to Azure SQL Database.By default, all objects are selected.
    8. Select Generate SQL script to create the SQL scripts, and then review the scripts for any errors.
    9. Select Deploy schema to deploy the schema to Azure SQL Database, and then after the schema is deployed, check the target server for any anomalies.
  1. Next, use Azure DMS to transfer data from the source RDS hosted SQL database to Azure SQL Database target. Note that before you can use the service, you need to register the Microsoft.DataMigration resource provider so that Azure knows that you want this service provider associated with your Azure subscription.
    1. Log in to the Azure portal, select All services, and then select Subscriptions.
    2. For the Azure subscription in which you want to create the Azure DMS instance, select Resource providers.
    3. Search for migration, and then to the right of Microsoft.DataMigration, select Register.

      The Microsoft.DataMigration provider is now registered with your subscription.
    4. Now, create an Azure DMS instance in Microsoft Azure. Before proceeding, it is strongly recommended to review the following items:
      - Azure DMS Prerequisites·
      - Azure DMS FAQ
  1. After ensuring that everything is in order, proceed with the following steps:
    1. In the Azure portal, select + Create a resource, search for Azure Database Migration Service, and then select Azure Database Migration Service from the drop-down list.
    2. On the Azure Database Migration Service (preview) screen, select Create.
    3. On the Database Migration Service screen, specify a name for the service, the subscription, the Azure virtual network, and the pricing tier, and then select Create,.

     After a few moments, the service will be created.

  1. After creating the service, locate it in the Azure portal:
    1. In the Azure portal, select All services, search for Azure Database Migration Service, and then select Azure Database Migration Services.
    2. On the Azure Database Migration Services screen, search for the name of the Azure DMS instance that you created, and then select the instance.
  1. Next, you need to give the service access to the source database that you want to migrate. What is important here is that above we were able to set up VPN tunneling between AWS and Azure so that the Azure DMS private IP can be whitelisted with the source security group and added to the VPC ACL if needed. After Azure DMS can access the source, you need to provide it with the ability to access the target Azure SQL Database instance.
  1. In the Azure portal, navigate to the Firewall section of your target SQL Azure virtual server and create a rule to allow TCP traffic from Azure DMS IP on port 1433.
  1. Now, create a new migration project.
    1. On the Azure Database Migration Service portal overview page, select + New Migration Project.
    2. On the New migration project screen, specify a name for the project, in the Source server type text box, select SQL Server, and then in the Target server type text box, select Azure SQL Database.
    3. Select Create to create a new project.
    4. On the Source details screen, specify the connection details for the source SQL Server on AWS RDS by using connection string from AWS console.
    5. Select Save, and then select the AdventureWorks2014 database for migration.
    6. Select Save again, and then on the Target details screen, specify the connection details for the target, which is the provisioned Azure SQL Database instance to which you deployed the AdventureWorks2014 schema using DMA.
    7. Select Save to save the project
    8. On the Migration summary screen, review and verify the details associated with the migration project, and then select Save.
  1. After creating the migration project, run the actual migration activity by using the following steps:
    1. Select the recently saved project, select + New Activity, and then select Run Data Migration.

       
    2. When prompted, enter the credentials for the source and the target servers, and then select Save.
    3. On the Map to target databases screen, map the source and the target database for migration.If the target database contains the same database name as the source database, Azure DMS selects the target database by default.

       
    4. On the Select tables screen, review the list of tables to be migrated.

       
    5. Select Save, on the Migration summary screen, in the Activity name text box, specify a name for the migration activity.On this screen, you can also set up post migration validation. For the purposes of this post, do not bother setting up post-migration validation, but be sure to consider doing this for production database migrations.
    6. Select Run migration to start the migration activity.

       
    7. Finally, select Refresh to review the status.
Comments (0)

Skip to main content