[Azure SQL DB] - How to create and manage your cross subscription Auto-Failover groups (FoG)

[joaoantunes]

Using auto-failover groups is an important feature to build a high resilient architecture, allowing you to manage the replication and failover for several databases across regions, using a common endpoint to communicate to them.

Reference:

Failover group architecture

Configure an auto-failover group - Azure SQL Database | Microsoft Learn

Sometimes we want to separate our production resources and our disaster recovery resources using different Azure subscriptions. In this scenario we need to create a Cross Subscription Failover Group (FoG).

Starting with an important aspect, by the time this article was written, creating and managing a cross subscription FoG using Azure Portal is not supported. The only supported operation is the failover, all the remaining operations can’t be done using Azure Portal.

So how many ways do we have to create a cross subscription FoG? 3 ways!

1. Using Powershell
2. Using cloud Shell
3. Using ARM template

Lets learn how to use each option

Creating a cross subscription FoG

Using Powershell

To use powershell 5.1 we firstly need to install the az.sql 3.11.0

If we don’t have the az.sql 3.11.0 installed, when sing the parameter -PartnerSubscriptionId you will get an error.

To check the az.sql modules installed, run the command “Get-InstalledModule Az.sql” on your Powershell window

To use Powershell 7 we need to install the az.sql module

Install-Module -Name Az -Scope CurrentUser -Repository PSGallery -Force

Reference: Migrating from Windows PowerShell 5.1 to PowerShell 7

After the steps above, we can run the following script to create our cross subscription FoG

$sub2 = ‘SecondarySubscriptionID’ $failoverGroup = New-AzSqlDatabaseFailoverGroup -ServerName <YourPrimaryServerName> -FailoverGroupName <YourFoGName> -PartnerSubscriptionId $sub2 -PartnerResourceGroupName <PartnerResourceGroup> -PartnerServerName <PartnerServerName> -FailoverPolicy Manual -ResourceGroupName <PrimaryResourceGroup> 

And the cross subscription FoG will be created successfully.

Now you can add your databases to your failover group (check the section on this article: Adding database to a FoG cross subscriptions)

Using Cloud Shell

Using Azure Portal Cloud Shell or the Azure Cloud Shell on this link New-AzSqlDatabaseFailoverGroup (Az.Sql) | Microsoft Learn will allow you to create the cross subscription FoG without any effort or issue.

Simply using the previous script directly on Cloud Shell

$sub2 = ‘SecondarySubscriptionID’ $failoverGroup = New-AzSqlDatabaseFailoverGroup -ServerName <YourPrimaryServerName> -FailoverGroupName <YourFoGName> -PartnerSubscriptionId $sub2 -PartnerResourceGroupName <PartnerResourceGroup> -PartnerServerName <PartnerServerName> -FailoverPolicy Manual -ResourceGroupName <PrimaryResourceGroup>

Now you can add your databases to your failover group (check the section on this article: Adding database to a FoG cross subscriptions)

Using ARM Template

To create a cross subscription FoG using ARM template, is rather simple as well.

You just need to edit the below script and run it, as you run all your ARM templates.

{
"$schema": https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#,
  "contentVersion": "1.0.0.0",
      "parameters": {},
    "variables": {},
  "resources": [
    {
          "name": "<primaryservername>/<FoGname>",
      "type": "Microsoft.Sql/servers/failoverGroups",
      "apiVersion": "2015-05-01-preview",
      "properties": {
        "readWriteEndpoint": {
          "failoverPolicy": "Automatic",
          "failoverWithDataLossGracePeriodMinutes": 60
        },
        "readOnlyEndpoint": {
          "failoverPolicy": "Disabled"
        },
        "partnerServers": [
          {
            "id": "/subscriptions/<SubscriptionID>/resourceGroups/<YourResourceGroup>/providers/Microsoft.Sql/servers/<YourServerName>", //secondary
                        "resourceType": "Microsoft.Sql/servers",
            "resourceName": "<YourResourceGroup>"
          }
        ],
           "databases": [/subscriptions/<SubscriptionID>/resourceGroups/<YourResourceGroup>/providers/Microsoft.Sql/servers/<YourServerName>/databases/<yourdatabase>"] //primary
      },
      }
          ]
     }

Adding database to a FoG cross subscription

Using exclusively PowerShell to manage Cross Subscription FoG (creating, adding and removing databases) will keep the FoG in a healthy state

• Performing this operation using Azure portal, will raise you an error
• Using PowerShell or cloud shell you will be able to add new databases to the FoG cross subscription without any error

Add-AzSqlDatabaseToFailoverGroup (Az.Sql) | Microsoft Learn

Adding database to FOG

==============================

1. Run the following command on PowerShell or Cloud Shell

Get-AzSqlDatabase -ResourceGroupName <PrimaryResourceGroup> -ServerName <YourPrimaryServerName>  -DatabaseName <databasenametoadd> | Add-AzSqlDatabaseToFailoverGroup -ResourceGroupName <PrimaryResourceGroup> -ServerName <YourPrimaryServerName>  -FailoverGroupName <FoGName>

2. Using Azure Portal you will be able to see the database there

Failovers

Failovers (using Azure portal or PS) will be performed without any issue or error.

Enjoy!