First published on MSDN on Jan 23, 2019
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
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
. 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
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 with ARM template
Just make sure that you have a
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:
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.
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.
Finally, you can start migrating your databases from SQL Server to Managed Instance. There are several ways to move your database:
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:
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
Adaptive Index Defrag
scripts can help you keep your indexes up-to-date. Currently Managed Instance don’t automatically rebuilds your indexes.
storage performance best practices and considerations for General Purpose service tier
recommended by Dimitri Furman.
that can help you configure your Managed Instance.
PowerShell and Azure CLI modules
that can help you configure your Managed Instance.
is a powerful PowerShell library that help you control SQL Server and many script can be used on Managed Instance.