As I’ve been recently working with a project where we were looking into where to place our databases in Azure and what kind of services or IaaS based delivery, we should choose for hosting databases for some certain line of business applications.
Within Azure we have three different managed Microsoft SQL offerings, but which one we can use is dependent also on the support from the 3. party vendors building the software.
So, what is a SQL Managed Instance in Azure?
Azure SQL and Azure SQL Managed Instance both run on the latest stable version of the SQL Server database engine and Windows operating system. Of course, compared to traditional SQL Server, Microsoft manages the service and maintains availability of the service.
When you are setting up a SQL Managed Instance in Azure you are setting up a Service Fabric Virtual Cluster (which is why it takes so long…). The Service Fabric Cluster will then be responsible for the management of the managed instance. The sqlserver.exe process will then be started on a single VM (known as the primary replica) all storage attached to the VM is mounted using https against blob storage. The configuration to the Primary replica is coming from the service fabric cluster. The Active Replica will be pushed jobs from the service fabric cluster including updates and new editions of the sqlserver.exe binary as well.
With each managed instance cluster, it will have its own local cluster address which will in turn communicate with the global cluster address within the region. (example below within the Norwegian datacenter)
So the Service Fabric is used to run many of the management capabilities such as handling incoming traffic to the cluster and even handling the SQL Threat Protection capability.
With Managed Instance you also have the option to scale the service up and down. With each vCore you get 5.1 GB of memory (per vCore) on the Generation 5 hardware with support for up to 8 TB of storage. So, with 4 vCores you have 20,4 GB of memory. So, the number of cores is linked to the amount of memory that you get.
So, in terms of sizing, how is this different from traditional SQL Server?
Well…it’s not. Managed Instance is running the same code base as SQL Server (2019) managed by Service Fabric, and since SQL server is running on a single VM and it is handling the I/O in the same way as regular SQL Server. Yes, it is running SQL server as a stateless service, but still the same under the hood.
One difference is that with Managed instance you have a storage account that is mounted to the VM that is running the sqlservr.exe process instead of having a managed disk.
This means that when you are sizing a SQL managed instance you will still need to have the same memory/IOPS sizing in mind when setting it up.
Whenever the database engine or the operating system is upgraded, or a failure is detected, Azure Service Fabric will move the stateless sqlservr.exe process to another stateless compute node with sufficient free capacity. All databases within the managed instance will share the resources allocated to the instance.
Initiating scale up or scale down action in any of the flavors would restart database engine process and move it to a different virtual machine if needed. Moving database engine process to a new virtual machine is online process where you can continue using your existing Azure SQL Database service while the process is in progress.
Now depending on what kind of tier of SQL Server for managed instance you are using you will get either active/passive (as shown in the picture above) for standard based cluster or always ON availability groups based active/active for Premium and Business Critical service tier.
If you plan to use Managed instance you should understand what kind of limitations that you have compared to regular SQL Server.
- No support for proximity groups – which are useful to provide lowest latency between application and database service.
- No support for ultra-high or high-end disk.
- Managed Instance is linked to specific hardware no option to define.
- No support for Active Directory based authentication (only SQL or Azure AD based)
- No support for Managed Instance within Data Factory data source (yet!)
- Some size limits (General Purpose: 8 TB, Business Critical 1 TB, 2 TB, or 4 TB depending on the number of cores)
- Sizing of storage is linked to the amount of vCPU for business critical.
- Locked to a specific hardware type.
Now looking at the differences between a regular SQL Server and Managed Instance, a regular VM can still provide lower latency compared to Managed instance with support for proximity groups and new capabilities with new hardware and even faster storage options (at least at the moment)