Configure an auto-failover group for Azure SQL Database

Applies to: Azure SQL Database

• Azure SQL Database
• Azure SQL Managed Instance

This topic teaches you how to configure an auto-failover group for single and pooled databases in Azure SQL Database by 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 Database. For Azure SQL Managed Instance, see Configure auto-failover groups in Azure SQL Managed Instance.

Prerequisites

Consider the following prerequisites for creating your failover group for a single database:

• The server login and firewall settings for the secondary server must match that of your primary server.

Create failover group

Portal

Create your failover group and add your single database to it using the Azure portal.

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

2. Select the database you want to add to the failover group.

3. Select the name of the server under Server name to open the settings for the server.

   

4. Select Failover groups under the Settings pane, and then select Add group to create a new failover group.

   

5. On the Failover Group page, enter or select the required values, and then select Create.

   • Databases within the group: Choose the database you want to add to your failover group. Adding the database to the failover group will automatically start the geo-replication process.

   

PowerShell

Create your failover group and add your database to it using PowerShell.

$subscriptionId = "<SubscriptionID>"
$resourceGroupName = "<Resource-Group-Name>"
$location = "<Region>"
$adminLogin = "<Admin-Login>"
$password = "<Complex-Password>"
$serverName = "<Primary-Server-Name>"
$databaseName = "<Database-Name>"
$drLocation = "<DR-Region>"
$drServerName = "<Secondary-Server-Name>"
$failoverGroupName = "<Failover-Group-Name>"

# Create a secondary server in the failover region
Write-host "Creating a secondary server in the failover region..."
$drServer = New-AzSqlServer -ResourceGroupName $resourceGroupName `
   -ServerName $drServerName `
   -Location $drLocation `
   -SqlAdministratorCredentials $(New-Object -TypeName System.Management.Automation.PSCredential `
      -ArgumentList $adminlogin, $(ConvertTo-SecureString -String $password -AsPlainText -Force))
$drServer

# Create a failover group between the servers
$failovergroup = Write-host "Creating a failover group between the primary and secondary server..."
New-AzSqlDatabaseFailoverGroup `
   ResourceGroupName $resourceGroupName `
   -ServerName $serverName `
   -PartnerServerName $drServerName  `
   FailoverGroupName $failoverGroupName `
   FailoverPolicy Automatic `
   -GracePeriodWithDataLossHours 2
$failovergroup

# Add the database to the failover group
Write-host "Adding the database to the failover group..."
Get-AzSqlDatabase `
   -ResourceGroupName $resourceGroupName `
   -ServerName $serverName `
   -DatabaseName $databaseName | `
Add-AzSqlDatabaseToFailoverGroup `
   -ResourceGroupName $resourceGroupName `
   -ServerName $serverName `
   -FailoverGroupName $failoverGroupName
Write-host "Successfully added the database to the failover group..."

Note

It's possible to deploy your auto-failover group across subscriptions by using the -PartnerSubscriptionId parameter in Azure Powershell starting with Az.SQL 3.11.0. To learn more, review the following Example.

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. Select Azure SQL in the left-hand menu of the Azure portal. If Azure SQL is not in the list, select All services, then type "Azure SQL" in the search box. (Optional) Select the star next to Azure SQL to favorite it and add it as an item in the left-hand navigation.

2. Select the database you want to add to the failover group.

   

3. Select Failover groups under the Settings pane and then choose the failover group you just created.

   

4. Review which server is primary and which server is secondary.

5. Select Failover from the task pane to fail over your failover group containing your database.

6. Select Yes on the warning that notifies you that TDS sessions will be disconnected.

   

7. Review which server is now primary and which server is secondary. If failover succeeded, the two servers should have swapped roles.

8. Select Failover again to fail the servers back to their original roles.

PowerShell

Test failover of your failover group using PowerShell.

Check the role of the secondary replica:

# Set variables
$resourceGroupName = "<Resource-Group-Name>"
$serverName = "<Primary-Server-Name>"
$failoverGroupName = "<Failover-Group-Name>"

# Check role of secondary replica
Write-host "Confirming the secondary replica is secondary...."
(Get-AzSqlDatabaseFailoverGroup `
   -FailoverGroupName $failoverGroupName `
   -ResourceGroupName $resourceGroupName `
   -ServerName $drServerName).ReplicationRole

Fail over to the secondary server:

# Set variables
$resourceGroupName = "<Resource-Group-Name>"
$serverName = "<Primary-Server-Name>"
$failoverGroupName = "<Failover-Group-Name>"

# Failover to secondary server
Write-host "Failing over failover group to the secondary..."
Switch-AzSqlDatabaseFailoverGroup `
   -ResourceGroupName $resourceGroupName `
   -ServerName $drServerName `
   -FailoverGroupName $failoverGroupName
Write-host "Failed failover group to successfully to" $drServerName

Revert failover group back to the primary server:

# Set variables
$resourceGroupName = "<Resource-Group-Name>"
$serverName = "<Primary-Server-Name>"
$failoverGroupName = "<Failover-Group-Name>"

# Revert failover to primary server
Write-host "Failing over failover group to the primary...."
Switch-AzSqlDatabaseFailoverGroup `
   -ResourceGroupName $resourceGroupName `
   -ServerName $serverName `
   -FailoverGroupName $failoverGroupName
Write-host "Failed failover group successfully to back to" $serverName

Important

If you need to delete the secondary database, remove it from the failover group before deleting it. Deleting a secondary database before it is removed from the failover group can cause unpredictable behavior.

Prerequisites

Consider the following prerequisites for creating your failover group for a pooled database:

• The server login and firewall settings for the secondary server must match that of your primary server.

Create failover group

Create the failover group for your elastic pool using the Azure portal or PowerShell.

Portal

Create your failover group and add your elastic pool to it using the Azure portal.

1. Select Azure SQL in the left-hand menu of the Azure portal. If Azure SQL is not in the list, select All services, then type "Azure SQL" in the search box. (Optional) Select the star next to Azure SQL to favorite it and add it as an item in the left-hand navigation.

2. Select the elastic pool you want to add to the failover group.

3. On the Overview pane, select the name of the server under Server name to open the settings for the server.

   

4. Select Failover groups under the Settings pane, and then select Add group to create a new failover group.

   

5. On the Failover Group page, enter or select the required values, and then select Create. Either create a new secondary server, or select an existing secondary server.

6. Select Databases within the group then choose the elastic pool you want to add to the failover group. If an elastic pool does not already exist on the secondary server, a warning appears prompting you to create an elastic pool on the secondary server. Select the warning, and then select OK to create the elastic pool on the secondary server.

   

7. Select Select to apply your elastic pool settings to the failover group, and then select Create to create your failover group. Adding the elastic pool to the failover group will automatically start the geo-replication process.

PowerShell

Create your failover group and add your elastic pool to it using PowerShell.

$subscriptionId = "<SubscriptionID>"
$resourceGroupName = "<Resource-Group-Name>"
$location = "<Region>"
$adminLogin = "<Admin-Login>"
$password = "<Complex-Password>"
$serverName = "<Primary-Server-Name>"
$databaseName = "<Database-Name>"
$poolName = "myElasticPool"
$drLocation = "<DR-Region>"
$drServerName = "<Secondary-Server-Name>"
$failoverGroupName = "<Failover-Group-Name>"

# Create a failover group between the servers
Write-host "Creating failover group..."
New-AzSqlDatabaseFailoverGroup `
    ResourceGroupName $resourceGroupName `
    -ServerName $serverName `
    -PartnerServerName $drServerName  `
    FailoverGroupName $failoverGroupName `
    FailoverPolicy Automatic `
    -GracePeriodWithDataLossHours 2
Write-host "Failover group created successfully."

# Add elastic pool to the failover group
Write-host "Enumerating databases in elastic pool...."
$FailoverGroup = Get-AzSqlDatabaseFailoverGroup `
                 -ResourceGroupName $resourceGroupName `
                 -ServerName $serverName `
                 -FailoverGroupName $failoverGroupName
$databases = Get-AzSqlElasticPoolDatabase `
            -ResourceGroupName $resourceGroupName `
            -ServerName $serverName `
            -ElasticPoolName $poolName
Write-host "Adding databases to failover group..."
$failoverGroup = $failoverGroup | Add-AzSqlDatabaseToFailoverGroup `
                                  -Database $databases
Write-host "Databases added to failover group successfully."

Test failover

Test failover of your elastic pool using the Azure portal or PowerShell.

Portal

Fail your failover group over to the secondary server, and then fail back using the Azure portal.

1. Select Azure SQL in the left-hand menu of the Azure portal. If Azure SQL is not in the list, select All services, then type "Azure SQL" in the search box. (Optional) Select the star next to Azure SQL to favorite it and add it as an item in the left-hand navigation.

2. Select the elastic pool you want to add to the failover group.

3. On the Overview pane, select the name of the server under Server name to open the settings for the server.

   

4. Select Failover groups under the Settings pane and then choose the failover group you created in section 2.

   

5. Review which server is primary, and which server is secondary.

6. Select Failover from the task pane to fail over your failover group containing your elastic pool.

7. Select Yes on the warning that notifies you that TDS sessions will be disconnected.

   

8. Review which server is primary, which server is secondary. If failover succeeded, the two servers should have swapped roles.

9. Select Failover again to fail the failover group back to the original settings.

PowerShell

Test failover of your failover group using PowerShell.

Check the role of the secondary replica:

# Set variables
$resourceGroupName = "<Resource-Group-Name>"
$serverName = "<Primary-Server-Name>"
$failoverGroupName = "<Failover-Group-Name>"

# Check role of secondary replica
Write-host "Confirming the secondary replica is secondary...."
(Get-AzSqlDatabaseFailoverGroup `
   -FailoverGroupName $failoverGroupName `
   -ResourceGroupName $resourceGroupName `
   -ServerName $drServerName).ReplicationRole

Fail over to the secondary server:

# Set variables
$resourceGroupName = "<Resource-Group-Name>"
$serverName = "<Primary-Server-Name>"
$failoverGroupName = "<Failover-Group-Name>"

# Failover to secondary server
Write-host "Failing over failover group to the secondary..."
Switch-AzSqlDatabaseFailoverGroup `
   -ResourceGroupName $resourceGroupName `
   -ServerName $drServerName `
   -FailoverGroupName $failoverGroupName
Write-host "Failed failover group to successfully to" $drServerName

Important

If you need to delete the secondary database, remove it from the failover group before deleting it. Deleting a secondary database before it is removed from the failover group can cause unpredictable behavior.

Use Private Link

Using a private link allows you to associate a logical server to a specific private IP address within the virtual network and subnet.

To use a private link with your failover group, do the following:

1. Ensure your primary and secondary servers are in a paired region.
2. Create the virtual network and subnet in each region to host private endpoints for primary and secondary servers such that they have non-overlapping IP address spaces. For example, the primary virtual network address range of 10.0.0.0/16 and the secondary virtual network address range of 10.0.0.1/16 overlaps. For more information about virtual network address ranges, see the blog designing Azure virtual networks.
3. Create a private endpoint and Azure Private DNS zone for the primary server.
4. Create a private endpoint for the secondary server as well, but this time choose to reuse the same Private DNS zone that was created for the primary server.
5. Once the private link is established, you can create the failover group following the steps outlined previously in this article.

Locate listener endpoint

Once your failover group is configured, update the connection string for your application to the listener endpoint. This 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:

Change the secondary region

To illustrate the change sequence, we will assume that server A is the primary server, server B is the existing secondary server, and server C is the new secondary in the third region. To make the transition, follow these steps:

1. Create additional secondaries of each database on server A to server C using active geo-replication. Each database on server A will have two secondaries, one on server B and one on server C. This will guarantee that the primary databases remain protected during the transition.
2. Delete the failover group. At this point login attempts using failover group endpoints will be failing.
3. Re-create the failover group with the same name between servers A and C.
4. Add all primary databases on server A to the new failover group. At this point the login attempts will stop failing.
5. Delete server B. All databases on B will be deleted automatically.

Change the primary region

To illustrate the change sequence, we will assume server A is the primary server, server B is the existing secondary server, and server C is the new primary in the third region. To make the transition, follow these steps:

1. Perform a planned geo-failover to switch the primary server to B. Server A will become the new secondary server. The failover may result in several minutes of downtime. The actual time will depend on the size of failover group.
2. Create additional secondaries of each database on server B to server C using active geo-replication. Each database on server B will have two secondaries, one on server A and one on server C. This will guarantee that the primary databases remain protected during the transition.
3. Delete the failover group. At this point login attempts using failover group endpoints will be failing.
4. Re-create the failover group with the same name between servers B and C.
5. Add all primary databases on B to the new failover group. At this point the login attempts will stop failing.
6. Perform a planned geo-failover of the failover group to switch B and C. Now server C will become the primary and B the secondary. All secondary databases on server A will be automatically linked to the primaries on C. As in step 1, the failover may result in several minutes of downtime.
7. Delete server A. All databases on A will be deleted automatically.

Important

When the failover group is deleted, the DNS records for the listener endpoints are also deleted. At that point, there is a non-zero probability of somebody else creating a failover group or a server DNS alias with the same name. Because failover group names and DNS aliases 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 Server Contributor role has all the necessary permissions to manage failover groups.

The following table lists specific permission scopes for Azure SQL Database:

Action	Permission	Scope
Create failover group	Azure RBAC write access	Primary server Secondary server All databases in failover group
Update failover group	Azure RBAC write access	Failover group All databases on the current primary server
Fail over failover group	Azure RBAC write access	Failover group on new server

Remarks

• Removing a failover group for a single or pooled database does not stop replication, and it does not delete the replicated database. You will need to manually stop geo-replication and delete the database from the secondary server if you want to add a single or pooled database back to a failover group after it's been removed. Failing to do either may result in an error similar to The operation cannot be performed due to multiple errors when attempting to add the database to the failover group.
• Auto-failover group name is subject to naming restrictions.

Next steps

For detailed steps configuring a failover group, see the following tutorials:

• Add a single database to a failover group
• Add an elastic pool to a failover group
• Add a managed instance to a failover group

For an overview of Azure SQL Database high availability options, see geo-replication and auto-failover groups.