{"id":14231,"date":"2020-06-03T06:30:23","date_gmt":"2020-06-03T04:30:23","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/introduction-to-azure-sql-database-auto-failover-groups\/"},"modified":"2025-10-01T11:45:29","modified_gmt":"2025-10-01T09:45:29","slug":"introduction-to-azure-sql-database-auto-failover-groups","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/introduction-to-azure-sql-database-auto-failover-groups\/","title":{"rendered":"Introduction to Azure SQL Database Auto-failover groups"},"content":{"rendered":"

SQL Azure Database by default offers a 99.99% availability SLA<\/a> 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.<\/p>\n

These impressive numbers can be achieved through in-region redundancy<\/a> of the compute and storage resources and automatic failover within the region.<\/p>\n

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

The technology behind Auto-failover group is the same as geo-replication<\/a> but they are some differences<\/a>.<\/p>\n

\"\"<\/a><\/p>\n

In this blog post, we will configure an Auto-failover group for the database we previously created in a previous post<\/a>.<\/p>\n

So I start with the simple configuration of multiple Azure SQL Databases on a single server.<\/p>\n

\"SSMS_Azure_Database\"<\/a><\/p>\n

Create a new server in another region<\/h4>\n

First, we need to create our backup server in another Azure region<\/a>, I will choose Japan East.<\/p>\n

\n
\n\n\n\n
\n
1<\/div>\n
2<\/div>\n
3<\/div>\n
4<\/div>\n<\/td>\n
\n
\n
New-AzSqlServer<\/code> -ResourceGroupName<\/code> 'SQLFailover-lab-rg'<\/code> `<\/code><\/div>\n
\u00a0\u00a0\u00a0\u00a0<\/code>-ServerName<\/code> 'snasqlsrv-lab-02'<\/code> `<\/code><\/div>\n
\u00a0\u00a0\u00a0\u00a0<\/code>-Location<\/code> 'Japan East'<\/code> `<\/code><\/div>\n
\u00a0\u00a0\u00a0\u00a0<\/code>-SqlAdministratorCredentials<\/code> $(<\/code>New-Object<\/code> -TypeName<\/code> System.Management.Automation.PSCredential<\/code> -ArgumentList<\/code> \"LabSqlAdmin\"<\/code>, $(<\/code>ConvertTo-SecureString<\/code> -String<\/code> \"MyPassword\"<\/code> -AsPlainText<\/code> -Force<\/code>))<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

I now have my second server ready with no databases except of master.<\/p>\n

\n
\n\n\n\n
\n
1<\/div>\n
2<\/div>\n
3<\/div>\n
4<\/div>\n
5<\/div>\n
6<\/div>\n
7<\/div>\n
8<\/div>\n
9<\/div>\n
10<\/div>\n<\/td>\n
\n
\n
Get-AzResource<\/code> | Where ResourceType <\/code>-like<\/code> '*Sql*'<\/code> | ft<\/code><\/div>\n
<\/div>\n
Name\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ResourceGroupName\u00a0 ResourceType\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Location<\/code><\/div>\n
----\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 -----------------\u00a0 ------------\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 --------<\/code><\/div>\n
snasqlsrv-lab-01\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SQLFailover-lab-rg Microsoft.Sql\/servers\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 francecentral<\/code><\/div>\n
snasqlsrv-lab-01\/DB01\u00a0\u00a0 SQLFailover-lab-rg Microsoft.Sql\/servers\/databases francecentral<\/code><\/div>\n
snasqlsrv-lab-01\/DB02\u00a0\u00a0 SQLFailover-lab-rg Microsoft.Sql\/servers\/databases francecentral<\/code><\/div>\n
snasqlsrv-lab-01\/master SQLFailover-lab-rg Microsoft.Sql\/servers\/databases francecentral<\/code><\/div>\n
snasqlsrv-lab-02\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SQLFailover-lab-rg Microsoft.Sql\/servers\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 japaneast<\/code><\/div>\n
snasqlsrv-lab-02\/master SQLFailover-lab-rg Microsoft.Sql\/servers\/databases japaneast<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

Create the Auto-Failover group<\/h4>\n
\n
\n\n\n\n
\n
1<\/div>\n
2<\/div>\n
3<\/div>\n
4<\/div>\n
5<\/div>\n
6<\/div>\n
7<\/div>\n
8<\/div>\n
9<\/div>\n
10<\/div>\n
11<\/div>\n
12<\/div>\n
13<\/div>\n
14<\/div>\n
15<\/div>\n
16<\/div>\n<\/td>\n
\n
\n
New-AzSqlDatabaseFailoverGroup<\/code> -ResourceGroupName<\/code> 'SQLFailover-lab-rg'<\/code> `<\/code><\/div>\n
>><\/code>\u00a0\u00a0\u00a0\u00a0 -ServerName<\/code> 'snasqlsrv-lab-01'<\/code> -PartnerServerName<\/code> 'snasqlsrv-lab-02'<\/code> `<\/code><\/div>\n
>><\/code>\u00a0\u00a0\u00a0\u00a0 -FailoverGroupName<\/code> 'sqlfailover-lab-fg'<\/code><\/div>\n
<\/div>\n
FailoverGroupName\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 : sqlfailover-lab-fg<\/code><\/div>\n
Location\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 : France Central<\/code><\/div>\n
ResourceGroupName\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 : SQLFailover-lab-rg<\/code><\/div>\n
ServerName\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 : snasqlsrv-lab-01<\/code><\/div>\n
PartnerLocation\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 : Japan East<\/code><\/div>\n
PartnerResourceGroupName\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 : SQLFailover-lab-rg<\/code><\/div>\n
PartnerServerName\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 : snasqlsrv-lab-02<\/code><\/div>\n
ReplicationRole\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 : Primary<\/code><\/div>\n
ReplicationState\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 : CATCH_UP<\/code><\/div>\n
ReadWriteFailoverPolicy\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 : Automatic<\/code><\/div>\n
FailoverWithDataLossGracePeriodHours : 1<\/code><\/div>\n
DatabaseNames\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 : {}<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

I now have an Automatic Failover group between my 2 regions.<\/p>\n

Add databases to Auto-Failover group<\/h4>\n

There are no databases in the group yet. Let’s add them.<\/p>\n

\n
\n\n\n\n
\n
1<\/div>\n
2<\/div>\n
3<\/div>\n<\/td>\n
\n
\n
$database<\/code> = <\/code>Get-AzSqlDatabase<\/code> -ResourceGroupName<\/code> 'SQLFailover-lab-rg'<\/code> -ServerName<\/code> 'snasqlsrv-lab-01'<\/code> -DatabaseName<\/code> 'DB01'<\/code><\/div>\n
Add-AzSqlDatabaseToFailoverGroup<\/code> -ResourceGroupName<\/code> 'SQLFailover-lab-rg'<\/code> -ServerName<\/code> 'snasqlsrv-lab-01'<\/code> `<\/code><\/div>\n
\u00a0\u00a0\u00a0\u00a0<\/code>-FailoverGroupName<\/code> 'sqlfailover-lab-fg'<\/code> -Database<\/code> $database<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

The script above adds a single database to the Failover group. The script below will add all databases on the primary server.<\/p>\n

\n
\n\n\n\n
\n
1<\/div>\n
2<\/div>\n<\/td>\n
\n
\n
$server01<\/code> = <\/code>Get-AzSqlServer<\/code> -ResourceGroupName<\/code> 'SQLFailover-lab-rg'<\/code> -ServerName<\/code> 'snasqlsrv-lab-01'<\/code><\/div>\n
$server01<\/code> | <\/code>Add-AzSqlDatabaseToFailoverGroup<\/code> -FailoverGroupName<\/code> 'sqlfailover-lab-fg'<\/code> -Database<\/code> (<\/code>$server01<\/code> | <\/code>Get-AzSqlDatabase<\/code>)<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

I can now see my two databases on my Failover group;<\/p>\n

\n
\n\n\n\n
\n
1<\/div>\n
2<\/div>\n
3<\/div>\n
4<\/div>\n
5<\/div>\n
6<\/div>\n
7<\/div>\n
8<\/div>\n
9<\/div>\n
10<\/div>\n
11<\/div>\n
12<\/div>\n
13<\/div>\n
14<\/div>\n
15<\/div>\n<\/td>\n
\n
\n
Get-AzSqlDatabaseFailoverGroup<\/code> -FailoverGroupName<\/code> 'sqlfailover-lab-fg'<\/code> -ResourceGroupName<\/code> 'SQLFailover-lab-rg'<\/code> -ServerName<\/code> 'snasqlsrv-lab-01'<\/code><\/div>\n
<\/div>\n
<\/div>\n
FailoverGroupName\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 : sqlfailover-lab-fg<\/code><\/div>\n
Location\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 : France Central<\/code><\/div>\n
ResourceGroupName\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 : SQLFailover-lab-rg<\/code><\/div>\n
ServerName\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 : snasqlsrv-lab-01<\/code><\/div>\n
PartnerLocation\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 : Japan East<\/code><\/div>\n
PartnerResourceGroupName\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 : SQLFailover-lab-rg<\/code><\/div>\n
PartnerServerName\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 : snasqlsrv-lab-02<\/code><\/div>\n
ReplicationRole\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 : Primary<\/code><\/div>\n
ReplicationState\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 : CATCH_UP<\/code><\/div>\n
ReadWriteFailoverPolicy\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 : Automatic<\/code><\/div>\n
FailoverWithDataLossGracePeriodHours : 1<\/code><\/div>\n
DatabaseNames\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 : {DB01, DB02}<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

An overview of the group with a map is now available on Azure Portal<\/a>.<\/p>\n

\"AzurePortal_FG\"<\/a><\/p>\n

Listeners<\/h4>\n

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