You are currently viewing How to Achieve High-Availability for Azure SQL by Using Auto-Failover Group

How to Achieve High-Availability for Azure SQL by Using Auto-Failover Group

An auto-failover group is an Azure SQL database feature that replicates one, or a group of databases to the secondary Azure SQL server in a different region. This feature is used to failover all the databases in the failover group in case of disaster and the failover is automatic.

High availability is a crucial component for data professionals, even when operating in a cloud environment such as Azure. The architecture of the Microsoft Azure Platform as a Service (PaaS) is built in a way that offers high availability for service right out of the gate. This helps to ensure that your databases, such as Azure SQL Database and Azure SQL Managed Instances, are always available for use without having to lift a finger. Even more, Microsoft now offers the ability to manually control a failover over for these resources, which gives data professionals more granular control.

Why You Should Choose an Auto-Failover Group

The Azure SQL Database by default offers a 99.99% availability SLA across all its service tiers, meaning any database’s downtime will not exceed 52 minutes per year. Using “zone redundancy” increases availability to 99.995%, which is about 26 minutes per year.

These impressive numbers are achievable 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 a datacentre outage, possibly caused by a natural disaster, network failure, hardware or software malfunction, and others. To protect against a region disaster, auto-failover groups can be configured to automatically failover one or multiple databases to another region.

The Difference Between Geo-Replication and Auto-Failover Group

With geo-replication, the former service provided by Azure, you can designate up to four readable secondary databases (each residing on a separate logical server in any region supported by your Azure subscription, including the region hosting the primary database), which are automatically configured as replication partners. Once the replication is established, you can initiate a planned or unplanned failover between the current primary and one of the secondaries.

While geo-replication is very straightforward to configure (it can be set up directly from the Azure portal by simply designating the desired location of secondary replicas), there are a few drawbacks that limit its usefulness, such as:

The lack of support for simultaneous replication and failover of multiple databases
The need for manual failover
These limitations have been addressed by auto-failover groups. One of the primary strengths of this technology is the support for automatic failover of multiple databases based on custom groupings that you define. All databases in the same group must be part of the same logical server with the source and target servers residing in two different Azure regions.

Another distinction between geo-replication and auto-failover groups is the auto-failover groups support “read-write” and “read-only” listener endpoints that remain the same following a failover. Meaning, client applications will continue to function without any additional configuration regardless of the location of the primary or secondary replica.

Auto-failover group uses the same underlying technology as geo-replication. The following are some of the differences between auto-failover groups and active geo-replication.

Leave a Reply