In the previous two trips of this articles series, databases trip to SQL Server on Azure VM and to Azure SQL Database, we discussed the benefits of migrating your on-premises SQL Server databases to the Microsoft Azure database platforms and the methods that can be followed to migrate the on-premises databases to the Azure database platforms.
In this article, we will go together on a new trip, in which we will show the benefits of migrating the on-premises SQL Server databases to the Microsoft Azure SQL Database Managed Instance and the methods that can be used for migration.
Why Azure SQL Database Managed Instance
With all the benefits that we can gain from migrating the databases to the Azure SQL Database, we still face difficulties when migrating our applications and databases together to PaaS platform services due to the absence of some SQL Server components that are required by the application.
In addition to the backward compatibility of the Azure SQL Database Managed Instance with the SQL Server version 2008 and later, and the network security isolation mechanism of the MI from any other tenant in the Microsoft Azure cloud, it is also useful when migrating your databases and applications to Microsoft Azure, where it provides you with the ability to migrate your SQL Server databases and enjoy the benefits of the Azure Platform as a Service, at the same time, it provides you with the required IaaS platform SQL Server components that interact with the database engine, and not supported in the Azure SQL Database platform, such as the SQL Server Agent Service, SQL Server Integration Services and cross-database joins, allowing you to migrate your SQL Server database to PaaS platform without the need to redesign your application.
In other words, Azure SQL Database Managed Instance provides you with all Azure SQL Database availability, performance tuning and security benefits and the SQL Server components supported by the SQL Server on Azure VM without worrying about administrating or maintaining the hosting infrastructure.
Azure SQL Database Managed Instance provides us also with the Instance Failover Group feature at the instance level, which allows us to create a group of databases under the same instance that will act as one failover unit between the different Microsoft Azure regions.
Planning to Migrate
Before migrating the on-premises SQL Server databases to the Microsoft Azure SQL Database Managed Instance, we should consider performing multiple actions, include:
- The tools that should be used to discover and assess the on-premises environment for any migration blocking issue. For example, the Microsoft Planning and Assessment tool that can be used to check and review the on-premises environment configurations and resources, the Data Migration Assistant tool that can be used to assess the on-premises databases for any breaking, blocking or compatibility issue that could affect the migration process, in addition to providing enhancement recommendation for the target database platform in Microsoft Azure, and the Database Experimentation Assistant tool that is used to check if the current workload can be handled by the target database platform in Microsoft Azure
- Specify the best licensing model for the Azure SQL Database Managed Instance that fits the company requirements, from the licensing models that are supported by the Azure SQL Database MI platform, such as the Enterprise Agreement, Pay-as-you-go, Cloud Service Provider, and Enterprise Dev/Test, Pay-As-You-Go Dev/Test or the monthly Azure credit for Visual Studio subscribers
- Specify the Azure SQL Database Managed Instance service tier, General Purpose and Business Critical, and generation type, Gen4 or Gen5, that meets your requirements
- Confirm that the Compatibility Level of the database to be migrated is 100 and later, to be compatible with the Azure SQL Managed Instance
- Prepare the isolated VNET that will host the Azure SQL Database Managed Instances and whether to connect your on-premises to this Azure SQL Database MI using a VPN tunneling to route gateway, or move the application to Microsoft Azure to overcome any latency issue
- As the Azure SQL Database Managed Instance keeps the backup files for 7-35 days, check if the company requirements to keep it further, up to 10 years, by configuring the Long Term Retention feature
Prepare Azure SQL Database Managed Instance
In order to migrate the on-premises SQL Server database to the Microsoft Azure SQL Database Managed Instance, we should create the target Managed instance in Azure. To achieve that, search for the SQL Managed Instance and click on Create SQL Managed Instance, as shown below:
In the Create Azure SQL Database Managed Instance window, provide:
- The subscription and the resource group under which the Azure SQL MI will be created
- A unique name for that Azure Managed Instance
- The region where this MI will be hosted
- The MI compute and storage pricing tier
- The credentials for the account that will be used to administrate that managed instance
After providing the requested information, click Next to configure the Networking settings, as shown below:
In the Networking settings, you will be asked to:
- Provide the name of a valid Virtual Network and Subnet or create a new VNET to host the Azure SQL Database Managed Instance
- Specify the connection type whether to use a Proxy and a Redirect
- Whether you plan to access the MI from a Public endpoint
- To allow access to the MI from the Azure services, from the Internet or no access allowed to the MI
- The TLS protocol that will be used for the inbound connections
After providing all the required information, click Next to configure the additional settings, as below:
In the additional settings page, you will be asked to provide:
- The default collation that will control the data sort and compare criteria
- The time zone for the MI and all databases hosted in it
- If this MI will act as a secondary replica in the failover group
After providing all required information, click on Review + create to create the Azure SQL Database Managed Instance, as shown below:
Once the Azure SQL Database Managed is created successfully, review the instance configurations and confirm that it meets your requirements before start migrating your databases from the on-premises SQL Server instances.
Make sure also to review the network settings for that MI, such as the user-defined route table and the Network Security Group and ensure that it meets all the network requirements for your users and applications connectivity as confirmed previously with the network administrator, as shown below:
Now, from the Overview tab, search for the Host property and copy the fully qualified host address for the Azure SQL Database Managed Instance and connect to it later, and check if you need to perform any change before the migration process, as below:
Migrate to Microsoft Azure SQL Database Managed Instance
Azure SQL Database Managed Instance is the best lift and shift migration target when migrating your SQL Server databases that are hosted in physical servers or VM machines.
After using the discovery and assessment tools that we discussed previously to confirm that we are able to migrate the SQL Server databases to Azure, we need now to choose a suitable tool for the database migration.
SQL Server provides us with two main methods that can be used to migrate the databases hosted in on-premises SQL Server servers to an Azure SQL Server Database Managed Instance. These methods include:
- Using Azure Database Migration Service, which is a free tool that helps to migrate from multiple database sources, such as SQL Server, MySQL, PostgreSQL, and MariaDB to Microsoft Azure Database platforms with the minimal downtime. Azure Database Migration Service uses first the Data Migration Assistant tool to assess the databases before migrating it to Azure
- Using Backup and Restore operations from Azure Blob Storage URL, where a backup will be taken from the database to be migrated, store the (.bak) file in an Azure Blob Storage then restore the database into the Azure SQL Database Managed instance from that Azure Blob Storage. You can use that method if you plan to have downtime during your database migration process
In this demo, we will use the offline Backup and Restore operations to migrate a SQL Server database hosted in an on-premises SQL Server instance into the Azure SQL Database Managed Instance.
The first step is taking backup from the on-premises SQL Server database, using SSMS, and choose to Backup to URL, specifying the URL of the Azure Storage container where the backup file will be stored, and the Shared Access Signature to authenticate writing to that storage account, as shown below:
Browse for the Azure Blob Storage and confirm that the backup file is stored there, as shown below:
These two methods that can be used to connect to the “Isolated” Azure SQL Database Managed Instance. The first method by configuring a point-to-site VPN connection from on-premises to Microsoft Azure MI, and the second method is installing a new Virtual Machine in the same VNET of the Azure SQL Database MI under another subnet, like the one, we will use in this migration demo.
From the VM machine that is created in another subnet within the same VNET as the Azure SQL Database MI, provide the MI name and the credentials for the administrator that you defined when creating the MI, as below:
In order to restore the database backup file that is stored in the Azure Blob Storage to the Microsoft Azure MI, we need to create a credential for the Azure Blob Storage account using the Blob Container URL and the previously generated Shared Access Signature to authenticate the MI to access that blob container, as shown below:
To confirm that the MI is authenticated to access the Azure Blob container, we will run the restore test command below that returns the list of the backup files, providing the Azure Blob URL, as shown below:
Now, we will restore the backup file taken from the on-premises SQL Server database to the Azure SQL Database MI, using the RESTORE DATABASE command below:
To confirm that the database is restored successfully, refresh the databases list from the Object Explorer under the SSMS, as below:
Or from the Microsoft Azure Portal, under the Managed Instance databases, as below:
Conclusion
In this article, we discussed the benefits that you can gain from migrating your databases from the on-premises SQL Server instances to the Azure SQL Database Managed Instance and the methods that can be used to achieve that.
This is the last, but not least article in the Migrate the SQL Workload to Microsoft Azure series. Hope you enjoyed it!
Table of contents
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.
View all posts by Ahmad Yaseen
- Azure Data Factory Interview Questions and Answers - February 11, 2021
- How to monitor Azure Data Factory - January 15, 2021
- Using Source Control in Azure Data Factory - January 12, 2021