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?
1
votes
2 Answers
1
votes
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 -
- 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
- 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
- As validly pointed in another answer, SQL managed instances only support failover groups via vNet peering
2
votes
There is little difference between Active Geo Replication and Auto Failover groups.
Active geo-replication is not supported by Azure SQL Managed Instance but Auto Failover groups is supported.
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.
