Share via

Getting started with Azure SQL Managed Instance

  • Article

Azure SQL Managed Instance is fully managed PaaS version of SQL Server hosted in Azure cloud and placed in you own VNet with private IP address. In this post, I will shortly explain how to configure and create Managed Instance including network environment, migrate your databases and manage databases after migration.

I will explain the following topics in this article:

  • Configure network environment where Managed Instance will be created
  • Create Managed Instance
  • Assess your databases to check could they be migrated
  • Migrate your databases
  • Manage your databases after migration

Configuring network environment

Managed Instance is placed in Azure VNet so you 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, it might be good to create it as a first step because you can configure the parameters of VNet.

The easiest way to create and configure the network environment is to use Azure Resource deployment template that will create and configure you network and subnet where the instance will be placed. You just need to press the Azure Resource Manager deploy button and populate the form with parameters. As an alternative, you can use PowerShell script described here.

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. You should use this PowerShell script to verify that your subnet is properly configured. This script will not just validate your network and report the issues - it will tell you what should be changed and also offer you to make the necessary changes in your VNet/subnet. Run this script if you don't want to configure your VNet/subnet manually, and also you should run it after any major reconfiguration of your network infrastructure. If you want to create and configure your own network read Managed Instance documentation and this guide.

Creating managed instance

Once you have prepared the network environment, you can create your first Managed Instance. The easiest way to create it is to use the Azure portal and configure all necessary properties. If you have not created the network environment as described in the previous step, the Azure portal can do it for you - the only drawback is the fact that it will configure it with some default parameters that you cannot change later. As an alternative you can use PowerShell, PowerShell with ARM template, or Azure CLI.

Just make sure that you have a subscription type that is allowed to create the instances.

Connecting to Managed Instance

When you create your Managed Instance you would need to find a way how to connect to it. Remember that Managed Instance is your private service placed on a private IP inside your VNet, so you cannot just connect via some public IP (this might be changed in the future). There are several ways how you can setup connection to Managed Instance:

  • Create Azure Virtual Machine 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. 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.

Assessing your databases

Now when you have prepared Managed Instance you can start migrating your databases from SQL Server to cloud.

First thing that you need to do is to ensure that there are no critical differences between your SQL Server and Managed Instance. You can find a high-level list of supported features in Managed Instance here, and you can find details and known issues here.

Instead of reading documentation and searching for incompatibilities, it  would be easier to install Data Migration Assistant (DMA). This tool will analyze your database on SQL Server and find any issue that could block migration to Managed Instance such as existence of FileStream, multiple log files, etc. If you could resolve these issues, your databases are ready to go to Managed Instance.

Other way might be to script your empty database using SSMS or SSDT and try to create all objects on Managed Instance, and check would there be any errors, but DMA is much easier to use.

Database Experimentation Assistant is another useful tool that can record your workload on SQL Server and replay it on Managed Instance so you can determine are there going to be any performance issues if you migrate to Managed Instance. Technical characteristics of Managed Instance are documented here, but DEA will enable you to more easily check does you instance fit your performance needs.

Migrating databases

Finally, you can start migrating your databases from SQL Server to Managed Instance. 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 downtime because your database cannot be used until you restore it on Managed Instance.
  • Data Migration service is a service that can migrate your database with minimal downtime.
  • Exporting and importing your database as .bacpac file, or using bcp too - but there is no big advantage of these methods compared to RESTORE/DMS, except if .bacpac is integrated in your DevOps pipeline.

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

Managing databases after migrations

Many management functions such as backups are handled by Managed Instance and don't require your involvement. However, there are some best practices, tools, and scripts that you might add to your Managed Instance:

  • sp_blitz procedure from Brent Ozar First Responder Kit can help you identify issues that you have in your database. One example is the fact that Managed Instance don't run DBCC CHECKDB on your database because this is resource consuming operation. Instead, Managed Instance check every backup and file an warning to Azure SQL team if any corruption is detected. However, it might be good if you could run DBCC CHECKDB periodically.
  • Maintenance script developed by Ola Hallengren and Microsoft Adaptive Index Defrag scripts can help you keep your indexes up-to-date. Currently Managed Instance don't automatically rebuilds your indexes.
  • Apply storage performance best practices and considerations for General Purpose service tier recommended by Dimitri Furman.
  • Find more How-to guides that can help you configure your Managed Instance.
  • Install Microsoft PowerShell and Azure CLI modules that can help you configure your Managed Instance.
  • DBA tools is a powerful PowerShell library that help you control SQL Server and many script can be used on Managed Instance.