Migration to SQL Server on Azure VM

As you adopt and embrace the cloud platform, some of the on premise workload needs to be migrated to Azure for the benefit of cost and efficiency. Usually the Dev/Cert/QA/test environments are the good candidates for initial migration since these servers have minimum business impact and need not be running 24X7. Migrating these environments also gives maximum cost benefits by running the VMs only during business hours and turning it OFF when not in use.

In this chapter, we will discuss the various methods for migrating on premise SQL Server databases to SQL Server on Azure VMs.

Choosing the right method for migration

The primary migration methods are:

• Use the Deploy a SQL Server Database to a Microsoft Azure VM wizard
• Perform on-premises backup using compression and manually copy the backup file into the Azure virtual machine
• Perform a backup to URL and restore into the Azure virtual machine from the URL
• Detach and then copy the data and log files to Azure blob storage and then attach to SQL Server in Azure VM from URL
• Convert on-premises physical machine to Hyper-V VHD, upload to Azure Blob storage, and then deploy as new VM using uploaded VHD
• Ship hard drive using Windows Import/Export Service
• Use Always-On to Add a replica in Azure and failover to migrate.

The method used for migration is primarily dependent on the target version of SQL Server instance in Azure VM and size of the database backup. The following table lists each of the primary migration methods and discusses when the use of each method is most appropriate.

Table 2: Choosing the right method for migration

The above table and the methods for migration are taken from Carl Rebeler’s following article

https://azure.microsoft.com/en-us/documentation/articles/virtual-machines-migrate-onpremises-database/#ship-hard-drive

Use the Deploy a SQL Server Database to a Microsoft Azure VM wizard

This wizard available in SQL Server Management Studio (SSMS) is the recommended method for migrating an on-premises user database running on SQL Server 2005 or greater to SQL Server 2014 or greater when the compressed database backup file is less than 1 TB.

Before using the wizard, ensure to download and install the latest version of SSMS on the client workstation. The latest version of this wizard incorporates the most recent updates to the Azure portal and supports the newest Azure VM images in the gallery (older versions of the wizard may not work).

This method requires us to configure an open endpoint for the SQL Server Cloud Adapter service on the Microsoft Azure gateway with private port of 11435. This port is created as part of SQL Server 2014 or SQL Server 2016 provisioning on a Microsoft Azure VM. The Cloud Adapter also creates a Windows Firewall rule to allow its incoming TCP connections at default port 11435. This endpoint enables the wizard to utilize the Cloud Adaptor service to copy the backup files from the on-premises instance to the Azure VM. For more information, see Cloud Adapter for SQL Server.

You can use the following Azure PowerShell Cmdlet to add a SQL Server Cloud Adapter Endpoint on port 11435.

Code Listing 15: Adding an SQL Server Cloud Adapter Endpoint to Azure VM

This method involves the following steps

1. Open Microsoft SQL Server Management Studio for Microsoft SQL Server 2016 and connect to the SQL Server instance containing the user database that you will be migrating to an Azure VM.
2. Right-click the database that you are migrating, point to Tasks and then click Deploy to a Microsoft Azure VM as shown in the following figure.

Figure 6: Deploy Database to Microsoft Azure VM Wizard

3. On the introduction page, click Next and On the Source Settings page, connect to the SQL Server instance containing the database to migrate.
4. On the Source setting page, Specify a temporary location to hold the backup files which is read-write accessible to the source SQL Server instance and Click Next
5. On the Microsoft Azure Sign-in Page, Sign-in to your Azure account and select the subscription you want to use.
6. On the Deployment Settings page, you can specify a new or an existing Cloud Service name and Virtual Machine name:

• Specify a new Cloud Service Name and Virtual Machine name to create a new Cloud Service with a new Azure virtual machine using a SQL Server 2014 or SQL Server 2016 Gallery image.

• If you specify a new Cloud Service name, specify the storage account that you will use.
• If you specify an existing Cloud Service name, the storage account will be retrieved and entered for you.

• Specify an existing Cloud Service name and new Virtual Machine name to create a new Azure virtual machine in an existing Cloud Service. Only specify a SQL Server 2014 or SQL Server 2016 gallery image.
• Specify an existing Cloud Service name and Virtual Machine name to use an existing Azure virtual machine. This must an image built using a SQL Server 2014 or SQL Server 2016 gallery image.

7. Click Settings in Deployment Settings page,

If you specified an existing Cloud Service and VM, you will prompted for admin username and password to connect to the VM.

If you specified a new virtual machine, you will prompted to select the following options

• Image – Select SQL 2014 or SQL 2016 Images from the Azure Gallery
• Username
• New password
• Confirm password
• Location
• Size

In addition, you need to click check box to accept the creation of new self-generated certificate for this new Microsoft Azure Virtual Machine.

8. Specify the target database name if different from the source database name. If the target database already exists, the system will automatically increment the database name rather than overwrite the existing database.
9. Click Next and click Finish to start the migration.

The wizard takes a compressed backup of the database to the specified backup location and Cloud Adaptor service within the VM is used to restore the backup into the target SQL instance in Azure VM.

The time taken by the wizard is dependent on the backup size and time to copy the compressed backup across network and restore time which in turn is dependent on the Azure VM Compute Size.

If you created a new virtual machine, configure the Azure virtual machine and the SQL Server instance by following the steps mentioned in the previous chapter.

Perform a compressed backup on premise and copy the files to Azure VM

Use this method when you cannot use the Deploy a SQL Server Database to a Microsoft Azure VM wizard either because you are migrating to a version of SQL Server prior to SQL Server 2014 or your backup file is larger than 1 TB. If your backup file is larger than 1 TB, you must stripe it because the maximum size of a VM disk is 1 TB. Use the following general steps to migrate a user database using this manual method:

1. Perform a compressed full database backup to an on-premises location.
2. Copy your backup file(s) to your VM using remote desktop, Windows Explorer or the copy command from a command prompt.
3. Restore the database from the copied backup file to the SQL Server instance in Azure VM

Perform a backup to URL and restore it from URL

This method is no different from the previous method except for the fact that data is backed up directly to Azure blob storage and is restored from the same location. The following article provides the steps to create a backup to Azure Blob Storage URL:

https://msdn.microsoft.com/library/dn435916.aspx

Microsoft introduced backup to URL option starting with SQL 2012 SP1 CU2; hence, this method can only be used when the source SQL Server instance is already on or above SQL 2012 SP1 CU2.

Use the backup to URL method when you cannot use the Deploy a SQL Server Database to a Microsoft Azure VM wizard because your backup file is larger than 1 TB and you are migrating from and to SQL Server 2016. For databases smaller than 1 TB or running a version of SQL Server prior to SQL Server 2016, use of the wizard is recommended. With SQL Server 2016, striped backup sets are supported, are recommended for performance, and required to exceed the size limits per blob. For very large databases, the use of the Windows Import/Export Service is recommended.

Detach the database, copy to URL and attach it from URL

Use this method when you plan to store these files using the Azure Blob storage service and attach them to SQL Server running in an Azure VM, particularly with very large databases. Use the following general steps to migrate a user database using this manual method:

1. Detach the database files from the on-premises database instance.
2. Copy the detached database files into Azure blob storage using the AZCopy command-line utility.
Attach the database files from the Azure URL to the SQL Server instance in the Azure VM.

Convert to VM and upload to URL and deploy as new VM

This method is same as we discussed previously in the topic Creating a Syspreped image of SQL Server in Hyper-V & adding it to Azure. This method is useful when you want to bring your own license and migrate the entire SQL instance along with the user and system databases to Azure VM.

Ship hard drive using Windows Export/Import Service

Microsoft Azure team introduced Windows Import/Export Service method couple of years back as an efficient solution for transferring large volume of on premise data into Azure blob storage This method is used when volume of data to be transferred is too high and transferring it over the network may be prohibitive or may not be a feasible solution. With Windows Import/Export service you can ship TBs of encrypted data via hard drives through FedEx to Azure data centers.  The primary requirements for this method is only 3.5 inch SATA II/III hard drive are supported with maximum size up to 6 TB. Further the hard drives needs to be bit locker encrypted before sending it to Azure Data centers. The detailed steps to transfer the data using Windows Import/Export Service is mentioned in the following article

https://azure.microsoft.com/en-us/documentation/articles/storage-import-export-service/

Use Always-On to add a replica in Azure and failover to migrate

This method is useful when you want to minimize the failover time for migration. In this method, we add the SQL Server instance on Azure VM as an Azure replica of the on premise SQL Server instance using the new Add an Azure replica wizard. This method requires a hybrid-IT environment where on premise subnet has a site-to-site VPN with Windows Azure. After the always-on availability group is set up and synchronized, you can failover at any time and go live with the database on Azure. The on premise database can then be removed from availability group and decommissioned once the migration is successful.