Quick-start script: Setup Azure network environment for Azure SQL Managed Instance

Azure SQL Database Managed Instance is a fully managed SQL Server Database Engine hosted in Azure cloud and placed in your Azure network. In this post will be explained how to create environment where Managed Instances can be placed using the sample PowerShell script.

Azure SQL Managed Instance is your private resources placed in your own Azure VNET on private IP. This is a big advantage of Managed Instance from the security point of view; however, setting the environment required to place Managed Instance might be one of the hardest tasks.

In order to configure your Managed Instance, you would need to

  1. Create Azure VNET where your Managed Instances will be placed
  2. Create a subnet in your VNET that will be dedicated to your Managed Instances
  3. Add user defined-route on your subnet that will enable Managed Instances to communicate to the Azure management service.

You can find more details in the documentation or read this article How to configure network for Azure SQL Managed Instance .

If you not a networking experts and you want to quickly setup default environment you can use the following script. As a prerequisite, you would need to install Azure RM PowerShell . In most of the cases, the following commands might install everything that you need:

Install-Module PowerShellGet -Force
Install-Module -Name AzureRM -AllowClobber

Just change the parameters in the following code (you subscription id, names of your VNET, subnet and address space that you want to allocate to subnets, etc.)

$subscriptionId = "ee5em899-9270-418f-0791-77cd7382a94b"
$resourceGroup = "JovanPopTestResourceGroup"
$location = "West Central US"
$vNetName = "JovanPopVirtualNetwork"
$vNetAddressPrefix = "10.0.0.0/16"
$defaultSubnetName = "default"
$defaultSubnetAddressPrefix = "10.0.0.0/24"
$miSubnetName = "mi"
$miSubnetAddressPrefix = "10.0.1.0/24"

Then execute the script that will create and configure environment where you can place your Azure SQL Managed Instances.

Select-AzureRmSubscription -Subscription $subscriptionId

New-AzureRmResourceGroup -ResourceGroupName $resourceGroup -Location $location

$virtualNetwork = New-AzureRmVirtualNetwork `
                      -ResourceGroupName $resourceGroup `
                      -Location $location `
                      -Name $vNetName `
                      -AddressPrefix $vNetAddressPrefix

$subnetConfig = Add-AzureRmVirtualNetworkSubnetConfig `
                      -Name $defaultSubnetName `
                      -AddressPrefix $defaultSubnetAddressPrefix `
                      -VirtualNetwork $virtualNetwork


$subnetConfigMi = Add-AzureRmVirtualNetworkSubnetConfig `
                      -Name $miSubnetName `
                      -AddressPrefix $miSubnetAddressPrefix `
                      -VirtualNetwork $virtualNetwork

$virtualNetwork | Set-AzureRmVirtualNetwork

$routeTableMiManagementService = New-AzureRmRouteTable `
                      -Name 'myRouteTableMiManagementService' `
                      -ResourceGroupName $resourceGroup `
                      -location $location

Set-AzureRmVirtualNetworkSubnetConfig `
                      -VirtualNetwork $virtualNetwork `
                      -Name $miSubnetName `
                      -AddressPrefix $miSubnetAddressPrefix `
                      -RouteTable $routeTableMiManagementService | `
                    Set-AzureRmVirtualNetwork

Get-AzureRmRouteTable `
                      -ResourceGroupName $resourceGroup `
                      -Name "myRouteTableMiManagementService" `
                      | Add-AzureRmRouteConfig `
                      -Name "ToManagedInstanceManagementService" `
                      -AddressPrefix 0.0.0.0/0 `
                      -NextHopType "Internet" `
                     | Set-AzureRmRouteTable

If you don’t have some policy that prevents you from creating some resources, you will get the configured environment where you can create Azure SQL Managed Instances.

You can use the Azure portal to create your first Azure SQL Managed Instance in the Azure VNet that this script created. Use “mi” subnet for Managed Instances, and you can place other resources (VMs) in “default” subnet.

The script is under MIT licence so feel free to update this script according to your needs.