Welcome to my blog, in this post I want to discuss Azure SQL Managed Instance. Azure MI is the 2nd SQL PaaS offering from Microsoft, questions around this regularly appear on the forums, so this post will seek to clarify what the feature is and what it can do.

What is Azure SQL MI?

Azure SQL Managed Instance is a fully provisioned and managed database engine including a SQL Server agent. The platform, including its base OS, is fully managed and no access is possible to this layer, you only manage and provision objects within the instance itself.
For General Purpose instances, you are able to stop and start the instance which can help to save on costs when the instance may be idle for periods of time.
Azure MI is provisioned based on service tiers and hardware generations, for the full list check the Microsoft website for detailed information, but the details are:

Backups and patching are fully managed and these are configured on the Azure SQL MI blade in the portal. For patching and upgrades, specify the maintenance window.

For the backups, specify the recovery interval time and also the retention periods for short and long term retention. 35 days is the maximum for point in time recovery, generally you shouldn't require anymore than that. It's important to note that you do not get access to the storage account that contains the managed backups. This should be immediately obvious as to why, how can Microsoft maintain an SLA and backup resilience if users were simply able to access the storage account and potentially remove files? 

As SQL MI is a managed platform there are some points to be aware of, these are:

• No access to the base operating system for CLR modules.
• SQL Server version uplifts automatically.
• Reliance on virtual network IP configuration, requires at least 32 IPs and must be dedicated to the MI.
• Max number of database and files per instance based on tier type.
• Instance storage sizes must be specified in multiples of 32GB.
• Lead time required to deploy SQL MI.
• One transaction log file per database.
• Limited number of agent job step types.
• Limited support for SQL Agent notifications.

Lets take a look at these in more detail to get an idea of how they affect the MI deployment\management.

CLR Integration

There are no physical CLR modules that can be deployed since you have no access to the OS drive. CLR assemblies are supported, but must be created from the assembly bits using the CREATE ASSEMBLY command passing in the assembly bits instead of a file path\name, an example is shown below.  

CREATE ASSEMBLY HelloWorld FROM 0x4D5A900000000000 WITH PERMISSION_SET = SAFE;

SQL Server Uplift

When setting up the maintenance window on the Azure SQL MI blade, you are specifying a window within which patches can be applied, but also if the next SQL Server version becomes available, it will be upgraded. You do not have the option to remain at a particular SQL Server version, you would need to deploy an IaaS SQL Server instance. Having said that, Microsoft still want you to uplift versions to remain in support, legacy platforms can be maintained but you are agreeing to provide a "best efforts" approach to uplift to supported product versions. Azure SQL MI provides this automatically for you.

Virtual Network Reliance

Azure SQL Managed Instances are placed into dedicated subnets within a virtual network. An Azure SQL MI is placed onto a set of VMs within a virtual cluster, the nodes each need an IP within the dedicated subnet for the SQL MI. The subnet has a minimum requirement for the number of IP addresses available per instance. A subnet for an SQL MI must have at least 32 IP addresses per instance. The azure networking will consume 5 IP addresses so you should bear this in mind when defining the subnet size..

No other resources apart from Azure SQL MIs may be placed into the subnet. You should be especially careful when applying resource locks on subnets, SQL Managed Instances or resource groups they may be a member of, as this can affect functionality.

Max Number of Databases and Files

Within Azure SQL Managed Instance lies a maximum limit on the number of databases, the number of files and the assigned backend storage size for General Purpose instances.

When you create or a restore a database in Azure SQL Managed Instance, each database file is placed on its own disk, the unused space is not charged but does contribute to a maximum backend limit. The disk sizes can be one of the following based on the file size: 

• 128 GB.
• 256 GB.
• 512 GB.
• 1 TB.
• 4 TB.

You have a maximum backend storage size of 35 TB, because of this limitation, exceeding the maximum storage space with small database files may restrict the number of databases you can host on the instance to less than the supported maximum. Also, because of this limitation, if you do exceed the backend limit, any operation to create or restore a database or alter the file size will fail. A typical example for how the limit is exceeded would be as follows: 

A General Purpose deployment of SQL Managed Instance might have one large file that's 1.2 TB in size placed on a 4-TB disk. It may then have 248 x 1GB files which would all be placed on different disks too.
In this example, the total allocated disk storage size is 1 x 4 TB + 248 x 128 GB = 35 TB.
The total reserved space for databases on the instance is only 1 x 1.2 TB + 248 x 1 GB = 1.4 TB.
This example shows how file creation and distribution can easily exceed hard limits, the total number of files may be across only 25 databases for example. After all, its not uncommon to have a database with multiple data files, but it can impede on the instances maximum 35-TB limit.
In this example, existing databases continue to work and can grow without any problem as long as new files aren't added and also as long as a file doesn't exceed its current size allocation.
New databases can't be created or restored because there isn't enough space for new disk drives, even if the total size of all databases doesn't reach the instance size limit.
The error message you receive when you have hit the limit and try to add a new file\database is completely unhelpful and it's not immediately clear what the issue is. If you are aware of these limitations you can easily get to the root of the problem fairly quickly.

There are system catalogs that can be used to check the file specs. If you do reach the hard limit, the options are to switch to the Business Critical tier, or shrink database files that may have a higher excess of space than desired. The entire limitation detail is shown in the table below.

From the table above, another point to understand is that if a database file on a GP instance requires more performance, the best option is to increase the file (and subsequently disk) size so that the IOPs provision is uplifted. Again though, be aware that this contributes to the backend hard limit.

Deployment Lead time

When deploying Azure SQL Managed Instance there is an associated lead time, this is mainly due to the infrastructure requirements behind the scenes. For certain types of configuration though, Microsoft now offer Fast Provisioning, this reduces the deployment time to 30 minutes or under for these assets.
Microsoft do plan to increase this functionality to cover more configurations but at the time of writing, the deployment time for high level configurations can still take up to 4 hours. The table below shows the deployment steps and the anticipated run times.

1. Fast provisioning is currently supported only for the first instance in the subnet, with 4 or 8 vCores, and with default maintenance window configuration.
2 A separate virtual machine group is created for each hardware generation and maintenance window configuration.

SQL Agent

SQL agent is available in Azure SQL MI, but it does have certain limitations due to the managed platform element. You can still use agent jobs to perform all the usual tasks you would with traditional instances of SQL Server, such as maintenance jobs, data movement and data collection. The following list from the Microsoft knowledge base lists items for which features are not available or partially available in Azure SQL MI SQL server agent.

• Enabling and disabling SQL Server Agent is currently not supported in SQL Managed Instance. SQL Agent is always running.
• Job schedule trigger based on an idle CPU isn't supported.
• SQL Server Agent settings are read only. The procedure "sp_set_agent_properties" isn't supported in SQL Managed Instance.
• Jobs
  • T-SQL job steps are supported.
  • The following replication jobs are supported:
    • Transaction-log reader
    • Snapshot
    • Distributor
  • SSIS job steps are supported.
  • Other types of job steps aren't currently supported:
    • The merge replication job step isn't supported.
    • Queue Reader isn't supported.
    • Command shell isn't yet supported.
  • SQL Managed Instance can't access external resources, for example, network shares via robocopy.
  • SQL Server Analysis Services isn't supported.
• Notifications are partially supported.
• Email notification is supported, although it requires that you configure a Database Mail profile. SQL Server Agent can use only one Database Mail profile, and it must be called "AzureManagedInstance_dbmail_profile".
  • Pager isn't supported.
  • NetSend isn't supported.
  • Alerts aren't yet supported.
  • Proxies aren't supported.
• EventLog isn't supported.
• User must be directly mapped to the Microsoft Entra server login to create, modify, or execute SQL Agent jobs. Users that aren't directly mapped, for example, users that belong to a Microsoft Entra group that has the rights to create, modify or execute SQL Agent jobs, will not effectively be able to perform those actions. This is due to SQL Managed Instance impersonation and EXECUTE AS limitations.
• The Multi Server Administration feature for master/target (MSX/TSX) jobs aren't supported.

Some of the options are host dependant in a traditional instance of SQL Server, such as NetSend and EventLog, that is why these are not available. In general, all of the features that would be used to run day to day tasks are portable over to Azure SQL MI. 

Summary

The detail above should provide a quick reference to Azure SQL Managed Instance and help to avoid some of the pitfalls of initial deployment and management. It cannot be stressed enough how important it is to fully understand the needs for your applications database tier and whether or not this fits in with a PaaS solution or an IaaS solution. Due diligence and proof of concept work can avoid wasted time on deployments and embarrassed faces down the line.
Microsoft do offer tools to help decide whether to go the PaaS or IaaS routes but even then, careful planning and decision making lay with the migration team.
Often, organisations adopt a cloud-first migration strategy but it should be realised that not everything fits in with this methodolgy, there will usually always be those systems that are bespoke or adhoc enough to require a different approach.

With that said, IaaS migrations allow rapid datacenter exit routes which provides the opportunity to repurpose assets once deployed to the cloud environment.