Failover Groups for your Azure SQL Database

This is a part of series “Stairway to being an Azure SQL DBA“, where I will be covering all the topics that an Azure SQL DBA should know about.

Previously we have seen the benefit of Geo-Replication in Azure SQL Database, using which your databases can be made available to multi different locations, and it also supports the read-scale i.e. the read connections can be directed to one of the replicas.

The drawbacks of Geo-Replication are, firstly that you need to make modifications in the application string in order to connect the new primary database post the failover of the Azure SQL Database, secondly multi databases cannot failover together which otherwise would have helped in moving all the databases for the same application together.

A quick recap of the differences between Geo-Replication and Auto-Failover Groups.

Working of Failover Groups

Failover Groups, just like Geo-Replication, uses SQL Server Always-on with asynchronous data movement to provide business continuity.

Failover groups are build on top of Geo-Replication and provide automatic failovers for your applications, this is achieved by having listeners created for the Failover Groups. These listeners are created by Azure.

As you can see in the above pic two listeners are created for each Failover Group. The Read-Only listener can be used for applications or connections which only want to read the data and for these, connections will be directed to the Secondary logical SQL server. The other listener is Read-write listener which can be used to direct the connections to the Primary logical SQL server where Read and writes both can happen.

Apart from the option of Auto-Failover, there is another benefit of creating groups of databases. Similar to Availability Groups on your on-premise Always-On, the Failover-Groups in Azure SQL database behave the same way i.e. all the databases inside the ‘Failover Group’ failover together to the other secondary node.

Configuring Failover Groups for Azure SQL database

Failover Groups unlike Geo-Replication is enabled at SQL Server level as you can club multiple databases together for failovers.

Once in you need to create a Failover group by selecting “+ Add group”

In the Failover Group you need to provide the details of your secondary SQL Server and some other details as below.

1. You can either create a new SQL Server or select an existing one.
2. Read Write failover policy is for setting the failover in case ay failure is detected either it could be Automatic or Manual.
3. Read Write grace is telling the system for wait for some specific period of time before initiating a failover. In the above case it is 1 hour i.e. the failover wont happen for 1 hour since failure of Primary. It is here as the primary and secondary databases are async replication and so secondary can trail behind primary and so by setting the value of 1 hours you are giving an hour time for your secondary database to be able to apply all the logs.
4. You can set multiple databases to be a part of the Failover Group.

Once you have clicked on Create you are done, your replica to be available will take time depending upon the size of the database. The seeding speed is 500 GB per hour.

Failover Group options

After the successful build of your Failover Groups you’ll be able to see the below options.

1. Add databases can be used to add more databases into this failover group.
2. In “Edit Configuration” you will be able to change the Read/write failover policy and Read/Write grace period for your Failover Group.
3. Remove databases can be used to remove the databases from the failover group.
4. By doing “Failover” you can initiate the switch of roles for the primary and secondary.
5. If you want to forcefully failover to the secondary and fine with some data loss (in case the secondary is lagging too behind) you can use the “Forced Failover”. In this the failover doesn’t wait for the read/write grace period.
6. To know what all databases are part of the Failover Group select “Databases within group”.
7. If you have select any new database to add you can see that in “Databases selected to be added”.
8. If you have removed any database from the Failover Group you can see that in “Databases selected for removal”.
9. Here you can see the current SQL Servers their location, role, read/write failover policy and grace period.
10. Read/write listener endpoint URL can be used in the connection string for all the application which needs to directly connect the primary at that time.
11. Read-only listener endpoint URL can be used in the application string for all the applications which only requires to run read on might be not up-to-date data.

Settings Failover Groups using PowerShell

As anything in Azure you can automate the setting up of Failover Group using PowerShell. For doing this you can use the
New-AzSQLDatabaseFailoverGroup cmdlet.

$resourcegroup = "myresourcegroup"
$primaryserver = "myprimarySQLServer"
$secondaryserver = "mysecondarySQLServer"
$failovergroupname = "mynewfailovergroupname"

New-AzSqlDatabaseFailoverGroup -ResourceGroupName $resourcegroup -ServerName $primaryserver -PartnerServerName $secondaryserver -FailoverGroupName $failovergroupname -FailoverPolicy Automatic -GracePeriodWithDataLossHours 1

Conclusion

To have the SQL Server Always-On Availability Groups like feature for your Azure SQL Database you can create a Failover Group. It provides the option to have automatic failover for your Azure SQL databases group without the need of changing the application connection string.