skreebydba

NOTE: This post contains steps for creating Azure resources.  You must have access to an Azure subscription to complete these steps.  IMPORTANT: According to this post from Microsoft, Managed Instance creation is currently supported for Enterprise Agreement, Pay-As-You-Go, and Cloud Service Provider subscriptions.  I will update this post when additional subscription types are added.

Before creating an Azure SQL Managed Instance, a number of prerequisite resources must be provisioned.  These are:

• An Azure Virtual Network
• A dedicated subnet for Managed Instances
• A route table

The Azure Virtual Network provides isolation of the Managed Instance from other tenants in the Azure cloud.  This VNet allows connectivity to your on-premises environment through an Azure Express Route or site-to-site VPN gateway.  Finally, the SQL endpoint for the Managed Instance is only exposed through a private IP, allowing safe connections from a private Azure or hybrid network.

A dedicated subnet is required within the VNet to contain your Managed Instances.  You will not be able to create a Managed Instance in a subnet that contains other resources.  Once you create a Managed Instance in a subnet, you will not be able to create other resources in it.  For ease of administration, it is best practice to create all of your Managed Instances in the same subnet, so it should be sized according to your requirements.

The Managed Instance subnet requires a route table assigned to it.  The route table must contain an address prefix of 0.0.0.0/0 with Next Hop Internet.

The steps to create these resources can be found here.  Alternatively, the following PowerShell script contains the cmdlets to create these resources.  It creates a second subnet in the VNet and an Azure virtual machine in that subnet.  This allows connection to the Managed Instance in the event you do not have access to a site-to-site VPN gateway or Azure Express Route.  You can also download the code here.

<#Generates a dialog box to log into your Azure subscription#>
Connect-AzureRmAccount;

<#Local variables for required Managed Instance resources#>
$rgname = "yourmirg";
$vnet = "yourmivn";
$subnet = "default";
$vmsubnet = "yournvstsn";
$rtable = "yournvstrt";
$server = "yournvstsrv";
$location = "northcentralus";

<#If your resource group already exists, uncomment this line#>
#Remove-AzureRmResourceGroup -Name $rgname -Force;

<#If you need to create a resource group, uncomment this line#>
New-AzureRmResourceGroup -Name $rgname -Location $location;

<#Creates VNet with an IP range of 10.0.0.0-10.0.255.255#>
$virtualNetwork = New-AzureRmVirtualNetwork `
-ResourceGroupName $rgname `
-Location $location `
-Name $vnet `
-AddressPrefix 10.0.0.0/16;

<#Creates a subnet with an IP range of 10.0.0.0-10.0.0.255
Used for Managed Instance#>
$subnetConfig = Add-AzureRmVirtualNetworkSubnetConfig `
-Name $subnet `
-AddressPrefix 10.0.0.0/24 `
-VirtualNetwork $virtualNetwork;

<#Creates a subnet with an IP range of 10.0.1.0-10.0.1.255
Used for VM#>
$subnetVmConfig = Add-AzureRmVirtualNetworkSubnetConfig `
-Name $vmsubnet `
-AddressPrefix 10.0.1.0/24 `
-VirtualNetwork $virtualNetwork;

<#Writes VNet information to the $VirtualNetwork variable#>
$virtualNetwork | Set-AzureRmVirtualNetwork;

<#The next two commands create a route table and write the route table information to variable $RouteTable#>
$Route = New-AzureRmRouteConfig -Name $rtable -AddressPrefix 0.0.0.0/0 -NextHopType Internet;
New-AzureRmRouteTable -Name $rtable -ResourceGroupName $rgname -Location $location -Route $Route;

$RouteTable = Get-AzureRmRouteTable -Name $rtable -ResourceGroupName $rgname;

<#Configures the route table in the drfault subnet#>
Set-AzureRmVirtualNetworkSubnetConfig `
-VirtualNetwork $virtualNetwork `
-Name $subnet `
-AddressPrefix 10.0.0.0/24 `
-RouteTable $RouteTable | Set-AzureRmVirtualNetwork;

$vnetlong = Get-AzureRmVirtualNetwork -Name $vnet -ResourceGroupName $rgname;

$subnetConfig = Get-AzureRmVirtualNetworkSubnetConfig -VirtualNetwork $vnetlong;

$subnetConfig[0].Id;

<#Creates an Azure VM in the yournvstsn subnet, opening ports 80 and 3389#>

New-AzureRmVm `
-ResourceGroupName $rgname `
-Name $server `
-Location $location `
-VirtualNetworkName $vnet `
-SubnetName $vmsubnet `
-SecurityGroupName &quot;$server-nsg&quot; `
-PublicIpAddressName &quot;$server-ip&quot; `
-OpenPorts 80,3389;

<#Once the VM is provisioned, RDP to it and download and install SQL Server Management Studio
https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-2017#>