Amazon Web Services EC2 is a well-known Infrastructure as a Service (IaaS) platform, and several organizations use EC2 to host various RDBMS platforms, including Microsoft SQL Server. Using an IaaS platform in the cloud provides certain advantages, such as ease of application migration, full control of software running in the VM, and simplified integration with other enterprise infrastructure. However, there are also some disadvantages to using an IaaS platform, such as cost and the need to perform many facets of HADR and VM management.
As a result, many customers, after an initial foray into the cloud using RDBMS on an IaaS platform such as EC2, subsequently decide to move to a PaaS (Platform as Service) RDBMS offering, such as Microsoft Azure SQL Database. Moving to Azure SQL Database minimizes administration costs, provides 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 one of the many ways to migrate a database hosted in an instance running in an AWS EC2 VM 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 DACFx, the Data Migration Assistant (DMA), etc., we are using Azure DMS 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.
- First, create a VM in AWS EC2 that hosts a SQL Server 2016 instance.
2. Assign a public, Elastic IP address and Public DNS to that instance to be able to connect to it from outside the AWS network. An Elastic IP address is a public static IPv4 address that is reachable from the internet.
If your instance does not have a public IPv4 address, you can associate an Elastic IP address with your instance to enable communication with the internet; for example, to connect to your instance from your local computer. For more on EC2 instance IP and DNS see the AWS article Public IPv4 Addresses and External DNS Hostnames.
Since the instance can be accessed remotely via a Public IP\DNS, configure the following security items in EC2:
- Security Groups. Security groups act as a virtual firewall for your instance to control inbound and outbound traffic at the instance level (not the subnet level). Use security group rules to open inbound TCP traffic access to ports 3389 (RDP) and 1433 (SQL Server) only to a certain subset of source IP addresses that will be used to connect to this instance. For more on AWS EC2 Security Groups, see the article Security Groups for Your VPC.
- VPC Access Control Lists (ACLs). The ACL acts as a firewall for controlling traffic into and out of a subnet. For more information about defining ACLs on your AWS VPC, see the article Network ACLs.
3. Now, use VPN tunneling to connect the source instance located in AWS to the target Azure environment for secure communication and data transfer. Set up a secure IPSEC tunnel between AWS and Azure following the method shown in the TechNet blog posting Step-by-Step: Connect your AWS and Azure environments with a VPN tunnel.
The general method for connecting between Azure Virtual Network and AWS VPC via secure tunnel is shown in the following graphic.
4. After setting up and verifying connectivity to the AWS EC2 SQL Server instance from the outside and from Azure, migrate the sample AdventureWorks2014 database that is hosted in that instance.
Before you can migrate data from an on-premises SQL Server instance to Azure SQL Database, you need to assess the SQL Server database for any blocking issues that might prevent migration.
5. Use DMA v3.3 or later to complete the on-premises 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.
6. 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).
7. 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.
8. Next, use DMA to migrate the AdventureWorks2014 database schema from the EC2 source to the Azure target:
a. In DMA, select the New (+) icon, and then under Project type, select Migration.
b. 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.
c. Under Migration Scope, select Schema only.
After performing the previous steps, the DMA interface should appear as shown in the following graphic:
d. Select Create to create the project.
e. In DMA, specify the source connection details for the AWS EC2 SQL Server, select Connect, and then select the AdventureWorks2014 database.
f. 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.
g. 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.
h. Select Generate SQL script to create the SQL scripts, and then review the scripts for any errors.
i. 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.
9. Next, use Azure DMS to transfer data from the source EC2 VM 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.
a. Log in to the Azure portal, select All services, and then select Subscriptions.
b. For the Azure subscription in which you want to create the Azure DMS instance, select Resource providers.
c. Search for migration, and then to the right of Microsoft.DataMigration, select Register.
The Microsoft.DataMigration provider is now registered with your subscription.
Now, create an Azure DMS instance in Microsoft Azure. Before proceeding, it is strongly recommended to review the following items:
10. After ensuring that everything is in order, proceed with the following steps:
a. 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.
b. On the Azure Database Migration Service (preview) screen, select Create.
c. 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.
11. After creating the service, locate it in the Azure portal:
a. In the Azure portal, select All services, search for Azure Database Migration Service, and then select Azure Database Migration Services.
b. On the Azure Database Migration Service screen, search for the name of the Azure DMS instance that you created, and then select the instance.
12. 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.
a. On the Azure Database Migration Service page, select the link for the Azure Resource Group in which you created your service.
This will take you to the Azure Resource Group Overview page:
b. Select the Network Interface for the Azure DMS instance you created (it is usually named in the pattern <NIC-stringnumericGUIDidentifier>).
Make a note of the private IP assigned to this Network Interface.
c. To whitelist this IP, navigate to the AWS Security Group for the source VM, and the select the Inbound rules tab.
d. Select Edit to add another inbound rule, and then add TCP port 1433 to the Azure DMS private IP as a permitted source.
If you are using VPC ACL for source IP filtering, be sure to add the Azure DMS IP to your ACL rule.
After Azure DMS can access the source, you need to provide it with the ability to access the target Azure SQL Database instance.
13. In the Azure portal, navigate to the Firewall section of your target SQL Azure virtual server and add a rule to allow TCP traffic from the Azure DMS IP on port 1433.
14. Now, create a new migration project.
a. On the Azure Database Migration Service portal overview page, select + New Migration Project.
b. 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.
c. Select Create to create a new project.
d. On the Source details screen, specify the connection details for the source SQL Server on AWS EC2 by using Public DNS.
e. Select Save, and then select the AdventureWorks2014 database for migration.
f. 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.
g. Select Save to save the project.
h. On the Migration summary screen, review and verify the details associated with the migration project, and then select Save.
15. After creating the migration project, run the actual migration activity by using the following steps:
a. Select the recently saved project, select + New Activity, and then select Run Data Migration.
b. When prompted, enter the credentials for the source and the target servers, and then select Save.
c. 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.
d. On the Select tables screen, review the list of tables to be migrated.
e. 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.
f. Finally, select Run migration to start the migration activity, and then select Refresh to review the status.
After approximately 90 seconds, migration of 191 MB database finishes and you can verify the data in the target tables.
- Step-by-Step: Connect your AWS and Azure environments with a VPN tunnel - https://blogs.technet.microsoft.com/canitpro/2016/01/11/step-by-step-connect-your-aws-and-azure-environments-with-a-vpn-tunnel/
- Migrate SQL Server on-premises to Azure SQL Database by using the Azure Database Migration Service https://docs.microsoft.com/en-us/azure/dms/tutorial-sql-server-to-azure-sql
- Azure Database Migration Service Prerequisites - https://docs.microsoft.com/en-us/azure/dms/pre-reqs
- Azure Database Migration Service FAQ - https://docs.microsoft.com/en-us/azure/dms/faq
- Azure Database Migration Guide - https://aka.ms/datamigration