As many of you know, I am a big fan of Azure SQL Database, the Platform as a Service (PaaS) database solution and that it frequently supports new features such as Active Geo-Replication for Azure SQL Database ALL Tiers and TRIM.
A failover group is a way of achieving transparent geographic failover for Azure SQL Database. Often an issue with secondary replica’s and failover is the connection string. In database mirroring we had the special mirror partner section of the string, in database availability groups we have the listener and now in Azure SQL Database we have failover groups and their endpoint listener.
A failover group is a server-level group that can contain one or more databases which are failed over as a group. There may be more than one failover group per server. The group can include individual databases, elastic pools or a combination of them. You can also add failover groups if you are already using geo-replication at no extra cost. The only limitation currently is there is only one read-only replica supported.
Firstly you need to add a group as below;
Next choose your failover group secondary server;
Next choose your database(s) to join the failover group, you will be shown additional costs;
Once this is all setup we can go into the failover group and examine the configuration. The most important point to note from the below is the introduction of the new read/write and read-only listener endpoints;
As you can see below we’ve performed a planned failover and swapped our servers. A well coded application with good connection retry logic will attempt to re-establish a connection over the same listerner endpoint (either read/write and read-only as applicable) and should connect to the other database server transparently;
With the constant development of Azure SQL Database and transparent geographic failover now exposing both read/write and read-only listener endpoints we can build our solution faster, easier and it will be more robust with these enhanced high availability features.
Thanks for reading!