Tutorial: Add an Azure SQL Database elastic pool to a failover group

APPLIES TO: Azure SQL Database

Configure an auto-failover group for an Azure SQL Database elastic pool and test failover using the Azure portal.

In this tutorial, you'll learn how to:

  • Create a single database.
  • Add the database to an elastic pool.
  • Create a failover group for two elastic pools between two servers.
  • Test failover.

Prerequisites

To complete this tutorial, make sure you have:

1 - Create a single database

In this step, you create a resource group, server, single database, and server-level IP firewall rule for access to the server.

In this step, you create a logical SQL server and a single database that uses AdventureWorksLT sample data. You can create the database by using Azure portal menus and screens, or by using an Azure CLI or PowerShell script in the Azure Cloud Shell.

All the methods include setting up a server-level firewall rule to allow the public IP address of the computer you're using to access the server. For more information about creating server-level firewall rules, see Create a server-level firewall. You can also set database-level firewall rules. See Create a database-level firewall rule.

To create a resource group, server, and single database in the Azure portal:

  1. Sign in to the portal.

  2. From the Search bar, search for and select Azure SQL.

  3. On the Azure SQL page, select Add.

    Screenshot of the Azure portal search screen, showing Azure SQL.

  4. On the Select SQL deployment option page, select the SQL databases tile, with Single database under Resource type. You can view more information about the different databases by selecting Show details.

  5. Select Create.

    Screenshot of the Select SQL deployment option page of the Azure portal.

  6. On the Basics tab of the Create SQL database form, under Project details, select the correct Azure Subscription if it isn't already selected.

  7. Under Resource group, select Create new, enter myResourceGroup, and select OK.

  8. Under Database details, for Database name enter mySampleDatabase.

  9. For Server, select Create new, and fill out the New server form as follows:

    • Server name: Enter mysqlserver, and some characters for uniqueness.
    • Server admin login: Enter AzureAdmin.
    • Password: Enter a password that meets requirements, and enter it again in the Confirm password field.
    • Location: Drop down and choose a location, such as (US) West US.

    Select OK.

    Screenshot of the Create SQL Database Server page of the Azure portal.

    Record the server admin login and password so you can sign in to the server and its databases. If you forget your login or password, you can get the login name or reset the password on the SQL server page after database creation. To open the SQL server page, select the server name on the database Overview page.

  10. Under Compute + storage, if you want to reconfigure the defaults, select Configure database.

    On the Configure page, you can optionally:

    • Change the Compute tier from Provisioned to Serverless.
    • Review and change the settings for vCores and Data max size.
    • Select Change configuration to change hardware configuration.

    After making any changes, select Apply.

  11. Select Next: Networking at the bottom of the page.

    Screenshot of the Create SQL Database page of the Azure portal, showing the basic tab.

  12. On the Networking tab, under Connectivity method, select Public endpoint.

  13. Under Firewall rules, set Add current client IP address to Yes.

  14. Select Next: Additional settings at the bottom of the page.

    Screenshot of the Create SQL Database page of the Azure portal, showing the networking tab.

    For more information about firewall settings, see Allow Azure services and resources to access this server and Add a private endpoint.

  15. On the Additional settings tab, in the Data source section, for Use existing data, select Sample.

  16. Optionally, enable Microsoft Defender for SQL.

  17. Optionally, set the maintenance window so planned maintenance is performed at the best time for your database.

  18. Select Review + create at the bottom of the page.

    Screenshot of the Create SQL Database page of the Azure portal, showing the additional settings tab.

  19. After reviewing settings, select Create.

2 - Add the database to an elastic pool

In this step, you'll create an elastic pool and add your database to it.

Create your elastic pool 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 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 Elastic pool from the Resource type drop-down in the SQL Databases tile. Select Create to create your elastic pool.

    Select elastic pool

  4. Configure your elastic pool with the following values:

    • Name: Provide a unique name for your elastic pool, such as myElasticPool.

    • Subscription: Select your subscription from the drop-down.

    • ResourceGroup: Select myResourceGroup from the drop-down, the resource group you created in section 1.

    • Server: Select the server you created in section 1 from the drop-down.

      Create new server for elastic pool

    • Compute + storage: Select Configure elastic pool to configure your compute, storage, and add your single database to your elastic pool. On the Pool Settings tab, leave the default of Gen5, with 2 vCores and 32gb.

  5. On the Configure page, select the Databases tab, and then choose to Add database. Choose the database you created in section 1 and then select Apply to add it to your elastic pool. Select Apply again to apply your elastic pool settings and close the Configure page.

    Add database to elastic pool

  6. Select Review + create to review your elastic pool settings and then select Create to create your elastic pool.

3 - Create the failover group

In this step, you'll create a failover group between an existing server and a new server in another region. Then add the elastic pool to the failover group.

Create your failover group 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 favorite it and add it as an item in the left-hand navigation.

  2. Select the elastic pool created in the previous section, such as myElasticPool.

  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 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 for your new secondary server, 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 can't 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

  6. Select Databases within the group then select the elastic pool you created in section 2. A warning should appear, 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 the 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.

4 - Test failover

In this step, you'll fail your failover group over to the secondary server, and then fail back using the Azure 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 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 favorite it and add it as an item in the left-hand navigation.

  2. Select the elastic pool created in the previous section, such as myElasticPool.

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

Clean up resources

Clean up resources by deleting the resource group.

  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 script

There are no scripts available for the Azure portal.

Next steps

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

  • Create a single database.
  • Add the database into an elastic pool.
  • Create a failover group for two elastic pools between two servers.
  • Test failover.

Advance to the next tutorial on how to migrate using DMS.