Migrating SQL Server databases from a Google Compute Engine instance to Azure SQL Database using the Azure Database Migration Service


Google Cloud Platform is a suite of public cloud computing services offered by Google. The platform includes a range of hosted services for compute, storage, and application development that runs on Google hardware. Google Cloud Platform (GCP) services can be accessed by software developers, cloud administrators, and other enterprise IT professionals over the public internet or through a dedicated network connection. Google Compute Engine (GCE), which is an infrastructure-as-a-service (IaaS) offering that provides users with virtual machine instances for workload hosting, allows users to host number of popular RDBMS databases, such as SQL Server or Oracle, on virtual machines.  As with other public IaaS services, the GCE can help ease customers’ application migration to the cloud, reduce their infrastructure costs, and scale up\down capacity based on demand.

However, after the initial migration to IaaS, many customers ultimately look to move to Platform as Service (PaaS) database services, such as Microsoft’s Azure SQL Database, to save on costs, infrastructure management, HADR complexity, etc. Using 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 to set up elasticity to accommodate variable workloads.

This blog posting illustrates one of the ways to migrate SQL Server databases hosted on a GCE VM to Microsoft Azure SQL Database by using the Azure Database Migration Service (Azure DMS), which is currently available in public preview. This new service provides a better at scale experience for migrations than do many existing methods, as explained in the blog entry Differentiating Microsoft Database Migration Tools and Services.

To begin with, we need to set up a source, in this case a GCE SQL Server 2016 VM, which was prepared following available GCP documentation.

This source VM is configured with a Public\External IP that is reachable from the internet. To secure this VM, GCE provides a cloud firewall, which by default doesn’t allow external traffic to reach this VM. If traffic needs to reach this VM and SQL Server instance from outside GCE than firewall exception\rule needs to be configured.  For more on GCE VPC firewall rules, see the article - https://cloud.google.com/vpc/docs/using-firewalls.

For migration purposes, a VPN tunnel between the source in the GCP network and the Azure network needs to be set up.  To accomplish this, see the GCP guide available at https://cloud.google.com/files/CloudVPNGuide-UsingCloudVPNwithAzureVPN.pdf.  This document provide detail on how to set up a basic Site­to­Site IPsec VPN tunnel configuration between GCP and Azure by using the standard Azure VPN Gateway. The design used here is shown in the following network diagram:

After verifying connectivity from outside and Azure to our GCE SQL Server instance, we will migrate the sample AdventureWorks2012 database hosted in that instance.

Before migrating data from an IaaS SQL Server instance to Azure SQL Database, we need to assess the SQL Server database for any blocking issues that might prevent migration. Using the Data Migration Assistant (DMA) v3.3 or later, complete the on-premises database assessment by following the steps provided in the article Performing a SQL Server migration assessment.

After performing the assessment and confirming that there are no blocking issues preventing migration the AdventureWorks2012 database, we proceed by creating the target Azure SQL Database environment using the detail provided in the article Create an Azure SQL database in the Azure portal. You can also refer to a great video by Scott Hanselman, which shows the same process. Using the above tutorials, we create the target Azure SQL Database instance named AdventureWorks2012 in Premium pricing tier (P1).

After creating the target database, be sure to create rules on the virtual Azure SQL Server firewall to allow the needed client IP ranges to connect to this Azure SQL Database instance.

With the target Azure SQL Database is provisioned and the client IP ranges opened for SQL connectivity, we can proceed with migrating the schema of the AdventureWorks2012   database from our GCE VM source to our Azure target by using the Data Migration Assistant.

  1. In DMA, select the New (+) icon, and then under Project type, select Migration.
  2. 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.

5.  In DMA, specify the source connection details for the GCP VM-based SQL Server, select Connect, and then select the AdventureWorks2012 database.

6. Select Next, under Connect to target server, specify the target connection details for the Azure SQL database, select Connect, and then select the AdventureWorks2012 database that we previously provisioned in Azure SQL database.

7. Select Next to advance to the Select objects screen, on which you can specify the schema objects in the AdventureWorks2012 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.

Next, we will use Azure DMS to transfer data from the source GCE VM hosted SQL database to the Azure SQL Database target. However, before we can use the service, we need to register the Microsoft.DataMigration resource provider. This lets Azure know that we to associate this service provider with our Azure subscription.

10.      Log back into Azure portal, select All services, and then select Subscriptions.

11. In the Azure subscription where we want to create the instance of the Azure Database Migration Service, select Resource providers.

12. Search for migration, and then to the right of Microsoft.DataMigration, select Register.

We have now registered the Microsoft.DataMigration provider with our subscription.

Now, we will create an instance of Azure DMS in Microsoft Azure. Before proceeding it is strongly recommended to revew the following items:

After reviewing the above documents, we can proceed.

13.      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.

14.      On the Azure Database Migration Service (preview) screen, select Create.

15.      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 bit, the service will be created.

16.      In the Azure portal, select All services, search for Azure Database Migration Service, and then select Azure Database Migration Services.

17. On the Azure Database Migration Services screen, search for the name of the Azure DMS instance that we created, and then select the instance.

 

The next step is to give our service access to source database that will be involved in the migration. What is important to note here is that above we were able to set up VPN tunneling between GCE  and Azure so that our Azure DMS private IP can be whitelisted with our source security group and added to our VPC ACL if needed.

18.      On the Azure Database Migration Service page, select the Azure Resource Group link for the service we created to view the Azure Resource Group Overview page:

 

19.      Select the Network Interface for the Azure DMS instance we created (it is usually named in pattern <NIC-stringnumericGUIDidentifier>).

 

20.      Make a note of the private IP assigned to this Network Interface.

21.      To whitelist this IP, navigate to GCP VPC firewall page for our source GCP VPC, and select Create Firewall Rule.

Here you can create new rule that will allow traffic from Azure DMS to the source on port our source SQL Server instance is listening to (in my case default 1433)

After Azure DMS can access the source, we need to give it the ability to access the target Azure SQL Database instance.

22.      In the Azure portal, navigate to the Firewall section of the target Azure SQL virtual server, and then create a rule to allow tcp traffic from Azure DMS IP on port 1433.

The next step is to create a new Azure Database Migration Service migration project.

23.      On the Azure Database Migration Service portal overview page, select the + New Migration Project menu.

24.      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.

25. Select Create to create new project.
26. On the Source details screen, specify the connection details for the source SQL Server on GCE VM using the Public IP.

27. Click Save, and then select the AdventureWorks2012 database for migration.

 

28. Select Save again, and then on the Target details screen, specify the connection details for the target, which is the Azure SQL Database instance to which the AdventureWorks2012 schema was deployed by using DMA.

 

29.      Select Save to save the project target details.

30.      On the Migration summary screen, review and verify the details associated with the migration project, and the select Save.

After we create the migration project, we will actually run a migration activity.

31. Select the recently saved project, select + New Activity, and then select Run Data Migration.

32.      When prompted, enter the credentials for the source and the target servers, and then select Save.

33.      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.

 

34.    On the next screen, review the list of tables being migrated

35. Select Save, on the Migration summary screen, in the Activity name text box, specify a name for the migration activity.

Note: This screen also enables you to set up post migration validation, which we will skip for this exercise, but it is something you may wish to perform for production database migrations/

 

36.   Finally, select Run migration to start the migration activity, and then select Refresh to review the status.

Additional resources

 


Comments (0)

Skip to main content