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

Question

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?

Answer 1

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

Answer 2

There is little difference between Active Geo Replication and Auto Failover groups.

1. Active geo-replication is not supported by Azure SQL Managed Instance but Auto Failover groups is supported.

2. Active geo-replication replicates changes by streaming database transaction log. It is unrelated to transactional replication, which replicates changes by executing DML (INSERT, UPDATE, DELETE) commands. It seems that Active geo-replication is more lightweight and efficient.

Active-geo-replication document

Auto-failover-group document