BlueXP Blog

Azure SQL Managed Instance Explained: Key Features & Challenges

Written by Yifat Perry, Technical Content Manager | Jul 15, 2020 9:53:28 AM

Azure SQL Managed Instance is a fully-managed deployment option designed to provide near-perfect compatibility for SQL migrations. You can use Azure SQL Managed Instance to lift and shift on-prem SQL apps, and then leverage unique Azure SQL features, such as linked servers, service brokers, and database mail. However, before using managed instances, you need to take into account service pricing, connectivity, authentication, and provisioning.

In this post, we’ll explain what is an Azure SQL Managed Instance, what features are offered, how pricing works, and what to consider before using this service. This is part of our series of articles on Azure Databases. We’ll also show how NetApp Cloud Volumes ONTAP can help address Azure SQL Managed Instance challenges before, during and after the migration phase.

In this article, you will learn:

Azure Options for SQL

Before focusing on Azure SQL Managed Instances, it helps to understand the options provided by Azure SQL Database. Within this service, you can deploy databases as:

  • Single databases—each instance has its own resources and functions like a single database on a SQL Server. This deployment option is designed for development of and use with cloud-native It is also available in serverless and Hyperscale variations.
  • Elastic pools—a collection of single databases using shared resources. This deployment option is designed for development of cloud-native applications with a multi-tenant pattern. You can also use it to optimize resource use for databases with variable use requirements.
  • Managed instances—a cluster of system and user databases that use shared resources. This deployment option is designed to mimic the on-prem SQL Server functionality.

What Is Azure SQL Managed Instance?

Azure SQL Managed Instance is designed to provide near-perfect compatibility with the latest SQL Server (Enterprise Edition) Database Engine used for on-premises databases. These instances enable you to deploy a native virtual network (VNet) that mimics on-premises deployments and increases security.

Key Features of SQL Managed Instances 

Image Source: Azure

Using managed instances, you can lift-and-shift on-premises SQL Servers and applications to Azure with minimal changes. This enables you to access cloud scalability and availability while eliminating the responsibilities of on-prem infrastructure maintenance, updating, and backups.

When migrating to Azure SQL Database Managed Instance, you can use the Azure Database Migration Service (DMS).

Key Features of Azure SQL Database Managed Instance

Managed instances for SQL Database provide several features that make this deployment option stand out from single or elastic pool deployments. These features include:

  • Linked servers that enable you to operate a distributed database
  • Service brokers that provide native support for messaging and queuing
  • Database mail that enables you to send emails from your database

Linked Servers

Linked servers enable you to read data from remote sources and execute commands for those sources from your database instance. For example, you can use this feature to execute T-SQL statements that include tables from an outside SQL Server or database.

This feature enables you to configure many API data sources as linked servers, including Azure CosmosDB, Microsoft Access, and Excel. Additionally, you can use linked servers to implement sharding without direct loading or the use of custom application code.

Service Broker

Service Broker is a feature that provides native support for asynchronous messaging and queuing. You can use it to build distributed applications and enable communication between separate databases.

When you use Service Broker, you can easily distribute workloads across databases. For example, separating quick vs process-intensive tasks on front-end vs back-end servers. This feature manages the communication paths for you, enabling you to focus on development and testing without sacrificing data consistency.

Database Mail

Database Mail is a feature that enables you to send e-mail messages from your Azure SQL managed instance. With it, you can send query results, notify users of completed processes, and attach files from any other in-network resource.

Azure SQL Managed Instance Pricing

When using Azure SQL Database Managed Instance, you can choose from two service tiers:

  • General purpose—designed for non-mission critical applications with standard IO latency and performance requirements. With this tier, you gain access to 8TB of high-performance Azure Blob storage and built-in availability ensured with Azure Service Fabric.
  • Business critical—designed for mission-critical applications that require low latency and that have low tolerance for performance impacts created by maintenance. With this tier, you gain access to a read-only replica, up to 4TB of local SSD storage, high-availability via Always On Availability groups and Azure Service Fabric, and In-Memory OLTP.

The pricing for managed instances depends on the tier you choose in combination with the number of CPU cores you need.

Below is a table summarizing these prices. Cloud pricing is subject to frequent change, for up-to-date prices and additional pricing options see the official pricing page.

 

General purpose single instance

Business Critical

Single instance

License-included pricing

From $0.5044 / hour for 2 vCores with 10GB RAM

to $20.1747 / hour for 80 vCores with 396GB RAM

$1.3589 / hour for 2 vCores with 10GB RAM

to $54.3548 / hour for 80 vCores with 396 GB RAM

Azure Hybrid Benefit savings*

40% discount on regular License-included pricing

55% discount on regular License-included pricing

Storage

First 32GB/mth free then $0.115/GB up to 8TB

First 32GB/mth free then $0.25/GB up to 4TB

Backup storage

$0.20 per GB/mth past your storage size

* Azure Hybrid Benefit is a service that enables you to bring on-premises licenses with you when you migrate.

Azure SQL Managed Instance Considerations

When adopting Azure SQL Managed Instance there are a few considerations to keep in mind. These issues stem from the unique architecture of Azure SQL Database.

Virtual networks
Managed instances are deployed to a dedicated subnet in a virtual network. This enables you to directly connect to Azure VMs on the same network, any directly connected virtual network, or on-premises via ExpressRoute or Site-to-Site VPN tunnel. However, it also requires you to specially configure management and deployment services for each instance and to prevent traffic flow restrictions from outside the network.

These configurations are based on your network intent policy and prevent you from provisioning any other services within the same subnet. Additionally, this subnet requires at least 16 IP addresses although 32 is preferable.

Windows authentication vs SQL authentication vs Azure AD
Another issue is that Windows authentication is not supported. With Azure SQL Managed Instance, you are only able to use SQL authentication or Azure Active Directory authentication. This isn’t a big deal as long as you can change methods, but this change often isn’t possible with third-party applications. Additionally, SQL authentication isn’t as secure as Windows authentication nor is it as easy to manage.

Provisioning time
Provisioning time is another issue you are likely to encounter. Often, this process takes four hours or more to complete. This can be problematic if you need to create a new instance on short order. Additionally, keep in mind that scaling can take up to two and a half hours and deletion up to one and a half hours. However, your database does remain available during scaling, so the wait is just for the additional space, not downtime for your existing data.

Azure SQL Managed Instance with Cloud Volumes ONTAP

NetApp Cloud Volumes ONTAP, the leading enterprise-grade storage management solution, delivers secure, proven storage management services on AWS, Azure and Google Cloud. Cloud Volumes ONTAP supports up to a capacity of 368TB, and supports various use cases such as file services, databases, DevOps or any other enterprise workload, with a strong set of features including high availability, data protection, storage efficiencies, Kubernetes integration, and more.

In particular, Cloud Volumes ONTAP helps in addressing database workloads challenges in the cloud, and filling the gap between your cloud-based database capabilities and the public cloud resources it runs on.