azure-sql-databaseazure-sql

What is the main difference between Active Geo Replication and Auto Failover Groups for Azure SQL DB


I would like to know what is the difference between Active Geo Replication and Auto Failover groups in Azure SQL DB ? I read that in Auto Failover groups, the secondary database is always created on a secondary region, but active geo-replication can happen between same region also. So when one should use compared to the other?


Solution

  • According to MSFT documentation - the Auto-failover groups "is a declarative abstraction on top of the existing active geo-replication feature, designed to simplify deployment and management of geo-replicated databases at scale". BCDR is the biggest use case - manual or automatic failover of SQL data to another region.

    The auto-failover group feature imposes some limitations while adding convenience -

    1. A listener concept enables your apps to take advantage of the same endpoint to your SQL, while with geo-replication your app is responsible for connection strings manipulation to target required SQL instance
    2. On another hand, geo-replication supports multiple RO targets including in the same region, while failover group supports only two SQL instances in different regions, in which one is RW and another is RO
    3. As validly pointed in another answer, SQL managed instances only support failover groups via vNet peering