Migrating SQL workloads to Microsoft Azure: Databases Trip to SQL Server on Azure VM
In the previous articles of this series, we built the bridge that leads us to the best way of migrating our SQL
workload to Microsoft Azure, by showing how to create the databases migration strategy that contains a deep study of
the current environment and the available options in Azure to replace the on-premises setup, which we tried to make
it easier by providing all Azure database services and the workload that fits each service, and finally, we
introduced several Microsoft tools and services that can be used to assess the current setup, provide migration
guidance steps and migrate the databases to Microsoft Azure services with the minimal downtime.
I recommend you go through these articles before stepping to this point of the article’s series:
In this article, we will show how to migrate a SQL Server database from an on-premises server to a SQL Server instance hosted in Azure VM.
Why Azure VM
Although the cost of hosting your SQL Server instance in an Azure VM is higher than using other PaaS services, still can be controlled by the compute and storage size of the VM, it comes with many benefits, include:
- Provides you with full administrative control over the operating system and the SQL Server instance installed in that virtual machine
- Ability to customize high availability, disaster recovery and backup plans
- Supports other SQL Server components that are not supported by the Azure SQL Database, such as SQL Server Transactional Replication, SQL Server Integration Services, SQL Server Analysis Services, SQL Server Reporting Services, Log Shipping and Always On Availability Groups
- Optimized for migrating on-premises SQL Server databases to Azure with the minimal required changes, due to 100% compatibility with all SQL Server versions 2005 and later
- Ability to host multiple databases in the same instance, allowing cross-database transaction execution
Before migrating your SQL Server databases to a Microsoft Azure VM, you should use the discovery and assessment
tools to make the migration process clear and smooth. These tools include the Microsoft Planning and Assessment tool that can be used to discover the on-premises environment configurations and components, the Data Migration Assistant tool that is used to assess the on-premises databases for any blocking
or compatibility issue that may affect the migration process and the Database Experimentation Assistant tool that is used to assess the target server for the current workload type.
After assessing the current environment, we should decide which license type we will use for the SQL Server Virtual
Machine, where Microsoft supports both the pay-as-you-go (PAYG) model, in which the license cost
will be included in the per-second billed cost, and the bring-your-own-license (BYOL) model, in which you can use your own SQL Server license in the Azure VM and billed for the VM usage only.
The next thing to consider here is the size of the virtual machine that will host the SQL Server instance in Microsoft Azure. The size of the machine should consider on-premises workload handling and growing requirements.
Take into consideration that the minimum VM size for SQL Server Enterprise editions DS3_v2, and DS2_v2 for the Standard edition. For more information about the VMs sizing in Azure, check the Size for Virtual Machines in Azure.
Prepare a VM on Microsoft Azure
Before we start the migration process, we should prepare the Azure Virtual Machine that will host the target SQL
Server instance. To create the SQL Virtual machine, search for SQL Virtual Machines in Azure Portal then click on Create SQL virtual machine, as below:
In the “Select SQL deployment option” page, choose the image that will be used to install the Operating System and the SQL Server instance, based on the license and version requirements, then click Create, as below:
In the Basic page of the Create a Virtual Machine window, provide the resource group under which you will create the VM, the region where the VM will be hosted, a unique name for the VM, the size for the VM and the administrative
account that will be used to connect to that VM. After providing the required information, click on Next to configure the SQL Server Settings:
In the SQL Server settings, configure the SQL Connectivity as Public to allow connections to the SQL Server over the
internet. Internally, the portal will enable the TCP/IP protocol for the SQL Server, configure a firewall rule to
open the configured TCP port, enable the SQL Server Authentication and configures the network security group on that
SQL VM to all TCP traffic on the configured TCP port. Click Review + Create to proceed with the VM
creation, as below:
Once the SQL VM is created successfully, open that VM from the Virtual Machines page and click on the
Connect option to create a connection to that machine, as shown below:
From the Connect page, we need to connect using RDP as this is a Windows machine. Click on Download RDP File option to download locally a .exe file that will be used to connect to that Microsoft Azure VM, as
After connecting to that machine, using the previously configured credentials, we will install the SQL Server
Management Studio in order to connect to the SQL Server on that machine, as shown below:
After installing the SSMS tool, open it and connect to the local SQL Server instance and you will see a normal SQL
Server instance that has no difference from the on-premises SQL Server instances you are familiar with, except for
that this instance is hosted in a Microsoft Azure VM, as follows:
SQL Server provides us with different methods that can be used to migrating the SQL Server databases from an
on-premises SQL Server instance to a SQL Server instance hosted in Microsoft Azure VM. The decision of which tool to
use depends on the planned downtime for the migration process, whether to automate the migration process or migrate
manually and the network bandwidth you have, with other factors based on each individual scenario. These SQL Server
databases migration methods include:
(Offline Migration) Take a compressed backup from the on-premises SQL Server database, with version 2005 and
later, copy it to the Azure VM then restore it to the SQL Server instance hosted in the Azure VM
(Offline Migration) Perform backup from the on-premises SQL Server instance, with version 2012 SP1 CU2 and
later, with the Azure Blob Container URL as the backup target, then restore to the SQL Server instance hosted in
the Azure VM from that Azure URL
(Offline Migration) Detach the database from the on-premises SQL Server instance, copy the database files to an
Azure Blob Storage using AZCopy command-line utility, then attach the database files to the SQL instance on that
Azure VM from the Azure Blob Storage URL, without the need to copy the database files to the Azure VM
(Offline Migration) Convert the on-premises physical machine to Hyper-V VHD, upload that VHD to Azure Blob
storage, then deploy the VHD as a new Microsoft Azure VM
(Offline Migration) Use the Data Migration Assistant to migrate the on-premises database schema and data to the
(Online) Configure the Always On Availability Group in the on-premises SQL Server instance, add the target Azure
VM as a replica using the Add Azure Replica wizard, or using the T-SQL commands, then perform a failover to make
the SQL Server instance hosted in the Azure VM as the primary replica, and direct the users to connect to the
new replica hosted in the Azure VM. This method provides us with the minimal possible downtime during the
(Online) Configure SQL Server Transactional Replication with the on-premises SQL Server instance as the
publisher and the SQL Server instance hosted in the Azure VM as the subscriber. After synchronizing the changes
between the two databases, you can disable the replication and direct the users to connect to the SQL Server
instance that is hosted in the Azure VM. This method also provides us with the minimal possible downtime during
the migration process
Based on the provided migration method, you can choose the method that meets your requirements and fits your own scenario.
In this demo, we will take a compressed backup and configure the destination of that backup as the Azure Blob
Storage URL, where you will be asked to provide the URL for the Azure Blob container where the backup will be stored
and the Shared Access Signature to authenticate the access to that container, as shown below:
After completing the backup process, we will restore the database backup file from that URL to the SQL Server
instance hosted in the Azure VM, again by providing the URL for the container where the backup is stored and the
Shared Access Signature to authenticate the access to that container, as shown below:
When you provide the container URL and SAS key, you will be able to access the container and choose the backup file
that will be restored to that SQL Server instance, as below:
When the Restore from the URL is completed, the database will be online in the SQL Server instance hosted in the
Microsoft Azure VM, and you can create the logins that should be granted permissions on that SQL Server instance
then point the database and application users to that instance, as below:
In this article, we went through the different considerations and methods that can be used to migrate an on-premises
SQL Server database to a SQL Server instance hosted in a Microsoft Azure VM.
In the next article, we will see how to migrate a SQL Server database from an on-premises server to an Azure SQL Database. Stay tuned!
Table of contents
Ahmad Yaseen is a Microsoft Big Data engineer with deep knowledge and experience in SQL BI, SQL Server Database Administration and Development fields.
He is a Microsoft Certified Solution Expert in Data Management and Analytics, Microsoft Certified Solution Associate in SQL Database Administration and Development, Azure Developer Associate and Microsoft Certified Trainer.
Also, he is contributing with his SQL tips in many blogs.
Latest posts by Ahmad Yaseen
via SQL Shack – articles about database auditing, server performance, data recovery, and more https://ift.tt/2nZ7eJ9
July 31, 2020 at 03:05PM