This post has been republished via RSS; it originally appeared at: Azure Database Support Blog articles.
Making a High Availability strategy is predominantly essential for organizational business requirements. This article will help you setting up the Cross-subscription Azure SQL Managed Instance Failover Group and gives you the ability to keep your Prod and DR workloads in separate billing boundaries.
Note:
- This article expects you already have an Existing Managed Instance and a VNET Gateway in the Managed instance VNET. Reference: Tutorial – Create & manage a VPN gateway – Azure portal - Azure VPN Gateway | Microsoft Docs
- This article will help you to build an entire new Managed instance in a separate Azure subscription by setting up all network requirements including VNET, Subnets VNET Gateway. The new managed instance will work as the Secondary in the FOG. A Cross subscription VNET Gateway Peering will be done in order to establish the traffics between Primary and secondary Managed Instances.
- Make sure the Primary VNET address space and Secondary VNET address spaces should not overlap . Overlapping will cause failure in setting up the FOG.
- As The Script requires switching between multiple subscriptions and Creating MI and Gateway are long running operation, it will be recommended to execute the script in your Local PowerShell. (Az modules needed to be installed) Reference: Install the Azure Az PowerShell module | Microsoft Docs
- Make sure the Account Executing the script should have privileges to both the subscription.
- Both the managed Instances in the FOG and VNET Gateways needed to be in the Same configurations
- Admin Password for the Secondary Managed instance will be autogenerated, you can reset the password after the MI is created.
Step 1
Execute the script below to connect to Azure, make sure the account used should have privilege in both the subscriptions.
Connect-AzAccount
Step 2
Copy and execute the below Code snippet in the PowerShell Session. This will create a function with the name CrosssubscriptionMI-Failovergroup.
Function CrosssubscriptionMI-Failovergroup
{
param
(
[parameter(Mandatory=$true)][string]$PrimaryMiSubID,
[parameter(Mandatory=$true)][string]$secondaryMISubID,
[parameter(Mandatory=$true)][string]$primaryMIRsourceGroupName,
[parameter(Mandatory=$true)][string]$primaryManagedInstanceName,
[parameter(Mandatory=$true)][string]$primaryMILocation,
[parameter(Mandatory=$true)][string]$primaryMiVnet,
[parameter(Mandatory=$true)][string]$primaryGWName,
[parameter(Mandatory=$true)][string]$secondaryResourcegroupname,
[parameter(Mandatory=$true)][string]$secondaryManagedInstanceName,
[parameter(Mandatory=$true)][string]$SecondaryMIlocation,
[parameter(Mandatory=$true)][string]$edition,
[parameter(Mandatory=$true)][int32]$vCores,
[parameter(Mandatory=$true)][int32]$maxStorage,
[parameter(Mandatory=$true)][string]$computeGeneration,
[parameter(Mandatory=$true)][string]$adminaccountname,
[parameter(Mandatory=$true)][string]$secondaryVNet,
[parameter(Mandatory=$true)][string]$secondaryAddressPrefix,
[parameter(Mandatory=$true)][string]$secondaryMiSubnetName,
[parameter(Mandatory=$true)][string]$secondaryMiSubnetAddres,
[parameter(Mandatory=$true)][string]$secondaryGWName,
[parameter(Mandatory=$true)][string]$secondaryMiGwSubnetAddress,
[parameter(Mandatory=$true)][string]$failoverGroupName
)
$secondaryGWPublicIPAddress = $secondaryGWName + "-IP"
$secondaryGWIPConfig = $secondaryGWName + "-ipc"
$secondaryGWAsn = 62000
$secondaryGWConnection = $secondaryGWName + "-connection"
$secpasswd = "PWD27!"+(New-Guid).Guid | ConvertTo-SecureString -AsPlainText -Force #secondaryMIPassword
$mycreds = New-Object System.Management.Automation.PSCredential ($adminaccountname, $secpasswd) ##secondaryMIUsername
################################################################################################
#computation 1
#Connect to primary to get the MI ID
Set-AzContext -Subscription $PrimaryMiSubID
$primaryManagedInstanceId = Get-AzSqlInstance -ResourceGroupName $primaryMIRsourceGroupName -Name $primaryManagedInstanceName|select -ExpandProperty ID
$PrimaryVnet = Get-AzVirtualNetwork -Name $primaryMiVnet -ResourceGroupName $primaryMIRsourceGroupName
$PrimaryGW = Get-AzVirtualNetworkGateway -Name $primaryGWName -ResourceGroupName $primaryMIRsourceGroupName
#####################################################################################
#computation 2
#Setting up Secondaty Netwrk Requirements
Set-AzContext -Subscription $secondaryMISubID
$SecondaryVirtualNetwork = New-AzVirtualNetwork -ResourceGroupName $secondaryResourcegroupname -Location $SecondaryMIlocation -Name $secondaryVNet -AddressPrefix $secondaryAddressPrefix
Add-AzVirtualNetworkSubnetConfig -Name $secondaryMiSubnetName -VirtualNetwork $SecondaryVirtualNetwork -AddressPrefix $secondaryMiSubnetAddress | Set-AzVirtualNetwork
# Configure the secondary managed instance subnet
Write-host "Configuring secondary MI subnet..."
$SecondaryVirtualNetwork = Get-AzVirtualNetwork -Name $secondaryVNet -ResourceGroupName $secondaryResourcegroupname
$secondaryMiSubnetConfig = Get-AzVirtualNetworkSubnetConfig -Name $secondaryMiSubnetName -VirtualNetwork $SecondaryVirtualNetwork
# Configure the secondary network security group management service
Write-host "Configuring secondary network security group management service..."
$secondaryMiSubnetConfigId = $secondaryMiSubnetConfig.Id
$secondaryNSGMiManagementService = New-AzNetworkSecurityGroup -Name 'secondaryToMIManagementService' -ResourceGroupName $secondaryResourcegroupname -location $SecondaryMIlocation
# Configure the secondary route table MI management service
Write-host "Configuring secondary route table MI management service..."
$secondaryRouteTableMiManagementService = New-AzRouteTable -Name 'secondaryRouteTableMiManagementService' -ResourceGroupName $secondaryResourcegroupname -location $SecondaryMIlocation
# Configure the secondary network security group
Write-host "Configuring secondary network security group..."
Set-AzVirtualNetworkSubnetConfig -VirtualNetwork $SecondaryVirtualNetwork -Name $secondaryMiSubnetName -AddressPrefix $secondaryMiSubnetAddress -NetworkSecurityGroup $secondaryNSGMiManagementService -RouteTable $secondaryRouteTableMiManagementService|Set-AzVirtualNetwork
Get-AzNetworkSecurityGroup -ResourceGroupName $secondaryResourcegroupname -Name "secondaryToMIManagementService" | Add-AzNetworkSecurityRuleConfig -Priority 100 -Name "allow_management_inbound" -Access Allow -Protocol Tcp -Direction Inbound -SourcePortRange * -SourceAddressPrefix * -DestinationPortRange 9000,9003,1438,1440,1452 -DestinationAddressPrefix * | Add-AzNetworkSecurityRuleConfig -Priority 200 -Name "allow_misubnet_inbound" -Access Allow -Protocol * -Direction Inbound -SourcePortRange * -SourceAddressPrefix $secondaryMiSubnetAddress -DestinationPortRange * -DestinationAddressPrefix * | Add-AzNetworkSecurityRuleConfig -Priority 300 -Name "allow_health_probe_inbound" -Access Allow -Protocol * -Direction Inbound -SourcePortRange * -SourceAddressPrefix AzureLoadBalancer -DestinationPortRange * -DestinationAddressPrefix * | Add-AzNetworkSecurityRuleConfig -Priority 1000 -Name "allow_tds_inbound" -Access Allow -Protocol Tcp -Direction Inbound -SourcePortRange * -SourceAddressPrefix VirtualNetwork -DestinationPortRange 1433 -DestinationAddressPrefix * | Add-AzNetworkSecurityRuleConfig -Priority 1100 -Name "allow_redirect_inbound" -Access Allow -Protocol Tcp -Direction Inbound -SourcePortRange * -SourceAddressPrefix VirtualNetwork -DestinationPortRange 11000-11999 -DestinationAddressPrefix * | Add-AzNetworkSecurityRuleConfig -Priority 1200 -Name "allow_geodr_inbound" -Access Allow -Protocol Tcp -Direction Inbound -SourcePortRange * -SourceAddressPrefix VirtualNetwork -DestinationPortRange 5022 -DestinationAddressPrefix * | Add-AzNetworkSecurityRuleConfig -Priority 4096 -Name "deny_all_inbound" -Access Deny -Protocol * -Direction Inbound -SourcePortRange * -SourceAddressPrefix * -DestinationPortRange * -DestinationAddressPrefix * | Add-AzNetworkSecurityRuleConfig -Priority 100 -Name "allow_management_outbound" -Access Allow -Protocol Tcp -Direction Outbound -SourcePortRange * -SourceAddressPrefix * -DestinationPortRange 80,443,12000 -DestinationAddressPrefix * | Add-AzNetworkSecurityRuleConfig -Priority 200 -Name "allow_misubnet_outbound" -Access Allow -Protocol * -Direction Outbound -SourcePortRange * -SourceAddressPrefix * -DestinationPortRange * -DestinationAddressPrefix $secondaryMiSubnetAddress | Add-AzNetworkSecurityRuleConfig -Priority 1100 -Name "allow_redirect_outbound" -Access Allow -Protocol Tcp -Direction Outbound -SourcePortRange * -SourceAddressPrefix VirtualNetwork -DestinationPortRange 11000-11999 -DestinationAddressPrefix * | Add-AzNetworkSecurityRuleConfig -Priority 1200 -Name "allow_geodr_outbound" -Access Allow -Protocol Tcp -Direction Outbound -SourcePortRange * -SourceAddressPrefix VirtualNetwork -DestinationPortRange 5022 -DestinationAddressPrefix * | Add-AzNetworkSecurityRuleConfig -Priority 4096 -Name "deny_all_outbound" -Access Deny -Protocol * -Direction Outbound -SourcePortRange * -SourceAddressPrefix * -DestinationPortRange * -DestinationAddressPrefix * | Set-AzNetworkSecurityGroup
Get-AzRouteTable -ResourceGroupName $secondaryResourcegroupname -Name "secondaryRouteTableMiManagementService" | Add-AzRouteConfig -Name "secondaryToMIManagementService" -AddressPrefix 0.0.0.0/0 -NextHopType Internet | Add-AzRouteConfig -Name "ToLocalClusterNode" -AddressPrefix $secondaryMiSubnetAddress -NextHopType VnetLocal | Set-AzRouteTable
Write-host "Secondary network security group configured successfully."
write-host "Adding deligation...."
$vnet = Get-AzVirtualNetwork -Name $secondaryVNet -ResourceGroupName $secondaryResourcegroupname
$subnet = Get-AzVirtualNetworkSubnetConfig -Name $secondaryMiSubnetName -VirtualNetwork $vnet
$subnet = Add-AzDelegation -name "Z" -ServiceName "Microsoft.Sql/managedInstances" -Subnet $subnet
Set-AzVirtualNetwork -VirtualNetwork $vnet
###########################################################################################
##Computation 3
## Creating secondary Managed instance##
Write-host "Creating secondary SQL Managed Instance..."
Write-host "This will take some time, see https://docs.microsoft.com/azure/sql-database/sql-database-managed-instance#managed-instance-management-operations or more information."
New-AzSqlInstance -Name $secondaryManagedInstanceName -ResourceGroupName $secondaryResourcegroupname -Location $SecondaryMIlocation -SubnetId $secondaryMiSubnetConfig.Id -AdministratorCredential $mycreds -StorageSizeInGB $maxStorage -VCore $vCores -Edition $edition -ComputeGeneration $computeGeneration -DnsZonePartner $primaryManagedInstanceId
################################################################################################
##Computation 4
## creating gateway subnet and VNET gW to peer with primary
Write-host "Adding GatewaySubnet to secondary VNet..."
Get-AzVirtualNetwork -Name $secondaryVNet -ResourceGroupName $secondaryResourcegroupname |Add-AzVirtualNetworkSubnetConfig -Name "GatewaySubnet" -AddressPrefix $secondaryMiGwSubnetAddress | Set-AzVirtualNetwork
$secondaryVirtualNetwork = Get-AzVirtualNetwork -Name $secondaryVNet -ResourceGroupName $secondaryResourcegroupname
$secondaryGatewaySubnet = Get-AzVirtualNetworkSubnetConfig -Name "GatewaySubnet" -VirtualNetwork $secondaryVirtualNetwork
$drLocation = $secondaryVirtualNetwork.Location
Write-host "Creating secondary gateway..."
Write-host "This will take some time."
$secondaryGWPublicIP = New-AzPublicIpAddress -Name $secondaryGWPublicIPAddress -ResourceGroupName $secondaryResourcegroupname -Location $drLocation -AllocationMethod Dynamic
$secondaryGatewayIPConfig = New-AzVirtualNetworkGatewayIpConfig -Name $secondaryGWIPConfig -Subnet $secondaryGatewaySubnet -PublicIpAddress $secondaryGWPublicIP
$secondaryGateway = New-AzVirtualNetworkGateway -Name $secondaryGWName -ResourceGroupName $secondaryResourcegroupname -Location $drLocation -IpConfigurations $secondaryGatewayIPConfig -GatewayType Vpn -VpnType RouteBased -GatewaySku VpnGw1 -EnableBgp $true -Asn $secondaryGWAsn
################################################################
##################################################################################
##Computation 5
## Add GW conenctions for Primary and secondary gateway
Write-host "Setting up Gateway peering..."
$SecondGW = Get-AzVirtualNetworkGateway -Name $secondaryGWName -ResourceGroupName $secondaryResourcegroupname
New-AzVirtualNetworkGatewayConnection -Name "Milink" -ResourceGroupName $secondaryResourcegroupname -VirtualNetworkGateway1 $SecondGW -VirtualNetworkGateway2 $PrimaryGW -Location $SecondaryMIlocation -ConnectionType Vnet2Vnet -SharedKey 'AzureA1b2C3'
Set-AzContext -Subscription $PrimaryMiSubID
New-AzVirtualNetworkGatewayConnection -Name "Milink" -ResourceGroupName $primaryMIRsourceGroupName -VirtualNetworkGateway1 $PrimaryGW -VirtualNetworkGateway2 $SecondGW -Location $primaryMILocation -ConnectionType Vnet2Vnet -SharedKey 'AzureA1b2C3'
Start-sleep -Seconds 600
##############################################
##Compute 6
## Create Cross Subscription FOG
Write-host "Creating the cross subscription MI failover group..."
$failoverGroup = New-AzSqlDatabaseInstanceFailoverGroup -Name $failoverGroupName -Location $primaryMILocation -ResourceGroupName $primaryMIRsourceGroupName -PrimaryManagedInstanceName $primaryManagedInstanceName -PartnerSubscriptionId $secondaryMISubID -PartnerRegion $SecondaryMIlocation -PartnerResourceGroupName $secondaryResourcegroupname -PartnerManagedInstanceName $secondaryManagedInstanceName -FailoverPolicy Automatic -GracePeriodWithDataLossHours 1
}
Step 3
Now Call the function and Supply the values for asked parameters. Example Below .
CrosssubscriptionMI-Failovergroup
After you provide all the details, the code will start
- Creating a VNET and required Subnets in the secondary Subscription with NSG and Delegation applied
- Create a New Managed instance, which will work as a secondary in the FOG.
- Creating a VNET Gateway in the Secondary VNET.
- Peering both the VNET over GW , Secure tunneling .
- Creating the FOG , and start Seeding .
Thank you for Reading, Please share your Feedback in the Comment section 
