This post has been republished via RSS; it originally appeared at: Azure Database Support Blog articles.

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 might 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).

 

Note that 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>", //Primary "resourceType": "Microsoft.Sql/servers", "resourceName": "<YourResourceGroup>" } ], "databases": [/subscriptions/<SubscriptionID>/resourceGroups/<YourResourceGroup>/providers/Microsoft.Sql/servers/<YourServerName>"] //Secondary }, } ] }

 

 

 

 

 

 

 

 

 

 

Adding a 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 a database to FOG

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

 

1. Run the following command on PowerShell or Cloud Shel

 

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 perform without any issue or error.

 

 

 

Enjoy!