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. This Article may get used as well to move your existing Managed instance from one subscription to another with merely zero downtime.
Two different procedures will be presented to cover two different options to connect virtual networks from both Managed Instances: VNET Gateway and Global VNET Peering.
Important:
- Admin Password for the Secondary Managed instance will be autogenerated, you can reset the password after the MI is created.
- Make sure Primary VNET and Secondary VNET address spaces do 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.
Option 1 - Using VNET Gateway
Note:
- It is expected you already have an existing Managed Instance (primary) and a VNET Gateway in the Primary Managed instance VNET along with a Resource group in the secondary subscription, Which will be used to deploy the secondary MI and VNET Reference: Tutorial – Create & manage a VPN gateway – Azure portal - Azure VPN Gateway | Microsoft Docs
- This procedure 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.
- Both the managed Instances in the FOG and VNET Gateways needed to be in the Same configurations
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]$secondaryMiSubnetAddress,
[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.
- Creating 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 tunnelling .
- Creating the FOG, and start Seeding.
Option 2 - Using Global VNET Peering
Note:
- It is expected you already have an existing Managed Instance (primary), which will be used to deploy the secondary MI and secondary VNET.
- This procedure will help you to build an entire new Managed instance in a separate Azure subscription by setting up resource groups and all network requirements including VNET, Subnets, Peering. The new managed instance will work as the Secondary in the FOG. Global VNET peering will be configured in order to establish the traffic between primary and secondary Managed Instances.
- Make sure subnet where your primary Managed Instance has been created from September 22nd, 2020 onwards, so that Global VNET peering is supported: How to check if global virtual network peering is supported on the virtual cluster for SQL MI (microsoft.com)
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 Set-CrossSubscriptionMIFailoverGroupVNETPeering.
Function Set-CrossSubscriptionMIFailoverGroupVNETPeering
{
param
(
[parameter(Mandatory=$true)][string]$primaryMISubID,
[parameter(Mandatory=$true)][string]$primaryManagedInstanceName,
[parameter(Mandatory=$true)][string]$secondaryMISubID,
[parameter(Mandatory=$true)][string]$secondaryMIResourceGroupName,
[parameter(Mandatory=$true)][string]$secondaryVNetResourceGroupName,
[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]$secondaryVNetName,
[parameter(Mandatory=$true)][string]$secondaryAddressPrefix,
[parameter(Mandatory=$true)][string]$secondaryMISubnetName,
[parameter(Mandatory=$true)][string]$secondaryMISubnetAddress,
[parameter(Mandatory=$true)][string]$failoverGroupName
)
#Secondary MI credentials
$secpasswd = "PWD27!"+(New-Guid).Guid | ConvertTo-SecureString -AsPlainText -Force #secondaryMIPassword
$mycreds = New-Object System.Management.Automation.PSCredential ($adminAccountName, $secpasswd) ##secondaryMICredentials
################################################################################################
#Step 1
#Connect to primary to get Primary MI info
Set-AzContext -Subscription $primaryMISubID
$primaryMI = Get-AzSqlInstance -Name $primaryManagedInstanceName
$primaryMIResourceGroupName = $primaryMI.ResourceGroupName
$primaryMILocation = $primaryMI.Location
$primaryMIVNetName = ($primaryMI.subnetId -Split '/')[-3]
$primaryManagedInstanceId = $primaryMI.Id
$primaryVnet = Get-AzVirtualNetwork -Name $primaryMIVNetName
#####################################################################################
#Step 2
##Connect to secondary to create VNET resources
Set-AzContext -Subscription $secondaryMISubID
#Create Secondary VNET Resource Group, if it doesn't exist
if (!(Get-AzResourceGroup -Name $secondaryVNetResourceGroupName -ErrorAction SilentlyContinue)) {New-AzResourceGroup -Name $secondaryVNetResourceGroupName -Location $secondaryMILocation }
#Setting up Secondary Network Requirements
$secondaryVNet = New-AzVirtualNetwork -ResourceGroupName $secondaryVNetResourceGroupName -Location $secondaryMILocation -Name $secondaryVNetName -AddressPrefix $secondaryAddressPrefix
Add-AzVirtualNetworkSubnetConfig -Name $secondaryMISubnetName -VirtualNetwork $secondaryVNet -AddressPrefix $secondaryMISubnetAddress | Set-AzVirtualNetwork
# Configure the secondary managed instance subnet
Write-host "Configuring secondary MI subnet..."
$secondaryVNet = Get-AzVirtualNetwork -Name $secondaryVNetName -ResourceGroupName $secondaryVNetResourceGroupName
$secondaryMISubnetConfig = Get-AzVirtualNetworkSubnetConfig -Name $secondaryMISubnetName -VirtualNetwork $secondaryVNet
# 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 $secondaryVNetResourceGroupName -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 $secondaryVNetResourceGroupName -location $secondaryMILocation
# Configure the secondary network security group
Write-host "Configuring secondary network security group..."
Set-AzVirtualNetworkSubnetConfig -VirtualNetwork $secondaryVNet -Name $secondaryMISubnetName -AddressPrefix $secondaryMISubnetAddress -NetworkSecurityGroup $secondaryNSGMiManagementService -RouteTable $secondaryRouteTableMiManagementService|Set-AzVirtualNetwork
Get-AzNetworkSecurityGroup -ResourceGroupName $secondaryVNetResourceGroupName -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 $secondaryVNetResourceGroupName -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 delegation...."
$vnet = Get-AzVirtualNetwork -Name $secondaryVNetName -ResourceGroupName $secondaryVNetResourceGroupName
$subnet = Get-AzVirtualNetworkSubnetConfig -Name $secondaryMISubnetName -VirtualNetwork $vnet
$subnet = Add-AzDelegation -name "Z" -ServiceName "Microsoft.Sql/managedInstances" -Subnet $subnet
Set-AzVirtualNetwork -VirtualNetwork $vnet
###########################################################################################
##Step 3
##Peering the virtual networks
Set-AzContext -Subscription $primaryMISubID
Write-host "Peering primary VNet to secondary VNet..."
$primaryPeering = $primaryMIVNetName + '-TO-' + $secondaryVNetName
Add-AzVirtualNetworkPeering -Name $primaryPeering -VirtualNetwork $primaryVnet -RemoteVirtualNetworkId $secondaryVNet.Id
Set-AzContext -Subscription $secondaryMISubID
Write-host "Peering secondary VNet to primary VNet..."
$secondaryPeering = $secondaryVNetName + '-TO-' + $primaryMIVNetName
Add-AzVirtualNetworkPeering -Name $secondaryPeering -VirtualNetwork $secondaryVNet -RemoteVirtualNetworkId $primaryVNet.Id
Set-AzContext -Subscription $primaryMISubID
Write-host "Checking peering state on the primary virtual network..."
Get-AzVirtualNetworkPeering -ResourceGroupName $primaryVNet.ResourceGroupName -VirtualNetworkName $primaryMIVNetName -Name $primaryPeering| Select PeeringState
Set-AzContext -Subscription $secondaryMISubID
Write-host "Checking peering state on the secondary virtual network..."
Get-AzVirtualNetworkPeering -ResourceGroupName $secondaryVNet.ResourceGroupName -VirtualNetworkName $secondaryVNetName -Name $secondaryPeering| Select PeeringState
###########################################################################################
##Step 4
#Creating Secondary MI Resource Group, if it doesn't exist
if (!(Get-AzResourceGroup -Name $secondaryMIResourceGroupName -ErrorAction SilentlyContinue)) {New-AzResourceGroup -Name $secondaryMIResourceGroupName -Location $secondaryMILocation }
## 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 $secondaryMIResourceGroupName -Location $secondaryMILocation -SubnetId $secondaryMISubnetConfig.Id -AdministratorCredential $mycreds -StorageSizeInGB $maxStorage -VCore $vCores -Edition $edition -ComputeGeneration $computeGeneration -DnsZonePartner $primaryManagedInstanceId
###########################################################################################
##Step 5
##Create Cross Subscription FOG
Set-AzContext -Subscription $primaryMISubID
Write-host "Creating the cross subscription MI failover group..."
$failoverGroup = New-AzSqlDatabaseInstanceFailoverGroup -Name $failoverGroupName -Location $primaryMILocation -ResourceGroupName $primaryMIResourceGroupName -PrimaryManagedInstanceName $primaryManagedInstanceName -PartnerSubscriptionId $secondaryMISubID -PartnerRegion $secondaryMILocation -PartnerResourceGroupName $secondaryMIResourceGroupName -PartnerManagedInstanceName $secondaryManagedInstanceName -FailoverPolicy Automatic -GracePeriodWithDataLossHours 1
}
Step 3
Now call the function and supply the values for asked parameters. Example below.
Set-CrossSubscriptionMIFailoverGroupVNETPeering
After you provide all the details, the code will start
- Creating resource group(s) (if not already existing), a VNET and required Subnets in the secondary Subscription with NSG and Delegation applied.
- Peering both VNETs.
- Creating a New Managed instance, which will work as a secondary in the FOG.
- Creating the FOG , and start Seeding.
Thank you for Reading, Please share your Feedback in the Comment section 
Updated Mar 02, 2023
Version 5.0
Swabhiman_Das
Microsoft
Microsoft
