You are designing a disaster recovery solution for a multi-tier application. The application consists of a web tier, an application tier, and a database tier running SQL Server on Azure VMs. The RPO must be 5 seconds, and the RTO must be 15 minutes. You need to recommend a SQL Server availability solution that meets these requirements. What should you use?
SQL Server Always On Availability Groups with synchronous commit mode writes the transaction to the primary and at least one secondary replica before acknowledging the commit, guaranteeing zero data loss and an RPO of 0 seconds. Automatic failover, when configured with two synchronous replicas and a quorum of validators, can complete in seconds to a few minutes, comfortably meeting the RTO of under 15 minutes. This is the only option that satisfies both the 5-second RPO and 15-minute RTO requirements.
Why this answer
SQL Server Always On Availability Groups with synchronous commit and automatic failover provides near-zero data loss (RPO of 5 seconds) and rapid automatic failover (RTO of 15 minutes) by replicating data synchronously across replicas. This solution meets the stringent RPO/RTO requirements for a multi-tier application running SQL Server on Azure VMs, as it ensures transactions are committed on both primary and secondary replicas before acknowledging success, and automatic failover occurs within seconds if the primary fails.
Exam trap
The trap here is that candidates often confuse Azure Site Recovery's VM-level replication with database-level replication, overlooking that ASR's RPO/RTO are typically higher and not suitable for sub-minute RPO requirements, while log shipping is dismissed due to its manual failover and higher RPO.
How to eliminate wrong answers
Option A is wrong because Azure SQL Database Managed Instance automatic backups have an RPO of up to 5 minutes (not 5 seconds) and an RTO measured in hours, not 15 minutes. Option B is wrong because Azure Site Recovery with replication of SQL Server VMs typically has an RPO of 30 seconds to several minutes and an RTO of 30 minutes or more, and it does not guarantee synchronous replication or automatic failover at the database level. Option C is wrong because SQL Server log shipping has an RPO of minutes (depending on backup/restore intervals) and an RTO of minutes to hours, as it requires manual failover and does not support automatic failover or synchronous replication.