Introduction to Azure SQL Database Auto-failover groups

SQL Azure Database by default offers a 99.99% availability SLA across all its service tiers. This means that for any database, the downtime should not exceed 52 minutes per year. Using Zone redundancy increases availability to 99.995% which is about 26 minutes per year.

These impressive numbers can be achieved through in-region redundancy of the compute and storage resources and automatic failover within the region.

Some disruptive events may impact the region’s availability like Datacenter outage, possibly caused by a natural disaster.
To protect against a Region disaster, Auto-failover groups can be configured to automatically failover one or multiple databases to another region.

The technology behind Auto-failover group is the same as geo-replication but they are some differences.

In this blog post, we will configure an Auto-failover group for the database we previously created in a previous post.

So I start with the simple configuration of multiple Azure SQL Databases on a single server.

Create a new server in another region

First, we need to create our backup server in another Azure region, I will choose Japan East.

New-AzSqlServer -ResourceGroupName 'SQLFailover-lab-rg' `
    -ServerName 'snasqlsrv-lab-02' `
    -Location 'Japan East' `
    -SqlAdministratorCredentials $(New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList "LabSqlAdmin", $(ConvertTo-SecureString -String "MyPassword" -AsPlainText -Force))

I now have my second server ready with no databases except of master.

Get-AzResource | Where ResourceType -like '*Sql*' | ft

Name                    ResourceGroupName  ResourceType                    Location
----                    -----------------  ------------                    --------
snasqlsrv-lab-01        SQLFailover-lab-rg Microsoft.Sql/servers           francecentral
snasqlsrv-lab-01/DB01   SQLFailover-lab-rg Microsoft.Sql/servers/databases francecentral
snasqlsrv-lab-01/DB02   SQLFailover-lab-rg Microsoft.Sql/servers/databases francecentral
snasqlsrv-lab-01/master SQLFailover-lab-rg Microsoft.Sql/servers/databases francecentral
snasqlsrv-lab-02        SQLFailover-lab-rg Microsoft.Sql/servers           japaneast
snasqlsrv-lab-02/master SQLFailover-lab-rg Microsoft.Sql/servers/databases japaneast

Create the Auto-Failover group

New-AzSqlDatabaseFailoverGroup -ResourceGroupName 'SQLFailover-lab-rg' `
>>     -ServerName 'snasqlsrv-lab-01' -PartnerServerName 'snasqlsrv-lab-02' `
>>     -FailoverGroupName 'sqlfailover-lab-fg'

FailoverGroupName                    : sqlfailover-lab-fg
Location                             : France Central
ResourceGroupName                    : SQLFailover-lab-rg
ServerName                           : snasqlsrv-lab-01
PartnerLocation                      : Japan East
PartnerResourceGroupName             : SQLFailover-lab-rg
PartnerServerName                    : snasqlsrv-lab-02
ReplicationRole                      : Primary
ReplicationState                     : CATCH_UP
ReadWriteFailoverPolicy              : Automatic
FailoverWithDataLossGracePeriodHours : 1
DatabaseNames                        : {}

I now have an Automatic Failover group between my 2 regions.

Add databases to Auto-Failover group

There are no databases in the group yet. Let’s add them.

$database = Get-AzSqlDatabase -ResourceGroupName 'SQLFailover-lab-rg' -ServerName 'snasqlsrv-lab-01' -DatabaseName 'DB01'
Add-AzSqlDatabaseToFailoverGroup -ResourceGroupName 'SQLFailover-lab-rg' -ServerName 'snasqlsrv-lab-01' `
    -FailoverGroupName 'sqlfailover-lab-fg' -Database $database

The script above adds a single database to the Failover group. The script below will add all databases on the primary server.

$server01 = Get-AzSqlServer -ResourceGroupName 'SQLFailover-lab-rg' -ServerName 'snasqlsrv-lab-01'
$server01 | Add-AzSqlDatabaseToFailoverGroup -FailoverGroupName 'sqlfailover-lab-fg' -Database ($server01 | Get-AzSqlDatabase)

I can now see my two databases on my Failover group;

Get-AzSqlDatabaseFailoverGroup -FailoverGroupName 'sqlfailover-lab-fg' -ResourceGroupName 'SQLFailover-lab-rg' -ServerName 'snasqlsrv-lab-01'


FailoverGroupName                    : sqlfailover-lab-fg
Location                             : France Central
ResourceGroupName                    : SQLFailover-lab-rg
ServerName                           : snasqlsrv-lab-01
PartnerLocation                      : Japan East
PartnerResourceGroupName             : SQLFailover-lab-rg
PartnerServerName                    : snasqlsrv-lab-02
ReplicationRole                      : Primary
ReplicationState                     : CATCH_UP
ReadWriteFailoverPolicy              : Automatic
FailoverWithDataLossGracePeriodHours : 1
DatabaseNames                        : {DB01, DB02}

An overview of the group with a map is now available on Azure Portal.

Listeners

The Auto-Failover provides 2 listeners. The first one for read-write OLTP type workload. The second one for read-only connection.
The DNS records are updated automatically to redirect to the correct server after a role change.

• Read-write: <failovergroup-name>.database.windows.net
• Read-only: <failovergroup-name>.secondary.database.windows.net

Connecting to my Read-only listener I can indeed read data but not modify it.

Msg 3906, Level 16, State 2, Line 6
Failed to update database "DB01" because the database is read-only.

Check the status of the Auto-Failover group

As we have seen already we can look at the status in PowerShell with Get-Az

(Get-AzSqlDatabaseFailoverGroup `
   -FailoverGroupName 'sqlfailover-lab-fg' `
   -ResourceGroupName 'SQLFailover-lab-rg' `
   -ServerName 'snasqlsrv-lab-01').ReplicationRole

Some information about geo-replication are available in SQL DMVs. Here I use sys.geo_replication_links in the master database.

select d.name, @@SERVERNAME AS ServerName
	, replication_state_desc, role_desc, partner_server 
from sys.geo_replication_links AS grl
	join sys.databases AS d
		on grl.database_id = d.database_id

Manual Failover

A failover can be done manually using the Switch-AzSqlDatabaseFailoverGroup command.
Before doing it, let’s simply from SQL which server is now primary when I connect to my read-write listener;

My primary server is the 01. Let’s Failover.

Switch-AzSqlDatabaseFailoverGroup -ResourceGroupName 'SQLFailover-lab-rg' `
   -ServerName 'snasqlsrv-lab-02' `
   -FailoverGroupName 'sqlfailover-lab-fg'

After a few seconds, my read-write listener now redirects my connection to server 02.

In this blog post, we have seen how to configure an Auto-failover group for Azure SQL Database.
This is definitely something that would have been more complicated to do on-premise.