A bank runs an OLTP workload on a managed SQL instance and needs regional disaster recovery. Data loss of up to one hour is acceptable, recovery within four hours is required, and the application should reconnect through a stable listener name without changing connection strings after a failover. What should you configure?
Show answer and explanation
Answer: B
Auto-failover groups for SQL Managed Instance replicate the instance to a paired secondary region and expose read-write and read-only listener endpoints that stay constant across failover. With an automatic failover policy the service fails over without manual intervention, comfortably within a four-hour recovery window. Replication is asynchronous, so a small amount of recent data can be lost, which fits a one-hour tolerance. Zone redundancy protects against a facility failure but not against loss of a whole region. A manually switched replica needs an operator and does not provide the same listener behaviour. Restored backups need a new instance before data is available.
Why the other options are wrong
- A. Zone redundancy keeps the instance running through a facility failure in one region, but it does nothing if the entire region becomes unavailable.
- C. Manual geo-replication requires an operator to start the switch and is built for single databases, so it does not give the listener behaviour this instance needs.
- D. A restore from copied backups must provision a new instance before data is available, so recovery time depends on database size and is hard to guarantee within four hours.