Migrating SQL workloads to Microsoft Azure: Databases trip to Azure SQL Database Managed Instance

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:

Create SQL Managed Instance

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:

Create MI_Basic

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:

cell phone

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:

Additional Settings

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:

Network Components of MI

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:

MI properties

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:

Backup to URL

Browse for the Azure Blob Storage and confirm that the backup file is stored there, as shown below:

Backup in Azure blob storage

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:

Connect to MI

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:

Create Credentials

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:

Test restore

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:

RESTORE DATABASE

To confirm that the database is restored successfully, refresh the databases list from the Object Explorer under the SSMS, as below:

Validate restore from SSMS

Or from the Microsoft Azure Portal, under the Managed Instance databases, as below:

Managed Instance databases

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

Ahmad Yaseen
Latest posts by Ahmad Yaseen (see all)

Author: admin

Leave a Reply

Your email address will not be published.