Configure a failover group for Azure SQL Database

APPLIES TO: yesAzure SQL Database yesAzure SQL Managed Instance

This topic teaches you how to configure an auto-failover group for Azure SQL Database and Azure SQL Managed Instance.

Single database in Azure SQL Database

Create the failover group and add a single database to it using the Azure portal or PowerShell.

Prerequisites

Consider the following prerequisites:

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

Create failover group

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.

    Open server for single db

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

    Add 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.

    Add SQL Database to failover group

Test failover

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

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.

    Open server for single db

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

    Select the failover group from the portal

  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.

    Fail over your failover group containing your database

  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 originally roles.

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.

Elastic pools in Azure SQL Database

Create the failover group and add an elastic pool to it using the Azure portal, or PowerShell.

Prerequisites

Consider the following prerequisites:

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

Create the failover group

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

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.

    Open server for elastic pool

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

    Add 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.

    Add elastic pool to failover group

  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.

Test failover

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

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.

    Open server for elastic pool

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

    Select the failover group from the portal

  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.

    Fail over your failover group containing your SQL database

  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.

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.

SQL Managed Instance

Create a failover group between two managed instances in SQL Managed Instance using the Azure portal or PowerShell.

You will either need to configure ExpressRoute or to create a gateway for the virtual network of each managed instance, connect the two gateways, and then create the failover group.

Prerequisites

Consider the following prerequisites:

  • The secondary managed instance must be empty.
  • The subnet range for the secondary virtual network must not overlap the subnet range of the primary virtual network.
  • The collation and timezone of the secondary managed instance must match that of the primary managed instance.
  • When connecting the two gateways, the Shared Key should be the same for both connections.

Create primary virtual network gateway

If you have not configured ExpressRoute, you can create the primary virtual network gateway with the Azure portal, or PowerShell.

Create the primary virtual network gateway using the Azure portal.

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

  2. Select Subnets under Settings and then select to add a new Gateway subnet. Leave the default values.

    Add gateway for primary managed instance

  3. Once the subnet gateway is created, select Create a resource from the left navigation pane and then type Virtual network gateway in the search box. Select the Virtual network gateway resource published by Microsoft.

    Create a new virtual network gateway

  4. Fill out the required fields to configure the gateway your primary managed instance.

    The following table shows the values necessary for the gateway for the primary managed instance:

    Field Value
    Subscription The subscription where your primary managed instance is.
    Name The name for your virtual network gateway.
    Region The region where your primary managed instance is.
    Gateway type Select VPN.
    VPN Type Select Route-based
    SKU Leave default of VpnGw1.
    Location The location where your secondary managed instance and secondary virtual network is.
    Virtual network Select the virtual network for your secondary managed instance.
    Public IP address Select Create new.
    Public IP address name Enter a name for your IP address.
       
  5. Leave the other values as default, and then select Review + create to review the settings for your virtual network gateway.

    Primary gateway settings

  6. Select Create to create your new virtual network gateway.

Create secondary virtual network gateway

Create the secondary virtual network gateway using the Azure portal or PowerShell.

Repeat the steps in the previous section to create the virtual network subnet and gateway for the secondary managed instance. Fill out the required fields to configure the gateway for your secondary managed instance.

The following table shows the values necessary for the gateway for the secondary managed instance:

Field Value
Subscription The subscription where your secondary managed instance is.
Name The name for your virtual network gateway, such as secondary-mi-gateway.
Region The region where your secondary managed instance is.
Gateway type Select VPN.
VPN Type Select Route-based
SKU Leave default of VpnGw1.
Location The location where your secondary managed instance and secondary virtual network is.
Virtual network Select the virtual network that was created in section 2, such as vnet-sql-mi-secondary.
Public IP address Select Create new.
Public IP address name Enter a name for your IP address, such as secondary-gateway-IP.
   

Secondary gateway settings

Connect the gateways

Create connections between the two gateways using the Azure portal or PowerShell.

Two connections need to be created - the connection from the primary gateway to the secondary gateway, and then the connection from the secondary gateway to the primary gateway.

The shared key used for both connections should be the same for each connection.

Create connections between the two gateways using the Azure portal.

  1. Select Create a resource from the Azure portal.

  2. Type connection in the search box and then press enter to search, which takes you to the Connection resource, published by Microsoft.

  3. Select Create to create your connection.

  4. On the Basics tab, select the following values and then select OK.

    1. Select VNet-to-VNet for the Connection type.
    2. Select your subscription from the drop-down.
    3. Select the resource group for your managed instance in the drop-down.
    4. Select the location of your primary managed instance from the drop-down.
  5. On the Settings tab, select or enter the following values and then select OK:

    1. Choose the primary network gateway for the First virtual network gateway, such as Primary-Gateway.
    2. Choose the secondary network gateway for the Second virtual network gateway, such as Secondary-Gateway.
    3. Select the checkbox next to Establish bidirectional connectivity.
    4. Either leave the default primary connection name, or rename it to a value of your choice.
    5. Provide a Shared key (PSK) for the connection, such as mi1m2psk.

    Create gateway connection

  6. On the Summary tab, review the settings for your bidirectional connection and then select OK to create your connection.

Create the failover group

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

Create the failover group for your managed instances using 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 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 page.

    Add a failover group

  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.

    Create failover group

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

Test failover

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

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. Review which managed instance is the primary, and which managed instance is the secondary.

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

    Fail over the failover group

  4. Review which manged instance is the primary and which instance is the secondary. If failover succeeded, the two instances should have switched roles.

    Managed instances have switched roles after failover

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

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:

Failover group connection string

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.

Next steps

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

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