Considerations for Deploying Azure SQL Database Managed Instances

Microsoft provides a number of different deployment options for its Azure SQL Database offerings. While most of them share the same characteristics, one in particular warrants special attention from the standpoint of its infrastructure dependencies. That exception is Azure SQL Database Managed Instance, which will be the topic of this article.

The majority of the caveats regarding Azure SQL Database Managed Instance results from its unique architecture. Its primary tenets, which differentiate it for other Azure SQL Database Platform-as-a-Service counterparts, include its networking model and virtual cluster-based implementation, which provides access to a number of SQL Server features, such as SQL Server Agent, along with close to 100% compatibility with full-fledged SQL Server installations.

Regarding networking, Azure SQL Database Managed Instance deploys into a dedicated subnet of a virtual network. On one hand, this facilitates direct connectivity with Azure virtual machines not only on the same or directly connected virtual network, but also from any on-premises environment connected via ExpressRoute private peering or Site-to-Site VPN tunnel. On the other hand, this introduces additional considerations that should be accounted for when preparing for a new deployment. More specifically, management and deployment services for each instance reside outside of the virtual network, which implies that the corresponding traffic must be allowed to flow without being obstructed by restrictions imposed by a customer unaware of this requirement.

The required configuration is implemented automatically based on the network intent policy, which designates the subnet as dedicated for Managed Instance deployment (also referred to as delegated to the Microsoft.Sql/managedInstances resource provider), precluding provisioning any other services within the same subnet. The subnet requires at least 16 available IP addresses, although the recommended minimum is 32. In addition, the platform creates a Network Security Group and a User Defined Route table, associates them with the designated subnet, and adds rules and routes to each of them (respectively).

Another potentially surprising networking caveat is that, while Azure SQL Database Managed Instance is accessible via its private IP address, that endpoint is actually represented by a publicly resolvable DNS name (in the form <managed_instance_name>.<dns_zone>.database.windows.net, where <dns_zone> is generated during the provisioning process).

From the operational standpoint, it is worth noting that the virtual cluster-based implementation of Azure SQL Database Managed Instance, which was mentioned earlier, while beneficial in many aspects, has also some negative consequences. In particular, management operations, such as provisioning, resizing, or deprovisioning, take considerably longer than the equivalent tasks performed on Azure SQL Database. For example, according to the metrics published by Microsoft, in 90% of cases, virtual cluster creation completes in about 4 hours, while resizing (expansion or shrinking) and deletion take 2.5 hours and 1.5 hours, respectively (although it is important to point out that expansion and shrinking are online operations, which do not affect instance availability, except for a short, about 10 second-long downtime).

Last, but not least, it is likely that databases of Azure SQL Database Managed Instance will be migrated from on-premises SQL Server deployments, rather than created anew. If that is the situation you are facing, then you might want to first verify their full compatibility (even though the possibility of issues is relatively small). Somewhat surprisingly, this functionality is not yet incorporated into Data Migration Assistant, so the current recommendation is to use it to perform an assessment against Azure SQL Database and review its results against the documented capabilities of Managed Instance.