The ultimate guide for creating and configuring Azure SQL Managed Instance environment

Azure SQL Managed Instance is a fully Managed SQL Server Instance hosted in Azure cloud and placed in your own private Azure network. One of the biggest challenges in the process of creating of Managed Instance is the proper configuration of Azure network where the instance should be created. In this article you can find some guidelines that can help you to more easily create managed instances.

Preparing network environment

Azure SQL Managed Instance is placed in your own Azure VNet, so as a first step you need to prepare VNet and subnet where the instance will be placed.

Full documentation about network requirements could be found here, and in this story you can find some shortcuts and the best practices.

Probably the easiest way to prepare valid network is to go to Azure quick start documentation https://docs.microsoft.com/en-us/azure/sql-database/#5-minute-quickstarts and create a new environment using the Azure Resource Management Deployment template, Azure Powershell, or Azure portal to create your instance. I would recommend using Azure resource template by clicking on the “Deploy to Azure” button on this page and it will open a form where you can specify the parameters of the network elements that will be deployed (make sure that you are signed-in to Azure portal). The form looks like:

Custom deployment form for Managed Instance environment

You can populate the following parameters in the form for the custom deployment:

• Name of the Azure Virtual Network that will be created and configured, including the address range that will be associated to this VNet. Default address range is 10.0.0.0/16 but you should probably change it to fit your needs. The absolute minimum for the subnet where the instance will be placed is 16 IP addresses (/28 subnet) so VNet size must have at least this range of IP addresses. This is recommended only if you are very sensitive on using IP address space and in that case there would be limited possibility to add additional instances in the subnet. That being said we recommend setting up at least /27 or /26 range. Azure documentation provides a way to estimate subnet size.
• Name of the default subnet where you can place the resources other than Managed Instances. The name will be “Default”, if you don’t want to change it. This is the subnet where you will place VMs or web apps that should access Managed Instances in your VNet. You should also enter address range that should be associated to this network. If you don’t need any other resources in your VNet you can delete this subnet later.
• Name of the subnet that will be dedicated to Managed Instances placed in your VNet including the subnet address range (this is Azure SQL Managed Instance specific requirement). The subnet name is verified against the regular expression ^[a-zA-Z_][^\\\/\:\*\?\”\<\>\|\`\’\^]*(?<![\.\s])$. All names that pass the regex and are valid subnet names are currently supported (you cannot have subnet names starting with numbers). Choose carefully the subnet address range because it depends on the number of instances that you would like to place in the subnet. As described above, the absolute minimum for the subnet where the instance will be placed is 16 IP addresses (/28 subnet). You would need at least two addresses per every General Purpose Managed Instance that you want to deploy in the subnet. Note that you cannot resize the subnet if there are Managed Instances inside. This is general Azure networking infrastructure limitation.
• Name of the route table that will enable Managed Instance in the subnet to communicate with the Azure Management service that controls them. If the route table with the specified name doesn’t exist the new one will be created and configured, otherwise the existing one will be used. The recommendation is to create one route table and don’t change it. User-defined route table (UDR) will have 0.0.0.0/0 next hop Internet rule. This rules ensures that Managed Instance can communicate with outside management services. This rule was meant to simply override common BGP advertisements that could cause asymmetric routing and interfere with the management traffic. In the case of BGP advertisement of more specific prefixes, this would not be enough to assure management traffic flow and customer will need to define UDRs to override the advertised prefixes.

Note that 0.0.0.0/0 next hop type Internet rule do not routes all traffic to the Internet. The next hop is always another device — in this case Internet Gateway that is located inside Azure. It is called Internet Gateway as it handles routing to public IP addresses. As these IP addresses mostly belong to Azure services collocated with Managed Instance the network traffic that always finds the shortest path stays inside the Azure.

The role that creates the network and the instance must have at least the following permissions:

• Microsoft.Resources/deployments/*
• Microsoft.Sql/managedInstances/write
• Microsoft.Sql/servers/write -> this is temporary requirement and it will be removed very soon
• Microsoft.Network/networkSecurityGroups/write
• Microsoft.Network/routeTables/write
• Microsoft.Network/virtualNetworks/subnets/write
• */join/action

If you are a subscription admin, you don’t need to worry about this, however, if you are delegating this permission to someone make sure that it has these permissions.

If you want to deploy the instances in the existing subnet, make sure that you don’t have any other resources in the subnet that is dedicated to your managed instances, and go to the step that verify that your network is valid.

Modifying VNet and subnet

You can make some modifications to the network/subnet, as long as it don’t cut-off connection between the instance and the management service. You could transparently monitor network traffic (i.e. using Network Watcher) or affect it using elements of network infrastructure Network security group (NSG) and Route tables (UDR). Managed Instances pickup DNS configuration from virtual network DNS servers list (more in this public document).

As an example, you can modify inbound Network Security Group rules and limit permissions in this group. To be compliant with Managed Instance Network Intent Policy, NSG must have rules that are numbered on picture as 100, 200, and 300 on the top of the list.

Required inbound rules on customized NSG

· Rule allow_management_inbound allows Azure management service to reach the instance.

· Rule allow_misubnet_inbound allows communication between the virtual machines that form Managed Instance cluster.

· Rule allow_health_probe allows health check from the virtual machines host. Without it Azure service fabric infrastructure will think that nodes are unhealthy and block the access.

· Rule allow_tds_inbound is optional but without it yon can’t access the Managed Instance. It is advised to narrow its IP range as much as possible.

Priority numbers need not to be as depicted, but top 3 rules have to be of higher priority than any Deny rules.

You can also change the outbound rules in NSG. Customized NSG must have rules that are numbered on picture as 100 and 200 on the top of the list.

Required outbound rules on the customized NSG

· Rule allow_management_outbound allows management traffic to reach the services managed instance depends upon.

· Rule allow_misubnet_outbound allows communication between the virtual machines that form Managed Instance cluster.

Priority numbers need not to be as depicted, but top 2 rules have to be of higher priority than any Deny rules.

Note that you cannot determine the exact IP address of your Managed Instance so you would need to use range of addresses dedicated to the subnet where the instance is placed.

You could set NSG that controls inbound access to the port 1433. It is advised to narrow its IP range as much as possible.

Managed Instance additional features may require additional ports to be open.

You could also set limited set of other User Defined Routes:

- UDRs with private IP ranges as destination without next hop restrictions. Exception is Managed Instance subnet destination that must have next hop type Virtual network — otherwise the connections between the Managed Instance virtual machines would be broken.

- UDRs with public IP range as destination if the next hop type is Internet

You can also add a custom DNS server; however, note that it has to provide public DNS names resolution. One way you could achieve this is described in documentation.

You cannot add service endpoints because they are causing asymmetric routing and might block communication between Azure Managed Instance and Azure Management Service.

Currently, you cannot also set NVA or firewall for the outbound non-management traffic (this would be changed in the future). Also, you would not be able to use Fully qualified domain names in the NVA/firewall.

Verify that your network is valid

Once you finish with modifications, it would be good to confirm that your modifications would not block managed instance creation due to some The easiest way is to run PowerShell script described in SQL Server samples on GitHub from either Windows or Azure Cloud Shell:

scriptUrlBase = 'https://raw.githubusercontent.com/Microsoft/sql-server-samples/master/samples/manage/azure-sql-db-managed-instance/prepare-subnet'
$parameters = @{
subscriptionId = '<subscriptionId>'
resourceGroupName = '<resourceGroupName>'
virtualNetworkName = '<virtualNetworkName>'
subnetName = '<subnetName>'
}
Invoke-Command -ScriptBlock ([Scriptblock]::Create((iwr ($scriptUrlBase+’/prepareSubnet.ps1?t=’+ [DateTime]::Now.Ticks)).Content)) -ArgumentList $parameters

Script has three simple steps:

- Validate — Selected virtual network and subnet are validated for Managed Instance networking requirements

- Confirm — User is shown a set of changes that need to be made to prepare subnet for Managed Instance deployment and asked for consent

- Prepare — Virtual network and subnet are configured properly

If everything is fine, or if you accept the changes that this script will make, you can create the instances in your subnet.

Once you create your first Managed Instance inside the subnet, it will automatically add so called Network Intent Policy that is preventing you from making modifications that could cut-off the instance from the Management service. Network Intent policy will prevent any update to network infrastructure element (NSG, UDR, Service Endpoints, DNS) that is not compliant with the policy.

Conclusion

Preparing and configuring Azure VNet where the Managed Instance will be placed is one of the most challenging tasks in the process of creation of managed instance. This story explains the most important options that can help you to properly configure your Managed instance environment.