Getting started with Azure SQL Managed Instance

  • Article

Applies to: Azure SQL Managed Instance

Azure SQL Managed Instance creates a database with near 100% compatibility with the latest SQL Server (Enterprise Edition) database engine, providing a native virtual network (VNet) implementation that addresses common security concerns, and a business model favorable for existing SQL Server customers.

In this article, you will find references to content that teach you how to quickly configure and create a SQL Managed Instance and migrate your databases.

Quickstart overview

The following quickstarts enable you to quickly create a SQL Managed Instance, configure a virtual machine or point to site VPN connection for client application, and restore a database to your new SQL Managed Instance using a .bak file.

Configure environment

As a first step, you would need to create your first SQL Managed Instance with the network environment where it will be placed, and enable connection from the computer or virtual machine where you are executing queries to SQL Managed Instance. You can use the following guides:

  • Create a SQL Managed Instance using the Azure portal. In the Azure portal, you configure the necessary parameters (username/password, number of cores, and max storage amount), and automatically create the Azure network environment without the need to know about networking details and infrastructure requirements. If you have your own network that you want to use or you want to customize the network, see configure an existing virtual network for Azure SQL Managed Instance or create a virtual network for Azure SQL Managed Instance.

  • A SQL Managed Instance is created in its own VNet with no public endpoint. For client application access, you can either create a VM in the same VNet (different subnet) or create a point-to-site VPN connection to the VNet from your client computer using one of these quickstarts:

    Note

    • You can also use express route or site-to-site connection from your local network, but these approaches are out of the scope of these quickstarts.
    • If you change retention period from 0 (unlimited retention) to any other value, please note that retention will only apply to logs written after retention value was changed (logs written during the period when retention was set to unlimited are preserved, even after retention is enabled).

As an alternative to manual creation of SQL Managed Instance, you can use PowerShell, PowerShell with Resource Manager template, or Azure CLI to script and automate this process.

Migrate your databases

After you create a SQL Managed Instance and configure access, you can start migrating your SQL Server databases. Migration can fail if you have some unsupported features in the source database that you want to migrate. To avoid failures and check compatibility, you can use Data Migration Assistant (DMA) to analyze your databases on SQL Server and find any issue that could block migration to a SQL Managed Instance, such as existence of FileStream or multiple log files. If you resolve these issues, your databases are ready to migrate to SQL Managed Instance. Database Experimentation Assistant is another useful tool that can record your workload on SQL Server and replay it on a SQL Managed Instance so you can determine are there going to be any performance issues if you migrate to a SQL Managed Instance.

Once you are sure that you can migrate your database to a SQL Managed Instance, you can use the native SQL Server restore capabilities to restore a database into a SQL Managed Instance from a .bak file. You can use this method to migrate databases from SQL Server database engine installed on-premises or Azure Virtual Machines. For a quickstart, see Restore from backup to a SQL Managed Instance. In this quickstart, you restore from a .bak file stored in Azure Blob storage using the RESTORE Transact-SQL command.

Tip

To use the BACKUP Transact-SQL command to create a backup of your database in Azure Blob storage, see SQL Server backup to URL.

These quickstarts enable you to quickly create, configure, and restore database backup to a SQL Managed Instance. In some scenarios, you would need to customize or automate deployment of SQL Managed Instance and the required networking environment. These scenarios will be described below.

Customize network environment

Although the VNet/subnet can be automatically configured when the instance is created using the Azure portal, it might be good to create it before you start creating instances in SQL Managed Instance because you can configure the parameters of VNet and subnet. The easiest way to create and configure the network environment is to use the Azure Resource deployment template that creates and configures your 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 also use this PowerShell script to automate creation of the network.

If you already have a VNet and subnet where you would like to deploy your SQL Managed Instance, you need to make sure that your VNet and subnet satisfy the networking requirements. Use this PowerShell script to verify that your subnet is properly configured. This script validates your network and reports any issues, telling you what should be changed and then offers to make the necessary changes in your VNet/subnet. Run this script if you don't want to configure your VNet/subnet manually. You can also run it after any major reconfiguration of your network infrastructure. If you want to create and configure your own network, read connectivity architecture and this ultimate guide for creating and configuring a SQL Managed Instance environment.

Migrate to a SQL Managed Instance

The previously-mentioned quickstarts enable you to quickly set up a SQL Managed Instance and move your databases using the native RESTORE capability. This is a good starting point if you want to complete quick proof-of concepts and verify that your solution can work on Managed Instance.

However, in order to migrate your production database or even dev/test databases that you want to use for some performance test, you would need to consider using some additional techniques, such as:

  • Performance testing - You should measure baseline performance metrics on your source SQL Server instance and compare them with the performance metrics on the destination SQL Managed Instance where you have migrated the database. Learn more about the best practices for performance comparison.
  • Online migration - With the native RESTORE described in this article, you have to wait for the databases to be restored (and copied to Azure Blob storage if not already stored there). This causes some downtime of your application especially for larger databases. To move your production database, use the Data Migration service (DMS) to migrate your database with the minimal downtime. DMS accomplishes this by incrementally pushing the changes made in your source database to the SQL Managed Instance database being restored. This way, you can quickly switch your application from source to target database with minimal downtime.

Next steps