Configure an auto-failover group for Azure SQL Managed Instance

Applies to: Azure SQL Managed Instance

• Azure SQL Database
• Azure SQL Managed Instance

This article teaches you how to configure an auto-failover group for Azure SQL Managed Instance using the Azure portal and Azure PowerShell. For an end-to-end experience, review the Auto-failover group tutorial.

Note

This article covers auto-failover groups for Azure SQL Managed Instance. For Azure SQL Database, see Configure auto-failover groups in SQL Database.

Prerequisites

Consider the following prerequisites:

• The secondary managed instance must be empty that is, contain no user databases.
• The two instances of SQL Managed Instance need to be the same service tier, and have the same storage size. While not required, it's strongly recommended that two instances have equal compute size, to make sure that secondary instance can sustainably process the changes being replicated from the primary instance, including the periods of peak activity.
• The IP address range(s) of the virtual network hosting the primary instance must not overlap with IP address range(s) of the virtual network hosting the secondary instance.
• Network Security Groups (NSG) rules on subnet hosting instance must have port 5022 (TCP) and the port range 11000-11999 (TCP) open inbound and outbound for connections from and to the subnet hosting the other managed instance. This applies to both subnets, hosting primary and secondary instance.
• The secondary SQL Managed Instance is configured during its creation with the correct DNS zone ID. It's accomplished by passing the primary instance's zone ID as the value of DnsZonePartner parameter when creating the secondary instance. If not passed as a parameter, the zone ID is generated as a random string when the first instance is created in each VNet and the same ID is assigned to all other instances in the same subnet. Once assigned, the DNS zone can't be modified.
• The collation and time zone of the secondary managed instance must match that of the primary managed instance.
• Managed instances should be deployed in paired regions for performance reasons. Managed instances residing in geo-paired regions benefit from significantly higher geo-replication speed compared to unpaired regions.

Enabling connectivity between the instances

Connectivity between the virtual network subnets hosting primary and secondary instance must be established for uninterrupted geo-replication traffic flow. Global virtual network peering is recommended as the most performant and robust way for establishing the connectivity. It provides a low-latency, high-bandwidth private connection between the peered virtual networks using the Microsoft backbone infrastructure. No public Internet, gateways, or additional encryption is required in the communication between the peered virtual networks. To learn about alternative ways of establishing connectivity, see enabling replication traffic between instances.

Important

Alternative ways of providing connectivity between the instances involving additional networking devices may make troubleshooting process in case of connectivity or replication speed issues very difficult and require active involvement of network administrators and significantly prolong the resolution time.

Portal

1. In the Azure portal, go to the Virtual network resource for your primary managed instance.
2. Select Peerings under Settings and then select + Add.

1. Enter or select values for the following settings:

   Settings	Description
   This virtual network
   Peering link name	The name for the peering must be unique within the virtual network.
   Traffic to remote virtual network	Select Allow (default) to enable communication between the two virtual networks through the default VirtualNetwork flow. 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.
   Traffic forwarded from remote virtual network	Both Allowed (default) and Block option will work for this tutorial. For more information, see Create a peering
   Virtual network gateway or Route Server	Select None. For more information about the other options available, see Create a peering.
   Remote virtual network
   Peering link name	The name of the same peering to be used in the virtual network hosting secondary instance.
   Virtual network deployment model	Select Resource manager.
   I know my resource ID	Leave this checkbox unchecked.
   Subscription	Select the Azure subscription of the virtual network hosting the secondary instance that you want to peer with.
   Virtual network	Select the virtual network hosting the secondary instance that you want to peer with. If the virtual network is listed, but grayed out, it may be because the address space for the virtual network overlaps with the address space for this virtual network. If virtual network address spaces overlap, they cannot be peered.
   Traffic to remote virtual network	Select Allow (default)
   Traffic forwarded from remote virtual network	Both Allowed (default) and Block option will work for this tutorial. For more information, see Create a peering.
   Virtual network gateway or Route Server	Select None. For more information about the other options available, see Create a peering.

2. Select Add to configure the peering with the virtual network you selected. After a few seconds, select the Refresh button and the peering status will change from Updating to Connected.

   

PowerShell

Create global virtual network peering between virtual networks hosting primary and secondary instance.

# Peer the virtual networks
Write-host "Retreiving primary VNet and secondary VNet..."

$primaryVirtualNetwork  = Get-AzVirtualNetwork `
                  -Name $primaryVNet `
                  -ResourceGroupName $primaryResourceGroupName

$secondaryVirtualNetwork = Get-AzVirtualNetwork -Name $secondaryVNet `
                                -ResourceGroupName $secondaryResourceGroupName

Write-host "Peering primary VNet to secondary VNet..."

Add-AzVirtualNetworkPeering `
 -Name $primaryVnetToSecondaryVNetPeeringName `
 -VirtualNetwork $primaryVirtualNetwork `
 -RemoteVirtualNetworkId $secondaryVirtualNetwork.Id

Write-host "Peering secondary VNet to primary VNet..."

Add-AzVirtualNetworkPeering `
 -Name $secondaryVNetToPrimaryVNetPeeringName`
 -VirtualNetwork $secondaryVirtualNetwork `
 -RemoteVirtualNetworkId $primaryVirtualNetwork.Id

Write-host "Checking peering state on the primary virtual network, expecting state Connected..."

Get-AzVirtualNetworkPeering `
-ResourceGroupName $primaryResourceGroupName `
-VirtualNetworkName $primaryVNet `
| Select PeeringState

Write-host "Checking peering state on the secondary virtual network, expecting state Connected..."

Get-AzVirtualNetworkPeering `
-ResourceGroupName $secondaryResourceGroupName `
-VirtualNetworkName $secondaryVNet `
| Select PeeringState

Create the failover group

Create the failover group for your managed instances by using the Azure portal or PowerShell.

Portal

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 Settings, navigate to Instance Failover Groups and then choose to Add group to open the instance failover group creation page.

   

4. On the Instance Failover Group page, type the name of your failover group and then choose the secondary managed instance from the drop-down. Select Create to create your failover group.

   

5. Once failover group deployment is complete, you'll be taken back to the Failover group page.

PowerShell

Create the failover group for your managed instances using PowerShell.

$primaryResourceGroupName = "<Primary-Resource-Group>"
$failoverGroupName = "<Failover-Group-Name>"
$primaryLocation = "<Primary-Region>"
$secondaryLocation = "<Secondary-Region>"
$primaryManagedInstance = "<Primary-Managed-Instance-Name>"
$secondaryManagedInstance = "<Secondary-Managed-Instance-Name>"

# Create failover group
Write-host "Creating the failover group..."
$failoverGroup = New-AzSqlDatabaseInstanceFailoverGroup -Name $failoverGroupName `
     -Location $primaryLocation -ResourceGroupName $primaryResourceGroupName -PrimaryManagedInstanceName $primaryManagedInstance `
     -PartnerRegion $secondaryLocation -PartnerManagedInstanceName $secondaryManagedInstance `
     -FailoverPolicy Automatic -GracePeriodWithDataLossHours 1
$failoverGroup

Test failover

Test failover of your failover group using the Azure portal or PowerShell.

Portal

Test failover of your failover group using the Azure portal.

1. Navigate to your secondary managed instance within the Azure portal and select Instance Failover Groups under settings.

2. Note managed instances in the primary and in the secondary role.

3. Select Failover and then select Yes on the warning about TDS sessions being disconnected.

   

4. Note managed instances in the primary and in the secondary role. If failover succeeded, the two instances should have switched roles.

   

Important

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

1. Go to the new secondary managed instance and select Failover once again to fail the primary instance back to the primary role.

PowerShell

Test failover of your failover group using PowerShell.

$primaryResourceGroupName = "<Primary-Resource-Group>"
$secondaryResourceGroupName = "<Secondary-Resource-Group>"
$failoverGroupName = "<Failover-Group-Name>"
$primaryLocation = "<Primary-Region>"
$secondaryLocation = "<Secondary-Region>"
$primaryManagedInstance = "<Primary-Managed-Instance-Name>"
$secondaryManagedInstance = "<Secondary-Managed-Instance-Name>"

# Verify the current primary role
Get-AzSqlDatabaseInstanceFailoverGroup -ResourceGroupName $primaryResourceGroupName `
    -Location $secondaryLocation -Name $failoverGroupName

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

# Verify the current primary role
Get-AzSqlDatabaseInstanceFailoverGroup -ResourceGroupName $primaryResourceGroupName `
    -Location $secondaryLocation -Name $failoverGroupName

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

# Verify the current primary role
Get-AzSqlDatabaseInstanceFailoverGroup -ResourceGroupName $primaryResourceGroupName `
    -Location $secondaryLocation -Name $failoverGroupName

Locate listener endpoint

Once your failover group is configured, update the connection string for your application to the listener endpoint. It will keep your application connected to the failover group listener, rather than the primary database, elastic pool, or instance database. That way, you don't have to manually update the connection string every time your database entity fails over, and traffic is routed to whichever entity is currently primary.

The listener endpoint is in the form of fog-name.database.windows.net, and is visible in the Azure portal, when viewing the failover group:

Create group between instances in different subscriptions

You can create a failover group between SQL Managed Instances in two different subscriptions, as long as subscriptions are associated to the same Azure Active Directory Tenant. When using PowerShell API, you can do it by specifying the PartnerSubscriptionId parameter for the secondary SQL Managed Instance. When using REST API, each instance ID included in the properties.managedInstancePairs parameter can have its own Subscription ID.

Important

Azure portal does not support creation of failover groups across different subscriptions. Also, for the existing failover groups across different subscriptions and/or resource groups, failover can't be initiated manually via portal from the primary SQL Managed Instance. Initiate it from the geo-secondary instance instead.

Change the secondary region

Let's assume that instance A is the primary instance, instance B is the existing secondary instance, and instance C is the new secondary instance in the third region. To make the transition, follow these steps:

1. Create instance C with same size as A and in the same DNS zone.
2. Delete the failover group between instances A and B. At this point, the logins will be failing because the SQL aliases for the failover group listeners have been deleted and the gateway won't recognize the failover group name. The secondary databases will be disconnected from the primaries and will become read-write databases.
3. Create a failover group with the same name between instance A and C. Follow the instructions in failover group with SQL Managed Instance tutorial. This is a size-of-data operation and will complete when all databases from instance A are seeded and synchronized.
4. Delete instance B if not needed to avoid unnecessary charges.

Note

After step 2 and until step 3 is completed the databases in instance A will remain unprotected from a catastrophic failure of instance A.

Change the primary region

Let's assume instance A is the primary instance, instance B is the existing secondary instance, and instance C is the new primary instance in the third region. To make the transition, follow these steps:

1. Create instance C with same size as B and in the same DNS zone.
2. Connect to instance B and manually failover to switch the primary instance to B. Instance A will become the new secondary instance automatically.
3. Delete the failover group between instances A and B. At this point, log in attempts using failover group endpoints will be failing. The secondary databases on A will be disconnected from the primaries and will become read-write databases.
4. Create a failover group with the same name between instance B and C. Follow the instructions in the failover group with managed instance tutorial. This is a size-of-data operation and will complete when all databases from instance A are seeded and synchronized. At this point login attempts will stop failing.
5. Manually failover to switch the C instance to primary role. Instance B will become the new secondary instance automatically.
6. Delete instance A if not needed to avoid unnecessary charges.

Caution

After step 3 and until step 4 is completed the databases in instance A will remain unprotected from a catastrophic failure of instance A.

Important

When the failover group is deleted, the DNS records for the listener endpoints are also deleted. At that point, there's a non-zero probability of somebody else creating a failover group with the same name. Because failover group names must be globally unique, this will prevent you from using the same name again. To minimize this risk, don't use generic failover group names.

Permissions

Permissions for a failover group are managed via Azure role-based access control (Azure RBAC).

Azure RBAC write access is necessary to create and manage failover groups. The SQL Managed Instance Contributor role has all the necessary permissions to manage failover groups.

The following table lists specific permission scopes for Azure SQL Managed Instance:

Action	Permission	Scope
Create failover group	Azure RBAC write access	Primary managed instance Secondary managed instance
Update failover group	Azure RBAC write access	Failover group All databases within the managed instance
Fail over failover group	Azure RBAC write access	Failover group on new primary managed instance

Next steps

For detailed steps configuring a failover group, see the Add a managed instance to a failover group tutorial

For an overview of the feature, see Auto-failover groups.