Tutorial: Add an Azure SQL Database single database to a failover group

Configure a failover group for an Azure SQL Database single database and test failover using either the Azure portal, PowerShell, or Azure CLI. In this tutorial, you will learn how to:

  • Create an Azure SQL Database single database.
  • Create a failover group for a single database between two logical SQL servers.
  • Test failover.

Prerequisites

To complete this tutorial, make sure you have:

1 - Create a single database

In this step, you will create an Azure SQL Database single database.

Important

Be sure to set up firewall rules to use the public IP address of the computer you're using to complete this article.

For information, see Create a database-level firewall rule or to determine the IP address used for the server-level firewall rule for your computer see Create a server-level firewall.

Create your resource group and single database 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 + Add to open the Select SQL deployment option page. You can view additional information about the different databases by selecting Show details on the Databases tile.

  3. Select Create:

    Create single database

  4. On the Basics tab, in the Project Details section, type or select the following values:

    • Subscription: Drop down and select the correct subscription, if it doesn't appear.

    • Resource group: Select Create new, type myResourceGroup, and select OK.

      New SQL database - basic tab

  5. In the Database Details section, type or select the following values:

    • Database name: Enter mySampleDatabase.

    • Server: Select Create new, enter the following values and then select Select.

      • Server name: Type mysqlserver; along with some numbers for uniqueness.

      • Server admin login: Type azureuser.

      • Password: Type a complex password that meets password requirements.

      • Location: Choose a location from the drop-down, such as West US.

        New server

      Important

      Remember to record the server admin login and password so you can log in to the server and databases for this and other quickstarts. If you forget your login or password, you can get the login name or reset the password on the SQL server page. To open the SQL server page, select the server name on the database Overview page after database creation.

    • Want to use SQL elastic pool: Select the No option.

    • Compute + storage: Select Configure database.

      SQL Database details

    • Select Provisioned.

      Provisioned Gen4

    • Review the settings for vCores, and Data max size. Change these as desired.

      • Optionally, you can also select Change configuration to change the hardware generation.
    • Select Apply.

  6. Select the Additional settings tab.

  7. In the Data source section, under Use existing data, select Sample.

    Additional SQL DB settings

    Important

    Make sure to select the Sample (AdventureWorksLT) data so you can follow easily this and other Azure SQL Database quickstarts that use this data.

  8. Leave the rest of the values as default and select Review + Create at the bottom of the form.

  9. Review the final settings and select Create.

  10. On the SQL Database form, select Create to deploy and provision the resource group, server, and database.

2 - Create the failover group

In this step, you will create a failover group between an existing Azure SQL server and a new Azure SQL server in another region. Then add the sample database to the 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 single database created in section 1, such as mySampleDatabase.

  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 following values, and then select Create:

    • Failover group name: Type in a unique failover group name, such as failovergrouptutorial.
    • Secondary server: Select the option to configure required settings and then choose to Create a new server. Alternatively, you can choose an already-existing server as the secondary server. After entering the following values, select Select.
      • Server name: Type in a unique name for the secondary server, such as mysqlsecondary.
      • Server admin login: Type azureuser
      • Password: Type a complex password that meets password requirements.
      • Location: Choose a location from the drop-down, such as East US. This location cannot be the same location as your primary server.

    Note

    The server login and firewall settings must match that of your primary server.

    Create a secondary server for the failover group

    • Databases within the group: Once a secondary server is selected, this option becomes unlocked. Select it to Select databases to add and then choose the database you created in section 1. Adding the database to the failover group will automatically start the geo-replication process.

    Add SQL DB to failover group

3 - Test failover

In this step, you will fail your failover group over to the secondary server, and then fail back using the Azure portal.

Test failover 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 single database created in the section 2, such as mySampleDatbase.

  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 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 failover your failover group containing your sample single database.

  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 now primary and which server is secondary. If fail over succeeded, the two servers should have swapped roles.

  9. Select Failover again to fail the servers back to their originally roles.

Clean up resources

Clean up resources by deleting the resource group.

Delete the resource group using the Azure portal.

  1. Navigate to your resource group in the Azure portal.
  2. Select Delete resource group to delete all the resources in the group, as well as the resource group itself.
  3. Type the name of the resource group, myResourceGroup, in the textbox, and then select Delete to delete the resource group.

Important

If you want to keep the resource group but 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.

Full scripts

There are no scripts available for the Azure portal.

You can find other Azure SQL Database scripts here: Azure PowerShell and Azure CLI.

Next steps

In this tutorial, you added an Azure SQL Database single database to a failover group, and tested failover. You learned how to:

  • Create an Azure SQL Database single database.
  • Create a failover group for a single database between two logical SQL servers.
  • Test failover.

Advance to the next tutorial on how to add your elastic pool to a failover group.