Share via

Azure Databases: Introduction To SQL Managed Instances

  • Article

 

↑ Back to top

Introduction

This post is an intro of the Azure SQL database Managed Instance. A fully managed SQL Server instance hosted in Azure Cloud, communicates with on-premise LAN or Azure VNet and provides most SQL Server features to applications.

 

Note

Managed Instances are not available for every region, check the supported regions from this link.

 

↑ Back to top

Supported Subscription Types

Until the time this article was posted, there is a limitation for the subscription types that supports SQL Managed Instance deployments, and these types are:

 

↑ Back to top

Benefits

SQL Database Managed Instance has Built-In High Availability 99,99% uptime SLA, and a lot of other benefits:

  • Security : SQL Audit, Row Level Security, Always Encrypted, etc.
  • Programmability : Cross-database queries, Linked Server, CLR modules, etc. 
  • Operational : SQL Agent, DMVs, XEvents, DB Mail, Automatic Patching, etc.

 

↑ Back to top

Features

There are many features available that previously someone could have only found on an on-premise SQL Server installation. Imagine a SQL Server instance fully isolated and protected in Azure cloud inside your corporate network. It sounds awesome and it truly is.

  • Always Encrypted, (Read more here)
  • Always On Availability Groups, (More about Always On AG, in this link)
  • SQL Server Agent (Read about this feature in this link)
  • SQL Server Data Tools (SSDT)
  • SQL Server Integration Services (SSIS)
  • SQL Server Powershell (Read more here)
  • Windows Server Fail-over Clustering (Read More in Microsoft docs)

 

↑ Back to top

Connectivity

Azure SQL Managed Instance provides a highly secured and isolated environment.

There are three ways a user can access Azure Managed Instance and all three are quite secured :

  • Deploying a VM inside the same VNet and connect via SSMS.
  • From a remote point, if you deploy a P2S (Point-To-Site) or S2S (Site-To-Site) VPN.
  • Connect a Web Application to a Managed Instance using Azure App VNet . 

Application Deployment Topologies

At the following image we are able to see the different use-cases for applications deployment using SQL Managed Instance.

As the previous image shows, applications can integrate with Managed Instances in the following ways:

  1. Apps hosted in a dedicated subnet inside the SQL MI Vnet (App Service apps and IaaS hosted apps).
  2. Using Vnet peering or VPN channels to integrate from a different Vnet (App Service apps and IaaS hosted apps).
  3. Using the Vnet integration feature (App Service apps only).
  4. On-premises apps integrate with SQL MI using Express Route / VPN Gateway

 

↑ Back to top

Hardware Generation Characteristics

There are two SQL Database Managed Instance hardware generations (Gen4, Gen5), at the table below we can see the differences between them.

Gen 4 Gen 5
Hardware Intel E5-2673 v3 (Haswell) 2.4-GHz processors,

attached SSD vCore = 1 PP (physical core)

 Intel E5-2673 v4 (Broadwell) 2.3 GHz processors,

fast eNVM SSD, vCore = 1 LP (hyper-thread)
Compute 8,16,24 vCores 8,16,24,32,40,64,80 vCores
Memory 7 GB per vCore 5.1 GB per vCore
Max Storage (Business Critical) 1 TB 1 TB, 2 TB, or 4 TB depending on the number of cores

 

↑ Back to top

Service Tier Characteristics

There are two service tiers in SQL Managed Instance, General Purpose, and Business Critical :

General Purpose : This service tier is for apps with typical requirements for performance.

Business Critical : On the other hand Business Critical, is for apps that require high performance and low IO latency.

At the table below we can see what are the capabilities for every tier. 

Feature General Purpose Business Critical
Number of vCores* Gen4: 8,16,24

Gen5: 8,16,24,32,40,64,80

 Gen4: 8,16,24,32

Gen5: 8,16,24,32,40,64,80
Memory Gen4: 56GB - 156GB

Gen5: 44GB - 440GB

*Proportional to the number of vCores

 Gen4: 56GB - 156GB

Gen5: 41GB - 408GB

*Proportional to the number of vCores
Max Storage Size 8 TB Gen4: 1 TB

Gen5: 1 TB for 8,16 vCores

          2 TB for 24 vCores

          4 TB for 32, 40, 64, 80 vCores
Max Storage Per Database Determined by the max storage size per instance Determined by the max storage size per instance
Max Number Of Databases

Per Instance

 100 100
Max Database Files Per Instance Up to 280 32.767 files per database
Data/Log IOPS (approximate) 500-7500 per file *Depends on the file size 11K - 110K (1375 per vCore)
Instance Log throughput  22MB/s per instance 3MB/s per vCore, Max 48MB/s
Data throughput (approximate) 100-250 MB/s per file *Depends on the file size 24-48 MB/s per vCore
IO latency (approximate) 5-10 ms 1-2 ms
Max TempDB size 192-1920 GB (24 GB per vCore) No constraints - limited by the max instance storage size

 

↑ Back to top

Managed Instance Pricing

At the following images, we can see the prices for the General purpose (Gen 4, Gen 5) tiers.

Note

To view the image in full size we need to click on the image

General Purpose

In case we have a moderate workload we can see the prices for the General purpose tier.

Gen 4 Tier

Gen 5 Tier

Storage and I/Os

Business critical

If the application needs fast IO and high availability requirements then we should check the following prices which are for the Business critical tier.

Gen 4

Gen 5

Storage and I/Os

 

↑ Back to top

Migrate To Azure SQL Managed Instance

At this point of this post, we talk about the migration process of an existing on-premises SQL Server instance to Azure SQL Managed Instance, and the ability to take advantage of all the possibilities it provides.

There are two ways to migrate an instance to Azure, offline and online :

  • Offline: The application that uses the SQL instance will have downtime when the migration begins.
  • Online: In this way, the downtime for the application will be limited for a short time at the end of the migration.

Except for the instructions above (Offline, Online), we can also migrate using Azure PowerShell.

Azure Database Migration Pricing

The pricing tiers from Azure Database Migration are two, the Standard and the Premium. Per Subscription, someone can create two Data Migration Services. Also, the Standard Tier of the Data Migration Service is FREE to use for offline migrations. At the following link, we can see the pricing details, Azure Database Migration Service pricing.

 

↑ Back to top

Create A SQL Managed Instance

Azure SQL Managed Instance is already (General Available) and the deployment is automated from Azure.

Search For The Service

The first thing we have do is to search for the "Azure SQL Managed Instance" service, select and click Create.

Create SQL MI

At the first deployment blade, we must fill up the required fields.

Setting Value
Subscription  Select  a valid Azure Subscription
Managed instance name Type the SQL Managed Instance Name
Managed Instance admin login Type the administration login name
Password / Confirm password Type and re-type the administrator's password
Collation Type the collation for the Instance, the default is SQL_Lating1_General_CP1_CI_AS
Location Select the location where the managed instance will be created.
Virtual network Select Create a new virtual network or an existing one
Resource Group Select Create new, or an existing Resource Group

If this is checked, then we must select an existing Managed Instance in the other DNS Zone to create a Fail-over Group.

The last we have to configure is the Pricing tier that we will use for the SQL Managed Instance. We must be very careful with this selection because it must fit to the application needs.

Select Create and wait until the SQL Managed Instance deployment complete.

Note

The deployment takes more than 10 hours to complete.

Connect To Azure SQL Managed Instance

Unlike the SQL Managed Instance service in Preview, now it's easier to connect to the Managed Instance.

There are two options in the Azure Portal to do this.

From the Settings - Quickstart, we can select between Virtual machine with SSMS and Point-to-Site VPN connection.

A virtual machine with SSMS

Follow the next steps to create a Virtual Machine attached to the VNet with the latest SSMS version.

Step 1. Run the following PowerShell code :

$scriptUrlBase = 'https://raw.githubusercontent.com/Microsoft/sql-server-samples/master/samples/manage/azure-sql-db-managed-instance/attach-jumpbox'
 
$parameters = @{
    subscriptionId = '########-####-####-####-############'
    resourceGroupName = 'SQLMI_RG'
    virtualNetworkName = 'SQLMI_VNET'
    administratorLogin  = 'azureuser'
    administratorLoginPassword  = '<password>'
}
 
Invoke-Command -ScriptBlock ([Scriptblock]::Create((iwr ($scriptUrlBase+'/attachJumpbox.ps1?t='+ [DateTime]::Now.Ticks)).Content)) -ArgumentList $parameters, $scriptUrlBase

Step 2. Use RDP to connect to the VM

Step 3. From the VM, open SSMS and use the parameters from the Azure Portal, as the image below shows.

Point-to-Site VPN connection

In case we want to connect to the Managed Instance from a local network we must follow these steps :

Step 1. Runt the PowerShell code shown below

$scriptUrlBase = 'https://raw.githubusercontent.com/Microsoft/sql-server-samples/master/samples/manage/azure-sql-db-managed-instance/attach-vpn-gateway'
 
$parameters = @{
    subscriptionId = '########-####-####-####-############'
    resourceGroupName = 'SQLMI_RG'
    virtualNetworkName = 'SQLMI_VNET'
    certificateNamePrefix  = 'cert4cloudopszone'
}
 
Invoke-Command -ScriptBlock ([Scriptblock]::Create((iwr ($scriptUrlBase+'/attachVPNGateway.ps1?t='+ [DateTime]::Now.Ticks)).Content)) -ArgumentList $parameters, $scriptUrlBase

Note

The above code also automatically creates a certificate, e.g cert4cloudopszone.

Step 2. Download and Install the VPN client on the computer that we want to connect using SSMS

Step 3. After we connect to the Vnet using the previously created VPN Gateway, open the SSMS by using the credentials from the Azure Portal as the next image.

 

↑ Back to top

Restore Database To SQL Azure Managed Instance

In this point, we are ready to restore database(s) for the application(s). We can do this in two ways, via T-SQL and PowerShell. Down, in the references section, we can find detailed instructions about the database restore procedure.

 

↑ Back to top

Conclusion

In this post, we made a thorough report about the Azure SQL Managed Instance service. We mentioned the key characteristics and benefits of this service and finally, we made a quick demo of the service implementation.

 

↑ Back to top

Reference

Azure Deployment Topologies

Service Tier Characteristics

Migrate To Azure SQL Managed Instance

Azure SQL Managed Instance Pricing

Create A SQL Managed Instance

Restore Database To SQL Azure Managed Instance

 

↑ Back to top

See Also

SQL Feature Comparison

Database Features

Azure SQL Database: Monitoring and tuning

Azure SQL Database: Scalability

 

↑ Back to top