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

Article
06/08/2022
28 minutes to read

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:

An Azure subscription. Create a free account if you don't already have one.

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:

Sign in to the portal.
From the Search bar, search for and select Azure SQL.
On the Azure SQL page, select Add.
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.
Select Create.
On the Basics tab of the Create SQL database form, under Project details, select the correct Azure Subscription if it isn't already selected.
Under Resource group, select Create new, enter myResourceGroup, and select OK.
Under Database details, for Database name enter mySampleDatabase.
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.

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.
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.
Select Next: Networking at the bottom of the page.
On the Networking tab, under Connectivity method, select Public endpoint.
Under Firewall rules, set Add current client IP address to Yes.
Select Next: Additional settings at the bottom of the page.

For more information about firewall settings, see Allow Azure services and resources to access this server and Add a private endpoint.
On the Additional settings tab, in the Data source section, for Use existing data, select Sample.
Optionally, enable Microsoft Defender for SQL.
Optionally, set the maintenance window so planned maintenance is performed at the best time for your database.
Select Review + create at the bottom of the page.
After reviewing settings, select Create.

You can create a resource group, server, and single database using Windows PowerShell. If you don't want to use the Azure Cloud Shell, install the Azure PowerShell module.

Note

This article uses the Azure Az PowerShell module, which is the recommended PowerShell module for interacting with Azure. To get started with the Az PowerShell module, see Install Azure PowerShell. To learn how to migrate to the Az PowerShell module, see Migrate Azure PowerShell from AzureRM to Az.

To run the following code sample in the Azure Cloud Shell, select Try it in the code title bar. When the Cloud Shell opens, select Copy in the code sample title bar, and paste the code sample into the Cloud Shell window. In the code, replace <Subscription ID> with your Azure Subscription ID, and for $startIp and $endIp, replace 0.0.0.0 with the public IP address of the computer you're using.

Follow the onscreen prompts to sign in to Azure and run the code.

You can also use Azure Cloud Shell from the Azure portal, by selecting the Cloud Shell icon on the top bar.

Screenshot of the Azure Cloud Shell location in the Azure portal.

The first time you use Cloud Shell from the portal, select PowerShell on the Welcome dialog. Subsequent sessions will use PowerShell, or you can select it from the Cloud Shell control bar.

The following PowerShell code creates an Azure resource group, server, single database, and firewall rule for access to the server. Make sure to record the generated resource group and server names, so you can manage these resources later.

# Set variables for your server and database
$subscriptionId = '<SubscriptionID>'
$resourceGroupName = "myResourceGroup-$(Get-Random)"
$location = "West US"
$adminLogin = "azureuser"
$password = "Azure1234567"
$serverName = "mysqlserver-$(Get-Random)"
$databaseName = "mySampleDatabase"

# The ip address range that you want to allow to access your server
$startIp = "0.0.0.0"
$endIp = "0.0.0.0"

# Show randomized variables
Write-host "Resource group name is" $resourceGroupName
Write-host "Server name is" $serverName

# Connect to Azure
Connect-AzAccount

# Set subscription ID
Set-AzContext -SubscriptionId $subscriptionId

# Create a resource group
Write-host "Creating resource group..."
$resourceGroup = New-AzResourceGroup -Name $resourceGroupName -Location $location -Tag @{Owner="SQLDB-Samples"}
$resourceGroup

# Create a server with a system wide unique server name
Write-host "Creating primary server..."
$server = New-AzSqlServer -ResourceGroupName $resourceGroupName `
   -ServerName $serverName `
   -Location $location `
   -SqlAdministratorCredentials $(New-Object -TypeName System.Management.Automation.PSCredential `
   -ArgumentList $adminLogin, $(ConvertTo-SecureString -String $password -AsPlainText -Force))
$server

# Create a server firewall rule that allows access from the specified IP range
Write-host "Configuring firewall for primary server..."
$serverFirewallRule = New-AzSqlServerFirewallRule -ResourceGroupName $resourceGroupName `
   -ServerName $serverName `
   -FirewallRuleName "AllowedIPs" -StartIpAddress $startIp -EndIpAddress $endIp
$serverFirewallRule

# Create General Purpose Gen5 database with 2 vCores
Write-host "Creating a gen5 2 vCore database..."
$database = New-AzSqlDatabase  -ResourceGroupName $resourceGroupName `
   -ServerName $serverName `
   -DatabaseName $databaseName `
   -Edition GeneralPurpose `
   -VCore 2 `
   -ComputeGeneration Gen5 `
   -MinimumCapacity 2 `
   -SampleName "AdventureWorksLT"
$database

The preceding code uses these PowerShell cmdlets:

Command	Notes
New-AzResourceGroup	Creates a resource group in which all resources are stored.
New-AzSqlServer	Creates a server that hosts databases and elastic pools.
New-AzSqlServerFirewallRule	Creates a server-level firewall rule for a server.
New-AzSqlDatabase	Creates a database.

For more Azure SQL Database PowerShell samples, see Azure PowerShell samples.

The following Azure CLI code blocks create a resource group, server, single database, and server-level IP firewall rule for access to the server. Make sure to record the generated resource group and server names, so you can manage these resources later.

If you don't have an Azure subscription, create an Azure free account before you begin.

Prepare your environment for the Azure CLI

Use the Bash environment in Azure Cloud Shell. For more information, see Azure Cloud Shell Quickstart - Bash.
If you prefer to run CLI reference commands locally, install the Azure CLI. If you are running on Windows or macOS, consider running Azure CLI in a Docker container. For more information, see How to run the Azure CLI in a Docker container.
- If you're using a local installation, sign in to the Azure CLI by using the az login command. To finish the authentication process, follow the steps displayed in your terminal. For additional sign-in options, see Sign in with the Azure CLI.
- When you're prompted, install Azure CLI extensions on first use. For more information about extensions, see Use extensions with the Azure CLI.
- Run az version to find the version and dependent libraries that are installed. To upgrade to the latest version, run az upgrade.

Launch Azure Cloud Shell

The Azure Cloud Shell is a free interactive shell that you can use to run the steps in this article. It has common Azure tools preinstalled and configured to use with your account.

To open the Cloud Shell, just select Try it from the upper right corner of a code block. You can also launch Cloud Shell in a separate browser tab by going to https://shell.azure.com.

When Cloud Shell opens, verify that Bash is selected for your environment. Subsequent sessions will use Azure CLI in a Bash environment, Select Copy to copy the blocks of code, paste it into the Cloud Shell, and press Enter to run it.

Cloud Shell is automatically authenticated under the initial account signed-in with. Use the following script to sign in using a different subscription, replacing <Subscription ID> with your Azure Subscription ID. If you don't have an Azure subscription, create an Azure free account before you begin.

subscription="<subscriptionId>" # add subscription here

az account set -s $subscription # ...or use 'az login'

For more information, see set active subscription or log in interactively

Set parameter values to create resources

Set the parameter values for use in creating the database and required resources. Server names need to be globally unique across all of Azure so the $RANDOM function is used to create the server name.

Change the location as appropriate for your environment. Replace 0.0.0.0 with the IP address range to match your specific environment. Use the public IP address of the computer you're using to restrict access to the server to only your IP address.

# Variable block
let "randomIdentifier=$RANDOM*$RANDOM"
location="East US"
resourceGroup="msdocs-azuresql-rg-$randomIdentifier"
tag="create-and-configure-database"
server="msdocs-azuresql-server-$randomIdentifier"
database="msdocsazuresqldb$randomIdentifier"
login="azureuser"
password="Pa$$w0rD-$randomIdentifier"
# Specify appropriate IP address values for your environment
# to limit access to the SQL Database server
startIp=0.0.0.0
endIp=0.0.0.0

echo "Using resource group $resourceGroup with login: $login, password: $password..."

Create a resource group

Use this script to create a resource group with the az group create command. An Azure resource group is a logical container into which Azure resources are deployed and managed.

echo "Creating $resourceGroup in $location..."
az group create --name $resourceGroup --location "$location" --tags $tag

Create a server

Use this script to create a server with the az sql server create command.

echo "Creating $server in $location..."
az sql server create --name $server --resource-group $resourceGroup --location "$location" --admin-user $login --admin-password $password

Configure a server-based firewall rule

Use this script to create a firewall rule with the az sql server firewall-rule create command.

echo "Configuring firewall..."
az sql server firewall-rule create --resource-group $resourceGroup --server $server -n AllowYourIp --start-ip-address $startIp --end-ip-address $endIp

Create a single database using Azure CLI

Use this script to create a database with the az sql db create command.

echo "Creating $database on $server..."
az sql db create --resource-group $resourceGroup --server $server --name $database --sample-name AdventureWorksLT --edition GeneralPurpose --family Gen5 --capacity 2 --zone-redundant true # zone redundancy is only supported on premium and business critical service tiers

Azure CLI command reference

The preceding code uses these Azure CLI commands:

Command	Description
az account set	Sets a subscription to be the current active subscription.
az group create	Creates a resource group in which all resources are stored.
az sql server create	Creates a server that hosts databases and elastic pools.
az sql server firewall-rule create	Creates a server-level firewall rule.
az sql db create	Creates a database.

For additional Azure SQL Database Azure CLI scripts, see Azure CLI samples.

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.

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.
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.
Select Elastic pool from the Resource type drop-down in the SQL Databases tile. Select Create to create your elastic pool.
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.
- 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.
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.
Select Review + create to review your elastic pool settings and then select Create to create your elastic pool.

Create your elastic pools and secondary server using PowerShell.

# Set variables for your server and database
# $subscriptionId = '<SubscriptionID>'
# $resourceGroupName = "myResourceGroup-$(Get-Random)"
# $location = "East US"
# $adminLogin = "azureuser"
# $password = "PWD27!"+(New-Guid).Guid
# $serverName = "mysqlserver-$(Get-Random)"
$poolName = "myElasticPool"
$databaseName = "mySampleDatabase"
$drLocation = "West US"
$drServerName = "mysqlsecondary-$(Get-Random)"
$failoverGroupName = "failovergrouptutorial-$(Get-Random)"

# The ip address range that you want to allow to access your server
# Leaving at 0.0.0.0 will prevent outside-of-azure connections
# $startIp = "0.0.0.0"
# $endIp = "0.0.0.0"

# Show randomized variables
Write-host "DR Server name is" $drServerName
Write-host "Failover group name is" $failoverGroupName

# Create primary Gen5 elastic 2 vCore pool
Write-host "Creating elastic pool..."
$elasticPool = New-AzSqlElasticPool -ResourceGroupName $resourceGroupName `
    -ServerName $serverName `
    -ElasticPoolName $poolName `
    -Edition "GeneralPurpose" `
    -vCore 2 `
    -ComputeGeneration Gen5
$elasticPool

# Add single db into elastic pool
Write-host "Creating elastic pool..."
$addDatabase = Set-AzSqlDatabase -ResourceGroupName $resourceGroupName `
    -ServerName $serverName `
    -DatabaseName $databaseName `
    -ElasticPoolName $poolName
$addDatabase

This portion of the tutorial uses the following PowerShell cmdlets:

Command	Notes
New-AzSqlElasticPool	Creates an elastic database pool for an Azure SQL Database.
Set-AzSqlDatabase	Sets properties for a database, or moves an existing database into an elastic pool.

In this step, you create your elastic pool and add your database to the elastic pool using the Azure CLI.

Set additional parameter values to create elastic pool

Set these additional parameter values for use in creating the elastic pool.

pool="msdocs-azuresql-pool-$randomIdentifier"

Create elastic pool on primary server

Use the az sql elastic-pool create command to create an elastic pool.

echo "Creating $pool on $server..."
az sql elastic-pool create --name $pool --resource-group $resourceGroup --server $server

Add database to elastic pool

Use the az sql db update command to add a database to an elastic pool.

echo "Adding $database to $pool..."
az sql db update --elastic-pool $pool --name $database --resource-group $resourceGroup --server $server

This portion of the tutorial uses the following Azure CLI cmdlets:

Command	Notes
az sql elastic-pool create	Creates an elastic pool.
az sql db update	Updates a database

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.

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.
Select the elastic pool created in the previous section, such as myElasticPool.
On the Overview pane, select the name of the server under Server name to open the settings for the server.
Select Failover groups under the Settings pane, and then select Add group to create a new failover group.
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.
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.
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.

Create your failover group using PowerShell.

# Set variables for your server and database
# $subscriptionId = '<SubscriptionID>'
# $resourceGroupName = "myResourceGroup-$(Get-Random)"
# $location = "East US"
# $adminLogin = "azureuser"
# $password = "PWD27!"+(New-Guid).Guid
# $serverName = "mysqlserver-$(Get-Random)"
# $poolName = "myElasticPool"
# $databaseName = "mySampleDatabase"
# $drLocation = "West US"
# $drServerName = "mysqlsecondary-$(Get-Random)"
$failoverGroupName = "failovergrouptutorial-$(Get-Random)"

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

# Create a server firewall rule that allows access from the specified IP range
Write-host "Configuring firewall for secondary server..."
New-AzSqlServerFirewallRule -ResourceGroupName $resourceGroupName `
   -ServerName $drServerName `
   -FirewallRuleName "AllowedIPs" -StartIpAddress $startIp -EndIpAddress $endIp
Write-host "Firewall configured"

# Create secondary Gen5 elastic 2 vCore pool
Write-host "Creating secondary elastic pool..."
$elasticPool = New-AzSqlElasticPool -ResourceGroupName $resourceGroupName `
    -ServerName $drServerName `
    -ElasticPoolName $poolName `
    -Edition "GeneralPurpose" `
    -vCore 2 `
    -ComputeGeneration Gen5
$elasticPool

# 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
$failoverGroup

This portion of the tutorial uses the following PowerShell cmdlets:

Command	Notes
New-AzSqlServer	Creates a server that hosts databases and elastic pools.
New-AzSqlServerFirewallRule	Creates a firewall rule for a server.
New-AzSqlElasticPool	Creates an elastic pool for an Azure SQL Database.
New-AzSqlDatabaseFailoverGroup	Creates a new failover group.
Add-AzSqlDatabaseToFailoverGroup	Adds one or more Azure SQL databases to a failover group.
Get-AzSqlDatabaseFailoverGroup	Gets or lists Azure SQL Database failover groups.

In this step, you use the Azure CLI to create your secondary server, failover group, elastic pool, and add a database to the failover group.

Set additional parameter values to create failover group

Set these additional parameter values for use in creating the failover group.

Change the failover location as appropriate for your environment.

failoverGroup="msdocs-azuresql-failover-group-$randomIdentifier"
failoverLocation="Central US"
secondaryServer="msdocs-azuresql-secondary-server-$randomIdentifier"

Create secondary server

Use the az sql server create command to create a secondary server.

Note

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

echo "Creating $secondaryServer in $failoverLocation..."
az sql server create --name $secondaryServer --resource-group $resourceGroup --location "$failoverLocation"  --admin-user $login --admin-password $password

Create elastic pool on secondary server

Use the az sql elastic-pool create command to create an elastic pool on the secondary server.

echo "Creating $pool on $secondaryServer..."
az sql elastic-pool create --name $pool --resource-group $resourceGroup --server $secondaryServer

Create failover group

Use the az sql failover-group create command to create a failover group.

echo "Creating $failoverGroup between $server and $secondaryServer..."
az sql failover-group create --name $failoverGroup --partner-server $secondaryServer --resource-group $resourceGroup --server $server --failover-policy Automatic --grace-period 2
databaseId=$(az sql elastic-pool list-dbs --name $pool --resource-group $resourceGroup --server $server --query [0].name -o json | tr -d '"')

Add database to the failover group

Use the az sql failover-group update command to add a database to the failover group.

echo "Adding $database to $failoverGroup..."
az sql failover-group update --name $failoverGroup --add-db $databaseId --resource-group $resourceGroup --server $server

Azure CLI failover group creation reference

This portion of the tutorial uses the following Azure CLI cmdlets:

Command	Notes
az sql server create	Creates a server that hosts databases and elastic pools.
az sql elastic-pool create	Creates an elastic pool.
az sql failover-group create	Creates a failover group.
az sql failover-group update	Updates a failover group.

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.

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.
Select the elastic pool created in the previous section, such as myElasticPool.
Select the name of the server under Server name to open the settings for the server.
Select Failover groups under the Settings pane and then choose the failover group you created in section 2.
Review which server is primary, and which server is secondary.
Select Failover from the task pane to fail over your failover group containing your elastic pool.
Select Yes on the warning that notifies you that TDS sessions will be disconnected.
Review which server is primary, which server is secondary. If failover succeeded, the two servers should have swapped roles.
Select Failover again to fail the failover group back to the original settings.

Test failover of your failover group using PowerShell.

# Set variables for your server and database
# $subscriptionId = '<SubscriptionID>'
# $resourceGroupName = "myResourceGroup-$(Get-Random)"
# $location = "East US"
# $adminLogin = "azureuser"
# $password = "PWD27!"+(New-Guid).Guid
# $serverName = "mysqlserver-$(Get-Random)"
# $poolName = "myElasticPool"
# $databaseName = "mySampleDatabase"
# $drLocation = "West US"
# $drServerName = "mysqlsecondary-$(Get-Random)"
# $failoverGroupName = "failovergrouptutorial-$(Get-Random)"

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

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

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

# Set variables for your server and database
# $subscriptionId = '<SubscriptionID>'
# $resourceGroupName = "myResourceGroup-$(Get-Random)"
# $location = "East US"
# $adminLogin = "azureuser"
# $password = "PWD27!"+(New-Guid).Guid
# $serverName = "mysqlserver-$(Get-Random)"
# $poolName = "myElasticPool"
# $databaseName = "mySampleDatabase"
# $drLocation = "West US"
# $drServerName = "mysqlsecondary-$(Get-Random)"
# $failoverGroupName = "failovergrouptutorial-$(Get-Random)"

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

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

This portion of the tutorial uses the following PowerShell cmdlets:

Command	Notes
Get-AzSqlDatabaseFailoverGroup	Gets or lists Azure SQL Database failover groups.
Switch-AzSqlDatabaseFailoverGroup	Executes a failover of an Azure SQL Database failover group.

Test failover using the Azure CLI.

Verify the roles of each server

Use the az sql failover-group show command to confirm the roles of each server in the failover group.

echo "Confirming the role of each server in the failover group..." # note ReplicationRole property
az sql failover-group show --name $failoverGroup --resource-group $resourceGroup --server $server

Fail over to the secondary server

Use the az sql failover-group set-primary command to fail over to the secondary server. Use the az sql failover-group show command to verify a successful failover.

echo "Failing over to $secondaryServer..."
az sql failover-group set-primary --name $failoverGroup --resource-group $resourceGroup --server $secondaryServer 

echo "Confirming role of $secondaryServer is now primary..." # note ReplicationRole property
az sql failover-group show --name $failoverGroup --resource-group $resourceGroup --server $server

Revert failover group back to the primary server

Use the az sql failover-group set-primary command to fail back to the primary server.

echo "Failing back to $server...."
az sql failover-group set-primary --name $failoverGroup --resource-group $resourceGroup --server $server

Azure CLI failover group management reference

This portion of the tutorial uses the following Azure CLI cmdlets:

Command	Notes
az sql failover-group show	Gets the failover groups in a server.
az sql failover-group set-primary	Set the primary of the failover group by failing over all databases from the current primary server.

Clean up resources

Clean up resources by deleting the resource group.

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

Clean up your resources using PowerShell.

# Set variables for your server and database
# $resourceGroupName = "myResourceGroup-$(Get-Random)"

# Clean up resources by removing the resource group
Write-host "Removing resource group..."
Remove-AzResourceGroup -ResourceGroupName $resourceGroupName
Write-host "Resource group removed =" $resourceGroupName

This portion of the tutorial uses the following PowerShell cmdlet:

Command	Notes
Remove-AzResourceGroup	Removes a resource group

Use the following command to remove the resource group and all resources associated with it using the az group delete command - unless you have an ongoing need for these resources. Some of these resources may take a while to create, as well as to delete.

echo "Cleaning up resources by removing the resource group..."
az group delete --name $resourceGroup -y

This portion of the tutorial uses the following Azure CLI cmdlets:

Command	Notes
az group delete	Deletes a resource group including all nested resources.

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


# Set variables for your server and database
$subscriptionId = '<Subscription-ID>'
$randomIdentifier = $(Get-Random)
$resourceGroupName = "myResourceGroup-$randomIdentifier"
$location = "East US"
$adminLogin = "azureuser"
$password = "PWD27!"+(New-Guid).Guid
$serverName = "mysqlserver-$randomIdentifier"
$poolName = "myElasticPool"
$databaseName = "mySampleDatabase"
$drLocation = "West US"
$drServerName = "mysqlsecondary-$randomIdentifier"
$failoverGroupName = "failovergrouptutorial-$randomIdentifier"


# The ip address range that you want to allow to access your server 
# Leaving at 0.0.0.0 will prevent outside-of-azure connections
$startIp = "0.0.0.0"
$endIp = "0.0.0.0"

# Show randomized variables
Write-host "Resource group name is" $resourceGroupName 
Write-host "Password is" $password  
Write-host "Server name is" $serverName 
Write-host "DR Server name is" $drServerName 
Write-host "Failover group name is" $failoverGroupName


# Set subscription ID
Set-AzContext -SubscriptionId $subscriptionId 

# Create a resource group
Write-host "Creating resource group..."
$resourceGroup = New-AzResourceGroup -Name $resourceGroupName -Location $location -Tag @{Owner="SQLDB-Samples"}
$resourceGroup

# Create a server with a system-wide unique server name
Write-host "Creating primary logical server..."
New-AzSqlServer -ResourceGroupName $resourceGroupName `
   -ServerName $serverName `
   -Location $location `
   -SqlAdministratorCredentials $(New-Object -TypeName System.Management.Automation.PSCredential `
   -ArgumentList $adminLogin, $(ConvertTo-SecureString -String $password -AsPlainText -Force))
Write-host "Primary logical server = " $serverName

# Create a server firewall rule that allows access from the specified IP range
Write-host "Configuring firewall for primary logical server..."
New-AzSqlServerFirewallRule -ResourceGroupName $resourceGroupName `
   -ServerName $serverName `
   -FirewallRuleName "AllowedIPs" -StartIpAddress $startIp -EndIpAddress $endIp
Write-host "Firewall configured" 

# Create General Purpose Gen5 database with 2 vCore
Write-host "Creating a gen5 2 vCore database..."
$database = New-AzSqlDatabase  -ResourceGroupName $resourceGroupName `
   -ServerName $serverName `
   -DatabaseName $databaseName `
   -Edition "GeneralPurpose" `
   -VCore 2 `
   -ComputeGeneration Gen5 `
   -MinimumCapacity 1 `
   -SampleName "AdventureWorksLT"
$database

# Create primary Gen5 elastic 2 vCore pool
Write-host "Creating elastic pool..."
$elasticPool = New-AzSqlElasticPool -ResourceGroupName $resourceGroupName `
    -ServerName $serverName `
    -ElasticPoolName $poolName `
    -Edition "GeneralPurpose" `
    -vCore 2 `
    -ComputeGeneration Gen5
$elasticPool

# Add single db into elastic pool
Write-host "Creating elastic pool..."
$addDatabase = Set-AzSqlDatabase -ResourceGroupName $resourceGroupName `
    -ServerName $serverName `
    -DatabaseName $databaseName `
    -ElasticPoolName $poolName
$addDatabase

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

# Create a server firewall rule that allows access from the specified IP range
Write-host "Configuring firewall for secondary logical server..."
New-AzSqlServerFirewallRule -ResourceGroupName $resourceGroupName `
   -ServerName $drServerName `
   -FirewallRuleName "AllowedIPs" -StartIpAddress $startIp -EndIpAddress $endIp
Write-host "Firewall configured" 

# Create secondary Gen5 elastic 2 vCore pool
Write-host "Creating secondary elastic pool..."
$elasticPool = New-AzSqlElasticPool -ResourceGroupName $resourceGroupName `
    -ServerName $drServerName `
    -ElasticPoolName $poolName `
    -Edition "GeneralPurpose" `
    -vCore 2 `
    -ComputeGeneration Gen5
$elasticPool


# 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 
$failoverGroup

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

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

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

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

# Clean up resources by removing the resource group
# Write-host "Removing resource group..."
# Remove-AzResourceGroup -ResourceGroupName $resourceGroupName
# Write-host "Resource group removed =" $resourceGroupName

This script uses the following commands. Each command in the table links to command specific documentation.

Command	Notes
New-AzResourceGroup	Creates a resource group in which all resources are stored.
New-AzSqlServer	Creates a server that hosts databases and elastic pools.
New-AzSqlServerFirewallRule	Creates a firewall rule for a server.
New-AzSqlDatabase	Creates a database.
New-AzSqlElasticPool	Creates an elastic database pool for an Azure SQL Database.
Set-AzSqlDatabase	Sets properties for a database, or moves an existing database into an elastic pool.
New-AzSqlDatabaseFailoverGroup	Creates a new failover group.
Get-AzSqlDatabase	Gets one or more databases in SQL Database.
Add-AzSqlDatabaseToFailoverGroup	Adds one or more Azure SQL databases to a failover group.
Get-AzSqlDatabaseFailoverGroup	Gets or lists Azure SQL Database failover groups.
Switch-AzSqlDatabaseFailoverGroup	Executes a failover of an Azure SQL Database failover group.
Remove-AzResourceGroup	Removes a resource group

# Add an Azure SQL Database elastic pool to a failover group
# Variable block
let "randomIdentifier=$RANDOM*$RANDOM"
location="East US"
resourceGroup="msdocs-azuresql-rg-$randomIdentifier"
tag="add-elastic-pool-to-failover-group-az-cli"
server="msdocs-azuresql-server-$randomIdentifier"
database="msdocsazuresqldb$randomIdentifier"
login="azureuser"
password="Pa$$w0rD-$randomIdentifier"
pool="msdocs-azuresql-pool-$randomIdentifier"
failoverGroup="msdocs-azuresql-failover-group-$randomIdentifier"
failoverLocation="Central US"
secondaryServer="msdocs-azuresql-secondary-server-$randomIdentifier"
echo "Using resource group $resourceGroup with login: $login, password: $password..."

echo "Creating $resourceGroup in $location..."
az group create --name $resourceGroup --location "$location" --tags $tag

echo "Creating $server in $location..."
az sql server create --name $server --resource-group $resourceGroup --location "$location"  --admin-user $login --admin-password $password

echo "Creating $database on $server..."
az sql db create --name $database --resource-group $resourceGroup --server $server --sample-name AdventureWorksLT

echo "Creating $pool on $server..."
az sql elastic-pool create --name $pool --resource-group $resourceGroup --server $server
echo "Adding $database to $pool..."
az sql db update --elastic-pool $pool --name $database --resource-group $resourceGroup --server $server
echo "Creating $secondaryServer in $failoverLocation..."
az sql server create --name $secondaryServer --resource-group $resourceGroup --location "$failoverLocation"  --admin-user $login --admin-password $password
echo "Creating $pool on $secondaryServer..."
az sql elastic-pool create --name $pool --resource-group $resourceGroup --server $secondaryServer
echo "Creating $failoverGroup between $server and $secondaryServer..."
az sql failover-group create --name $failoverGroup --partner-server $secondaryServer --resource-group $resourceGroup --server $server --failover-policy Automatic --grace-period 2
databaseId=$(az sql elastic-pool list-dbs --name $pool --resource-group $resourceGroup --server $server --query [0].name -o json | tr -d '"')
echo "Adding $database to $failoverGroup..."
az sql failover-group update --name $failoverGroup --add-db $databaseId --resource-group $resourceGroup --server $server
echo "Confirming the role of each server in the failover group..." # note ReplicationRole property
az sql failover-group show --name $failoverGroup --resource-group $resourceGroup --server $server
echo "Failing over to $secondaryServer..."
az sql failover-group set-primary --name $failoverGroup --resource-group $resourceGroup --server $secondaryServer 

echo "Confirming role of $secondaryServer is now primary..." # note ReplicationRole property
az sql failover-group show --name $failoverGroup --resource-group $resourceGroup --server $server
echo "Failing back to $server...."
az sql failover-group set-primary --name $failoverGroup --resource-group $resourceGroup --server $server

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.

Tutorial: Migrate SQL Server to a pooled database using DMS

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

Prerequisites

1 - Create a single database

2 - Add the database to an elastic pool

3 - Create the failover group

4 - Test failover

Clean up resources

Full script

Next steps

Feedback