This blog post is one that I have had percolating in the background since around November 2018.

My good mate John Martin ( t ) was speaking at PASS Summit on Azure SQL Managed Instances and we had talked about use cases, some of the gotchas and things to consider when migrating your databases to it.

I have been discussing Managed Instances (MI) with more people recently and this blog post is basically a run down of what MI is and how you can migrate to it with some considerations.

The Past

Before we go into MI we should look at what the current offerings were in Azure before MI became available in 2018.

We already had Azure SQL Database (introduced in 2010) and also the ability to run up SQL Server on an Azure VM. Both of these offerings were attractive as:

Azure SQL Database:

Pros:

Server Administration is handled by Microsoft

You could scale out as required

Backups were fully managed

(BTW you can configure a long-term backup retention policy

to automatically retain backups in Azure blob storage for up to 10 years.)

Cons:

We don’t have SQL Server Agent

It’s running 24/7

Cross-database queries are not native

No control of files and filegroups

Can’t use native backups for restore

No Service Broker

Backups are fully managed (for control freaks like myself this can be an annoyance)

Note: Azure SQL Database comes in Singleton databases or Elastic Pools

SQL Server running an Azure VM:

Pros:

Good old familiar SQL Server

SQL Server Agent jobs!!

We can power down the VM if we do not require SQL Server 24/7

Cons:

We still have to administrate a server

The costs associated with hosting VMs in Azure

The management overhead of hosting VMs in Azure

Introducing Managed Instances:

Pros:

HA is built in

Infrastructure is handled by Azure

Can backup/restore to Azure Blob Storage

Lift & shift migrations

SQL Server Agent

Cross Database Queries

Linked Server (I know, I know….)

But like everything there are some cons.

Cons:

Commissioning the Managed Instance can take a fair while.

We can’t shut it down so cost can be prohibitive if you have scaled it wrong.

Azure SQL Database Managed Instance does not currently support long-term backup retention

Here is a good comparison of Managed Instance vs Azure SQL Database

Reference: “Azure Managed Instance your bridge to the cloud”, Joey D’Antoni, SQLSaturday Cambridge 2018

Requirements for Azure Managed Instances

Configuring network environment:

You need to configure the network environment where Managed instance will be created. You will need to create an Azure VNet and a subnet where the instance will be placed.

Although the VNet/subnet can be automatically configured when the instance is created, the only drawback is the fact that it will configure it with some default parameters that you cannot change later.

If you already have a VNet and subnet where you would like to deploy your Managed Instance, you would need to make sure that your VNet and subnet satisfy networking requirements.

Creating Managed Instance:

Once we have the network environment configured the Managed instaqnce can be created. The easiest method is to use the Azure portal, however you can use PowerShell, PowerShell with ARM template, or Azure CLI.

My recommendation is to script it out as that adheres to my philosophy of using Infrastructure as Code to do pretty much anything where possible.

Be careful though…

Storage is vital to the performance of your database:

You need to size your underlying disk with throughput in mind:

So if we want at least 5,000 IOPS per disk then we need to size at P30 or combine smaller disks to achieve the necessary IOPS.

Provisioned capacity and performance

When you provision a premium storage disk, unlike standard storage, you are guaranteed the capacity, IOPS, and throughput of that disk. For example, if you create a P50 disk, Azure provisions 4,095-GB storage capacity, 7,500 IOPS, and 250-MB/s throughput for that disk. Your application can use all or part of the capacity and performance.

Disk size

Azure maps the disk size (rounded up) to the nearest premium storage disk option, as specified in the table above. For example, a disk size of 100 GB is classified as a P10 option. It can perform up to 500 IOPS, with up to 100-MB/s throughput. Similarly, a disk of size 400 GB is classified as a P20. It can perform up to 2,300 IOPS, with 150-MB/s throughput.

Connecting to Managed Instance:

Essentially – Managed Instance is a private service placed on a private IP inside your VNet, so you cannot connect via public IPs.

You can connect to your Managed Instance in a variety of ways:

• Create an Azure VM with installed SSMS and other apps that can be used to access your Managed Instance in a subnet within the same VNet where your Managed Instance is placed. The VM cannot be in the same subnet with your Managed Instances.
• Setup Point-to-site connection on your computer that will enable you to “join” your computer to the VNet where Managed Instance is placed and use Managed Instance as any other SQL Server in your network.
• Connect your local network using express route or site-to-site connection.

Validating your database before migration:

It is vital that you check that there are no differences between your SQL Server and Managed Instance.  You need to understand what features you are using and whether you need to update your existing instance in order to migrate.

A good method is to install the Data Migration Assistant which will analyse the database on your SQL Server and alert you to any issue that  could block the migration.

You also need to consider your authentication methods for your users and applications:

Authentication considerations for your applications when migrating to Azure SQL Managed Instances

Migrating databases:

There are several ways to move your database:

• Native restore functionality that enables you to create a backup of your database, upload it to an Azure blob storage and RESTORE database from the blob storage. This is probably the faster approach for migration, but requires some downtime because your database cannot be used until you restore it on Managed Instance. You can even roll your own log shipping to it to minimise the amount of downtime.
• Data Migration Service is a service that can migrate your database with minimal downtime. It does require vNet connectivity for source, VPN or Express Route to Azure.
• Transactional Replication – this also minimises the amount of down-time and you can use a push subscriber model for Managed Instance

You can migrate up to 100 database on a single Managed Instance.

T-SQL Considerations:

There are some differences in T-SQL syntax and behaviour between Managed Instance and on-premises SQL Server.

It is highly recommended that you read this:

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-managed-instance-transact-sql-information

So there you have it – Azure SQL Managed Instances are live – some might say that they are the future of SQL databases in Azure.

I personally think that like anything it has it’s place. For new cloud based apps that require a SQL database in Azure I’d probably still use Azure SQL Database but of course there is that good old saying:

“It depends”

Yip.