Configure a failover group for Azure SQL Managed Instance

To create virtual network for your secondary instance in the Azure portal, follow these steps:

1. Check the address space for the primary instance. Go to the virtual network resource for the primary instance in the Azure portal and under Settings, select Address space. Check the range under Address range:

   

2. Create a new virtual network that you plan to use for the secondary instance by going to the Create virtual network page.

3. On the Basics tab of the Create virtual network page:

   1. Select the Resource Group you intend to use for the secondary instance. Create a new one if it doesn't exist yet.
   2. Provide a name for your virtual network, such as vnet-sql-mi-secondary.
   3. Select a region that's paired with the region where the primary instance is.

4. On the IP addresses tab of the Create virtual network page:

   1. Use Delete address space to delete the existing IPv4 address space.
   2. After the address space is deleted, select Add IPv4 address space to add a new space, and then provide an IP address space that is different to the address space used by the virtual network of the primary instance. For example, if your current primary instance uses an address space of 10.0.0.16, then enter 10.1.0.0/16 for the address space of the virtual network you intend to use for the secondary instance.
   3. Use + Add a subnet to add a default subnet with default values.
   4. Use + Add a subnet to add an empty subnet named ManagedInstance that will be dedicated to the secondary instance, using an address range different to the default subnet. For example, if your primary instance uses an address range of 10.0.0.0 - 10.0.255.255, then provide a subnet range of 10.1.1.0 - 10.1.1.255 for the subnet of the secondary instance.

   

5. Use Review + Create to review your settings and then use Create to create your new virtual network.

After your virtual network is ready, follow these steps to create your secondary instance in the Azure portal:

1. Go to the Create Azure SQL Managed Instance in the Azure portal.

2. On the Basics tab of the Create Azure SQL Managed Instance page:

   1. Choose a region for your secondary instance that's paired with the primary instance.
   2. Choose a service tier that matches the service tier of the primary instance.

3. On the Networking tab of the Create Azure SQL Managed Instance page, use the dropdown list under Virtual network / subnet to select the virtual network and subnet you previously created:

   

4. On the Additional settings tab of the Create Azure SQL Managed Instance page, select Yes to Use as failover secondary and then choose the appropriate primary instance from the dropdown list.

   

5. Configure the rest of the instance according to your business needs, and then create it by using Review + Create.

You can create both your virtual network, and your secondary instance by using PowerShell. For a full PowerShell script sample that does both, review Add instance to failover group with PowerShell.

When you create your secondary instance with Azure PowerShell, be sure to specify the DnsZonePartner value with the zone ID of the primary instance.

You can use the Get-AzSqlInstance command to parameterize the DNS zone ID of the primary instance, and then pass it to the DnsZonePartner parameter of the New-AzSqlInstance command:

# Get the DNS zone ID of the primary instance
$primaryManagedInstanceId = Get-AzSqlInstance -Name $primaryInstance -ResourceGroupName $resourceGroupName | Select-Object Id

1. In the Azure portal, go to the Virtual network resource for your primary managed instance.

2. Select Peerings under Settings to open the Peerings page and then use + Add from the command bar to open the Add peering page.

   

3. On the Add peering page, enter or select values for the following settings:

   Expand table

   Settings	Description
   Remote virtual network summary
   Peering link name	The name for the peering must be unique within the virtual network. This article uses Fog-peering.
   Virtual network deployment model	Select Resource manager.
   I know my resource ID	You can leave this box unchecked, unless you know the resource ID.
   Subscription	Select the subscription from the dropdown list.
   Virtual network	Select the virtual network for the secondary instance from the dropdown list.
   Remote virtual network peering settings
   Allow 'secondary virtual network' to access 'primary virtual network'	Check the box to allow communication between the two networks. Enabling communication between virtual networks allows resources that are connected to either virtual network to communicate with each other with the same bandwidth and latency as if they were connected to the same virtual network. All communication between resources in the two virtual networks is over the Azure private network.
   Allow 'secondary virtual network' to receive forwarded traffic from 'primary virtual network'	You can either check or uncheck this box, either works for this guide. For more information, see Create a peering.
   Allow gateway or route server in 'secondary virtual network' to forward traffic to 'primary virtual network'	You can either check or uncheck this box, either works for this guide. For more information, see Create a peering.
   Enable 'secondary virtual network' to use 'primary virtual network's remote gateway or route server	Leave this box unchecked. For more information about the other options available, see Create a peering.
   Local virtual network summary
   Peering link name	The name of the same peering used for the remote virtual network. This article uses Fog-peering.
   Allow 'primary virtual network' to access 'secondary virtual network'	Check the box to allow communication between the two networks. Enabling communication between virtual networks allows resources that are connected to either virtual network to communicate with each other with the same bandwidth and latency as if they were connected to the same virtual network. All communication between resources in the two virtual networks is over the Azure private network.
   Allow 'primary virtual network' to receive forwarded traffic from 'secondary virtual network'	You can either check or uncheck this box, either works for this guide. For more information, see Create a peering.
   Allow gateway or route server in 'primary virtual network' to forward traffic to 'secondary virtual network'	You can either check or uncheck this box, either works for this guide. For more information, see Create a peering.
   Enable 'primary virtual network' to use 'secondary virtual network's remote gateway or route server	Leave this box unchecked. For more information about the other options available, see Create a peering.

4. Use Add to configure peering with the virtual network you selected, and automatically navigate back to the Peerings page, which shows the two networks are connected:

   

Configure global virtual network peering between the virtual networks of the primary and secondary managed instances:

# Create global virtual network peering
$primaryVirtualNetwork  = Get-AzVirtualNetwork -Name $primaryVNet -ResourceGroupName $resourceGroupName

$secondaryVirtualNetwork = Get-AzVirtualNetwork -Name $secondaryVNet -ResourceGroupName $resourceGroupName
                  
Write-host "Peering primary VNet to secondary VNet..."
Add-AzVirtualNetworkPeering -Name primaryVnet-secondaryVNet1 -VirtualNetwork $primaryVirtualNetwork -RemoteVirtualNetworkId $secondaryVirtualNetwork.Id
 Write-host "Primary VNet peered to secondary VNet successfully."

Write-host "Peering secondary VNet to primary VNet..."
Add-AzVirtualNetworkPeering -Name secondaryVNet-primaryVNet -VirtualNetwork $secondaryVirtualNetwork -RemoteVirtualNetworkId $primaryVirtualNetwork.Id
Write-host "Secondary VNet peered to primary VNet successfully."

Write-host "Checking peering state on the primary virtual network..."
Get-AzVirtualNetworkPeering -ResourceGroupName $resourceGroupName -VirtualNetworkName $primaryVNet | Select PeeringState

Write-host "Checking peering state on the secondary virtual network..."
Get-AzVirtualNetworkPeering -ResourceGroupName $resourceGroupName -VirtualNetworkName $secondaryVNet | Select PeeringState

This code snippet uses the following commands:

To open Network Security Group (NSG) ports in the Azure portal, follow these steps:

1. Go to the Network security group resource for the primary instance.

2. Under Settings, select Inbound security rules. Check to see if you already have rules that allow traffic for port 5022, and the range 11000-11999. If you do, and the source meets your business needs, skip this step. If the rules don't exist, or if you want to use a different source (such as the more secure IP address) delete the existing rule, and then select + Add from the command bar to open the Add inbound security rule pane:

   

3. On the Add inbound security rule pane, enter, or select values for the following settings:

   Expand table

   Settings	Recommended value	Description
   Source	IP addresses or Service Tag	The filter for the source of the communication. IP address is the most secure, and recommended for production environments. Service tag is appropriate for nonproduction environments.
   Source service tag	If you selected Service tag as the source, then provide VirtualNetwork as the source tag.	Default tags are predefined identifiers that represent a category of IP addresses. The VirtualNetwork tag denotes all virtual and local network address spaces.
   Source IP addresses	If you selected IP addresses as the source, then provide the IP address of the secondary instance.	Provide an address range using CIDR notation (e.g. 192.168.99.0/24 or 2001:1234::/64), or an IP address (e.g. 192.168.99.0 or 2001:1234::). You can also provide a comma-separated list of IP addresses or address ranges using either IPv4 or IPv6.
   Source port ranges	5022	This specifies on which ports traffic will be allowed this rule.
   Service	Custom	The service specifies the destination protocol and port range for this rule.
   Destination port ranges	5022	This specifies on which ports traffic will be allowed this rule. This port should match the source port range.
   Action	Allow	Allow communication on the specified port.
   Protocol	TCP	Determines the protocol for port communication.
   Priority	1200	Rules are processed in priority order; the lower the number, the higher the priority.
   Name	allow_geodr_inbound	The name of the rule.
   Description	Optional	You can choose to provide a description, or leave this field blank.

   Select Add to save your settings and add your new rule.

4. Repeat these steps to add another inbound security rule for the port range 11000-11999 with a name such as allow_redirect_inbound and a priority slightly higher than the 5022 rule, such as 1100.

5. Under Settings, select Outbound security rules. Check to see if you already have rules that allow traffic for port 5022, and the range 11000-11999. If you do, and the source meets your business needs, skip this step. If the rules don't exist, or if you want to use a different source (such as the more secure IP address) delete the existing rule, and then select + Add from the command bar to open the Add outbound security rule pane.

6. On the Add outbound security rule pane, use the same configuration for port 5022, and the range 11000-11999 as you did for the inbound ports.

7. Go to the Network security group for the secondary instance, and repeat these steps so that both network security groups have the following rules:

   • Allow inbound traffic on port 5022
   • Allow inbound traffic on port range 11000-11999
   • Allow outbound traffic on port 5022
   • Allow outbound traffic on port range 11000-11999

You can configure the NSG rules for both virtual networks by using the Add-AzNetworkSecurityRuleConfig PowerShell command to modify the existing network security group. For a full PowerShell script sample that configures both networks, review Add instance to failover group with PowerShell.

Create the failover group for your SQL Managed Instances by using the Azure portal.

1. Select Azure SQL in the left-hand menu of the Azure portal. If Azure SQL isn't in the list, select All services, then type Azure SQL in the search box. (Optional) Select the star next to Azure SQL to add it as a favorite item to the left-hand navigation.

2. Select the primary managed instance you want to add to the failover group.

3. Under Data Management, select Failover groups and then use Add group to open the Instance Failover Group page:

   

4. On the Instance Failover Group page:

   1. The Primary managed instance is preselected.
   2. Under Failover group name enter a name for the failover group.
   3. Under Secondary managed instance, select the managed instance you want to use as the secondary in the failover group.
   4. Choose a Read/Write failover policy from the dropdown list. Manual is recommended to give you control over failover.
   5. Leave Enable failover rights to Off, unless you intend to use this replica for disaster recovery only.
   6. Use Create to save your settings and create your failover group.

   

5. After failover group deployment starts, you're taken back to the Failover groups page. The page refreshes to show you the new failover group after deployment completes.

Use New-AzSqlDatabaseInstanceFailoverGroup to create the failover group for your managed instances by using PowerShell.

# Create failover group
Write-host "Creating the failover group..."
$failoverGroup = New-AzSqlDatabaseInstanceFailoverGroup -Name $failoverGroupName -Location $location -ResourceGroupName $resourceGroupName -PrimaryManagedInstanceName $primaryInstance -PartnerRegion $drLocation -PartnerManagedInstanceName $secondaryInstance -FailoverPolicy Manual -GracePeriodWithDataLossHours 1
$failoverGroup

Test failover of your failover group using the Azure portal.

1. Go to either the primary or secondary managed instance within the Azure portal.

2. Under Data management, select Failover groups.

3. On the Failover groups pane, note which instance is the primary instance, and which instance is the secondary instance.

4. On the Failover groups pane, select Failover from the command bar. Select Yes on the warning about TDS sessions being disconnected, and note the licensing implication.

   

5. On the Failover groups pane, after failover succeeds, the instances switch roles so the previous secondary becomes the new primary and the previous primary becomes the new secondary.

   Important

   If roles didn't switch, check connectivity between the instances and related NSG and firewall rules. Proceed with the next step only after roles switch.

6. (Optional) On the Failover groups pane, use Failover to switch the roles back so the original primary becomes primary again.

Test planned failover by failing over to the secondary replica with PowerShell, and then failing back.

Use the Get-AzSqlDatabaseInstanceFailoverGroup command to confirm the roles of each instance:

# Verify the current primary role
Get-AzSqlDatabaseInstanceFailoverGroup -ResourceGroupName $resourceGroupName -Location $location -Name $failoverGroupName

Use the Switch-AzSqlDatabaseFailoverGroup to fail over to the secondary instance.

# Failover the primary managed instance to the secondary role
Write-host "Failing primary over to the secondary location"
Get-AzSqlDatabaseInstanceFailoverGroup -ResourceGroupName $resourceGroupName -Location $drLocation -Name $failoverGroupName | Switch-AzSqlDatabaseInstanceFailoverGroup
Write-host "Successfully failed failover group to secondary location"

# Verify the current primary role
Get-AzSqlDatabaseInstanceFailoverGroup -ResourceGroupName $resourceGroupName -Location $drLocation -Name $failoverGroupName

Use the Get-AzSqlDatabaseInstanceFailoverGroup command to confirm the roles of each instance:

# Verify the current primary role
Get-AzSqlDatabaseInstanceFailoverGroup -ResourceGroupName $resourceGroupName -Location $location -Name $failoverGroupName

Optionally, use the Switch-AzSqlDatabaseFailoverGroup command to fail back to the primary instance.

# Fail primary managed instance back to primary role
Write-host "Failing primary back to primary role"
Get-AzSqlDatabaseInstanceFailoverGroup -ResourceGroupName $resourceGroupName -Location $location -Name $failoverGroupName | Switch-AzSqlDatabaseInstanceFailoverGroup
Write-host "Successfully failed failover group to primary location"

# Verify the current primary role
Get-AzSqlDatabaseInstanceFailoverGroup -ResourceGroupName $resourceGroupName -Location $location -Name $failoverGroupName

To modify an existing failover group by using the Azure portal, follow these steps:

1. Go to your SQL managed instance in the Azure portal.

2. Under Data management, select Failover groups to open the Failover groups pane.

3. On the Failover groups pane, select Edit configurations from the command bar to open the Edit failover group pane:

   

To modify an existing failover group use the Set-AzSqlDatabaseInstanceFailoverGroup PowerShell command to modify failover group configuration settings.

To modify an existing failover group, use the az sql instance-failover-group update Azure CLI command to modify failover group configuration settings.

To modify an existing failover group, use the Create or Update Failover Group REST API call to modify failover group configuration settings.