First published on MSDN on Jul 12, 2018
Azure SQL Managed Instance is a fully managed PaaS SQL Server Database Engine hosted in Azure cloud that is placed in Azure Virtual network, and you need to ensure that your network is properly configured. In this post your will see a PowerShell script that can validate the Azure network that you prepared for the Managed Instance.
In the previous posts we saw how to
create a new Azure Network environment
where Managed instance will be deployed via
PowerShell
or
ARM
. Often customers want to deploy Managed Instance to an existing Virtual Network (Vnet) & Subnet in their subscription. However the deployment shall fail if the subnet does not meet requirements outlined
here
.
In this post we share a PowerShell script authored by Rohit Nayak that checks
a specific subnet
within your Vnet against the pre-requisites for hosting Managed Instance. Please ensure you have
installed Azure RM PowerShell
.
IMPORTANT NOTE: There is another official Managed Instance network validation script published on
Azure SQL Managed Instance documentation page
. Although this script should work, I would recommend to use the officially documented script. Try this script only if the official one has some issue.
The following parameters are required for the script below to run:
Subscription ID where you placed the Azure VNet where you want to deploy Managed Instances
Resource group name – This is the resource group that contains the Virtual network – and where your Managed Instance will be eventually deployed
Virtual Network (Vnet) Name – the name of the Azure VNet where your want to deploy your SQL Managed Instance
Subnet Name – This is the subnet within your Virtual network which you plan to use for Managed Instance
Validation can be done using a simple PowerShell function call where you provide parameters described above:
##Inputs that end user needs to fill in manually
$azSubscriptionName = “Replace with your subscription id”
$azResourceGroupName = “Replace with your Resource group name”
$azVnetName = “Replace with your Virtual network name”
$azSubnetName = “Replace with your Subnet name”
Validate-Subnet-For-MI -inSubscriptionName $azSubscriptionName -inResourceGroupName $azResourceGroupName -inVnetName $azVnetName -inSubnetName $azSubnetName
The output will be something like below, listing out all the checks that were done against the subnet and which ones passed (in green) vs failed( in red)
Here is the PowerShell script that performs these validation checks:
function Validate-Subnet-For-MI {
[CmdletBinding()]
param (
[Parameter(Mandatory = $true)]
[String]
$inSubscriptionName,
[Parameter(Mandatory = $true)]
[String]
$inResourceGroupName,
[Parameter(Mandatory = $true)]
[String]
$inVnetName,
[Parameter(Mandatory = $true)]
[String]
$inSubnetName
)
begin {
#Login to Azure
Login-AzureRMAccount | Out-null
Select-AzureRmSubscription -SubscriptionName $inSubscriptionName | Out-null
$vnet = Get-AzureRmVirtualNetwork -ResourceGroupName $inResourceGroupName -Name $inVnetName
#Start with getting the vnet details
Write-Host(“——————— Vnet Details ————————————- “) -ForegroundColor Yellow
Write-Host (“Vnet ID = {0}” -f $vnet.ID.ToString()) -ForegroundColor Yellow
Write-Host (“Vnet Address Space = {0}” -f $vnet.AddressSpaceText.ToString()) -ForegroundColor Yellow
Write-Host(“———————————————————————— “) -ForegroundColor Yellow
}
process {
$subnets = $vnet.Subnets.Name
if ( $true -eq $subnets.contains($azSubnetName)) {
Write-Host(“— Begin validation of subnet {0} in {1} —” -f $inSubnetName, $vnet.Name)
#Get the subnet object
$idx = $subnets.IndexOf($azSubnetName)
$sn = $vnet.Subnets[$idx]
$bIsSubnetValidForMI = $true
Write-Host(“”)
Write-Host(“———- Subnet Name {0} [ Subnet Address Range {1} ] ————— ” -f $sn.Name, $sn.AddressPrefix) -ForegroundColor Yellow
Write-Host(“”)
# Check that subnet is not a Gateway subnet
if ($azSubnetName -eq “GatewaySubnet”) {
Write-Host (“Warning – Subnet {0} in virtual network {1} is possibly Gateway Subnet. Please check subnet configuration in Portal before continuing with deployment” -f $sn.Name, $vnet.Name ) -ForegroundColor Yellow
}
# Check that subnet does not have a Network Security Group associated with it.
$sn.NetworkSecurityGroup| Format-List | Out-Host
if ($true -eq [System.String]::IsNullOrEmpty($sn.NetworkSecurityGroup)) {
Write-Host(“Passed Validation – NSG is empty subnet {0} in virtual network {1}” -f $sn.Name, $vnet.Name) -ForegroundColor Green
Write-Host(“”)
}
else {
Write-Host (“Failed Validation – Found NSG associated with subnet {0} in virtual network {1}” -f $sn.Name, $vnet.Name ) -ForegroundColor Red
$bIsSubnetValidForMI = $false
}
## Route table specific checks
# Check that subnet is associated with a route table
$rt = $sn.RouteTable
if ($false -eq [System.String]::IsNullOrEmpty($rt)) {
$rtSegments = ($rt.Id).Split(“{/}”, [System.StringSplitOptions]::RemoveEmptyEntries)
$rtName = $rtSegments[-1].Trim()
$rtResourceGroup = $rtSegments[3].Trim()
Write-Host(“Passed Validation – Route table {0} is associated with subnet {1} in virtual network {2}” -f $rtName, $sn.Name, $vnet.Name) -ForegroundColor Green
Write-Host(“”)
Write-Host(“———- Route Table {0} Details ————— ” -f $rtName) -ForegroundColor Yellow
Write-Host(“”)
$tblRoutes = Get-AzureRmRouteTable -ResourceGroupName $rtResourceGroup -Name $rtName
$tblRoutes.Routes| Select-Object -Property Name, AddressPrefix, NextHopType | Format-Table| Out-Host
Write-Host(“”)
# Check if route table has BGP route propagation set to Enabled
if ($false -eq $tblRoutes.DisableBgpRoutePropagation) {
Write-Host(“Passed Validation – Route table {0} has BGP Propagation = Enabled” -f $tblRoutes.Name) -ForegroundColor Green
Write-Host(“”)
}
else {
Write-Host(“Warning – Route table {0} has BGP Propagation = Disabled. This could affect connectivity to resources behind VPN Gateway” -f $tblRoutes.Name) -ForegroundColor Yellow
}
#Check if route table contains more than one rule
if ($tblRoutes.Routes.count -eq 1) {
Write-Host(“Passed Validation – Route table {0} has only one rule” -f $tblRoutes.Name) -ForegroundColor Green
Write-Host(“”)
}
else {
Write-Host(“Failed Validation – Route table {0} has more than one rule ” -f $tblRoutes.Name) -ForegroundColor Red
$bIsSubnetValidForMI = $false
}
# Check route table has a route that is 0.0.0.0/0 Next Hop Internet
$nextHopRule = $tblRoutes.Routes| Where-Object { $_.AddressPrefix -eq “0.0.0.0/0” -and $_.NextHopType -eq “Internet” }| Select-Object -Property Name, AddressPrefix, NextHopType
if ($false -eq [System.String]::IsNullOrEmpty($nextHopRule)) {
Write-Host(“Passed Validation – Route table {0} has 0.0.0.0/0 rule” -f $tblRoutes.Name) -ForegroundColor Green
Write-Host(“”)
}
else {
Write-Host(“Failed Validation – Route table {0} is missing 0.0.0.0/0 rule ” -f $tblRoutes.Name) -ForegroundColor Red
$bIsSubnetValidForMI = $false
}
}
else {
Write-Host (“Failed Validation -No Route table is associated with subnet {0} in virtual network {1}” -f $sn.Name, $vnet.Name ) -ForegroundColor Red
$bIsSubnetValidForMI = $false
}
# Check that subnet does not have a Service endpoint (Storage or Sql) associated to it
$sn.ServiceEndpoints| Format-List | Out-Host
if ($true -eq [System.String]::IsNullOrEmpty($sn.ServiceEndpoints)) {
Write-Host(“Passed Validation – Service endpoints are empty for subnet {0} in virtual network {1}” -f $sn.Name, $vnet.Name ) -ForegroundColor Green
Write-Host(“”)
}
else {
Write-Host (“Failed Validation – Found Service Endpoint associated with subnet {0} in virtual network {1}” -f $sn.Name, $vnet.Name ) -ForegroundColor Red
$bIsSubnetValidForMI = $false
}
# Optional custom DNS: If custom DNS is specified on the VNET, Azure’s recursive resolvers IP address (such as 168.63.129.16) must be added to the list
If ($vnet.DhcpOptions.DnsServers.Count -eq 0 ) {
Write-Host(“Passed Validation – No custom DNS is defined for virtual network {0}” -f $vnet.Name ) -ForegroundColor Green
Write-Host(“”)
}
else {
Write-Host(“Custom DNS is defined for virtual network {0}” -f $vnet.Name ) -ForegroundColor Yellow
#Write-Host(“Please add Azure’s recursive resolvers IP address (such as 168.63.129.16) to the list of DNS Servers for virtual network {0}” -f $vnet.Name ) -ForegroundColor Yellow
$isAzureRRAdded = $false
Write-Host(“———- DnsServers ———-“) -ForegroundColor Yellow
foreach ($entry in $vnet.DhcpOptions.DnsServers) {
Write-Host($entry.ToString())
if ($entry.ToString() -eq “168.63.129.16”) {
$isAzureRRAdded = $true
}
}
if ($isAzureRRAdded -eq $true) {
Write-Host(“Passed Validation – Azure’s Recursive resolver IP address 168.63.129.16 is added to DNS Servers for virtual network {0}” -f $vnet.Name ) -ForegroundColor Green
Write-Host(“”)
}
else {
Write-Host(“Failed Validation – Azure’s Recursive resolver IP address 168.63.129.16 is not added to DNS Servers for virtual network {0}” -f $vnet.Name ) -ForegroundColor Red
$bIsSubnetValidForMI = $false
}
}
if ($bIsSubnetValidForMI -eq $true) {
Write-Host(“———- Subnet Name {0} in Vnet {1} passed all pre-requisite checks for SQL Managed Instance ————— ” -f $sn.Name, $vnet.Name) -ForegroundColor Green
}
else {
Write-Host(“———- Subnet Name {0} in Vnet {1} did not pass all pre-requisite checks for SQL Managed Instance ——— ” -f $sn.Name, $vnet.Name) -ForegroundColor Red
}
Write-Host(“— End validation of subnet {0} in {1} —” -f $inSubnetName, $vnet.Name)
}
else {
Write-Host(“— Subnet{0} does not exist in vnet {1} —” -f $inSubnetName, $vnet.Name) -ForegroundColor Red
}
}
end {
}
}