SQL Database High Availability and Disaster Recovery

Evaluate Azure-native Replication and Failover Techniques

When designing high-availability solutions for relational data in Azure, you must evaluate the built-in replication and failover techniques that keep data available with minimal downtime. Each technique uses a different synchronization model, topology, and failover process, and your choice depends on the Recovery Time Objective (RTO) and Recovery Point Objective (RPO) your application requires.

Active Geo-replication

Active geo-replication for Azure SQL Database creates up to four readable secondary databases in different Azure regions. The primary database uses asynchronous replication to copy committed transactions to each secondary, so the secondary is nearly up to date but may lag by a small amount. This technique is used for disaster recovery because it keeps data loss low—typically an RPO of about 5 seconds—and it also offloads read-only workloads to the secondaries, which helps balance load across regions. After a failover, the secondary becomes the new primary, and applications must be redirected to the new endpoint.

Auto-failover Groups

Auto-failover groups group multiple databases together and manage failover at the group level, which is useful for both Azure SQL Database and SQL Managed Instance. The group provides a single, consistent endpoint that remains the same even after a failover, so application code does not need to change. Failover can be automatic when the primary region becomes unavailable, or it can be triggered manually. The automatic failover process typically has an RTO of about 60 seconds, while the RPO depends on the replication delay that existed at the time of the outage. Because the group uses the same asynchronous replication model, data loss is possible but limited.

Managed Instance Availability Groups

Managed Instance availability groups in Azure SQL Managed Instance work like on-premises SQL Server availability groups. Within a single region, you can use synchronous replication between the primary and a secondary replica, which guarantees that committed transactions are written to both replicas before the transaction is acknowledged. This gives an RPO of 0, meaning no data loss. The group supports automatic failover for high availability within the region and manual failover for disaster recovery to a replica in another region. When you need both zero data loss and fast automatic failover locally, this is the strongest option.

Backup Solutions

Even with replication in place, backups provide an additional safety net for data recovery. Azure SQL Database automatically creates full, differential, and transaction log backups. The point-in-time restore feature lets you restore the database to any moment within the retention period, which is useful for recovering from application errors or accidental data changes. Geo-restore uses geo-redundant backups to restore the database in any Azure region, with a typical RPO of 1 hour; this is a good fallback when the primary region is lost and replication is not in place. For long-term compliance, long-term backup retention stores backups for up to 10 years.

RTO and RPO Considerations

Your choice of technique is driven by the RTO and RPO targets. For high availability within a region using zone redundancy, the RTO is typically less than 30 seconds and the RPO is 0, because the replicas are kept in sync synchronously. For disaster recovery across regions using failover groups, the RTO is around 60 seconds, but the RPO varies based on the asynchronous replication delay. When you accept a slightly higher RPO, you gain geographic separation and protection against a full region outage. For the lowest possible data loss, combine synchronous replication within a region with asynchronous replication across regions using a multi-layered approach.