Getting started with Azure SQL Managed Instance

Posted on by — No Comments ↓




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





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:


Leave a Reply

Your email address will not be published. Required fields are marked *

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.