Introduction to Azure SQL Database Managed Instances

By: John Martin | Updated: 2018-03-29 | Comments | Related: > Azure

Problem

You have heard about Azure SQL Database Managed Instances, but you are not sure what they are and how you might be able to use them. In this post, I will cover what a Managed Instance is and how it is positioned so that you can look at how you might be able to use it.

Solution

Put very simply, Azure SQL Database Managed Instance are another flavour and deployment option of Azure SQL Database. They are a managed Platform as a Service (PaaS) database offering, but with a far greater level of parity with the retail SQL Server product that we are all familiar with. Managed Instances give you capabilities that previously prevented many database systems being moved to Azure SQL Database, including cross database queries, lack of SQL Server Agent and other items.

Whereas Azure SQL Database is a database-centric platform, Managed Instances shift that scope to the Instance Level. This now gives you the ability to make use of a managed platform with built-in high availability and backup management, allowing for a point in time recovery should you need it. There is also the added benefit that as a PaaS solution, Managed Instances remove the headaches associated with regular patching of the Operating System and SQL Server software that we have had to manage as a DBA presently.

Managed Instance Architecture

Azure SQL Database Managed Instances are built on the Azure platform and are a tightly integrated PaaS offering that competes with the likes of RDS for SQL Server from Amazon. However, there are key differences in the architecture that make Managed Instances a different proposition. Key among these differences is the fact that Managed Instances are built on Azure SQL Database meaning they are running the latest code that Microsoft has deployed, whereas RDS for SQL Server is running retail SQL Server versions on a VM and are abstracted from the user.

The Managed Instance General Purpose service, unlike Azure SQL Database will be defined by vCPU core count. Specifically, there will be eight, sixteen, and twenty-four core compute options available for us to select. Additionally, the amount of memory will vary based on core count with Microsoft advising that there will be 5-7 GB of memory per core. This means that the smallest configuration would be 8 cores with ~40 GB of RAM, all the way up to the largest being 24 cores with ~168 GB of RAM available.

There will be no artificial limitations on the throughput of the storage that is connected to a Managed Instance, it will be limited by the capabilities of the Azure Premium Storage platform on which it is built.

General Purpose Managed Instances

Managed Instances that are deployed as General Purpose and leverage Azure Premium Storage for user databases, this will be abstracted and managed by the platform so there will be no need to figure out where to put database files, etc. The storage architecture will be such that there are 200 disks, each will contain one database file. There is a limit of 100 databases, or 280 files per-instance. It is possible to have 100 databases with two or more files (data & log) totaling up to the file limit, or 1 database with 279 data files and one log file. System databases will be stored on SSD storage that is local to the compute node where the database engine and SQL Server Agent are running.

This storage architecture means that the redundancy of Azure Storage can be used to protect the database and transaction log files that underpin our Data Platform solutions.

The compute element of General Purpose Managed Instances will be handled by a ‘stateless’ compute node. This means that in the event of an issue at the compute layer, the existing compute node will be removed and a new one provisioned. This new compute node will then have the Azure Premium Storage mounted and then the databases will be attached to the new compute node.

Azure SQL Database Managed Instance - General Purpose - Description: High level view of General Purpose Managed Instance architecture with stateless compute and Azure Premium Disk storage.

Because General Purpose Managed Instances make use of Azure Premium Disk storage, it is important to understand the limitations based on the different configurations available. These are documented by Microsoft at High-performance Premium Storage and managed disks for VMs.

The storage in use by the Managed Instance ranges from P10 to the upper limits at P50. This means that the size of the files used in the database will directly impact the performance throughput in IOPS and MB/s that you will have for your databases. Ranging from 500 IOPS and 100 MB/s for files 128 GB and smaller, through to 7,500 IOPS and 250 MB/s for file sizes more than 2TB.

Managed Instance Features

Managed Instances have several key features that do not exist in Azure SQL Database most notably among these are:

Cross Database Queries and Transactions;
CLR;
SQL Server Agent, and Database Mail;
Linked Servers;
Service Broker (within the instance);
Multiple Database file groups and files;
Native Azure vNet deployment; and,
Azure Active Directory Integration;

As well as existing features that were released in current versions such as Query Store, Temporal Tables, Row Level Security, Dynamic Data Masking, Graph Database, etc.

Cross Database Queries

As much as we hate to admit it, we are all guilty of having built solutions that rely on making cross-database queries in our time. While this has been fine for retail SQL Server deployments onto Windows or Linux Servers, this architecture was a blocker when migrating to Azure SQL Database. The only way around it currently is to make use of Elastic Query, which also requires that the schema of the database is altered to make use of external tables.

With Managed Instances this problem goes away, because this is an Instance scope rather than database it means that we can just pick up our database solutions that use cross-database queries and move them to Managed Instance without needing to modify the database.

Linked Servers

Building Data Platform solutions that span multiple database servers is sometimes a necessity. Likewise, the ability to connect to that data via Linked Servers and not performing ETL or building it into the application layer is also needed. This is where Linked Servers excel however this capability does not exist in Azure SQL Database.

Managed Instances provide the ability to create Linked Servers to both retail SQL Server deployments whether they are in Azure VMs or deployed on-premises, and to other Managed Instances. This again removes another common blocker for migration to PaaS Data Platform systems.

Azure vNet deployment

One of the key capabilities that has been asked for with Azure SQL Database is the ability to connect to it via Azure Virtual Networks. This was recently added with Azure Service Endpoints which allow for connectivity to these resources via the Azure vNet. However, the public endpoint for both services still exists and while in both cases they can be locked down, many security teams still use this as a reason to not use Azure SQL Database.

Managed Instances deploy to a subnet on Azure vNets (Configure a VNet for Azure SQL Database Managed Instance) by default and do not have a publicly accessible endpoint. If you are not connected to the vNet then you cannot access the resources, in order to connect from on-premises systems then either Express Route or a VPN can be configured to allow connectivity.

This vNet only configuration allows for greater confidence among security teams and businesses where they have more control over the paths that their network traffic is taking when connecting to Managed Instance PaaS deployments.

Managed Instance Use Cases

Now that we have an idea of what the Azure SQL Database Managed Instance is, what should we look to use it for? It is my view that it is a viable alternative to using deploying SQL Server in Azure VMs if your applications support connectivity via Azure Active Directory or SQL Authentication.

This view does carry the caveat that you need to do workload testing before you just move your solutions to Managed Instance. It is important to understand the profile of your workloads and how they will interact within the boundaries set by the resources available to you with Managed Instance.

Another important element to understand is the support that you will receive from application vendors should you look to move off the shelf applications to this platform. While Managed Instances are built on Azure SQL Database, they do support compatibility modes to help maintain levels of behavior. However, it is always important to work with your vendors to ensure that you are within a supported configuration.

As with any PaaS solution that can scale up easily, it is important to remember that you are better off running at ~80% utilization by default. This gives a bit of room for workload variation while at the same time being highly cost effective with the ability to increase resources if needed.

The workloads that would appear to sit best on General Purpose Managed Instances are ones that are not latency sensitive and require larger data footprints. I would envisage that line of business applications or reporting platforms where the power of Azure SQL Data Warehouse is not needed would be good initial candidates to start evaluating.

But as with anything, your mileage may vary, and you should test it thoroughly.

Next Steps

If after reading this post, you are thinking that you would like to explore Managed Instances more then why not check out these additional tips around database migrations and testing.

About the author

John Martin is a Data Platform Engineer working in the financial sector as well as Director-at-Large for the PASS organisation and currently a Microsoft Data Platform MVP.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2018-03-29