Database high availability
In short: Database high availability keeps data accessible when a server fails, usually by keeping a synchronised standby that can be promoted. Design choices are driven by RPO, how much data you can lose, and RTO, how quickly you must recover. Synchronous replication reduces data loss but adds write latency.
Start with RPO and RTO
Before choosing technology, agree two numbers with the business. The recovery point objective (RPO) is the maximum data loss allowed, measured in time. The recovery time objective (RTO) is the maximum time to restore service. An online shop may need an RPO near zero for orders and an RTO of a minute; a reporting database may accept an hour of each.
Main approaches
| Approach | How it works | Strength | Watch out for |
|---|---|---|---|
| Primary with streaming standby | Changes stream from the primary's write-ahead log to standbys | Widely supported, standbys can serve reads | Async mode can lose recent writes |
| Shared-storage failover cluster | Two hosts, one shared disk; only one runs the database | No replication lag | Storage is a single point of failure |
| Availability groups | Database-level replicas with a listener name, as in SQL Server Always On | Automatic failover, readable secondaries | Licensing and cluster setup |
| Distributed SQL | Data sharded with consensus per shard | Survives node or zone loss automatically | Higher write latency across regions |
| Managed cloud database | Provider runs standby in another zone | Little operational work | Know the provider's failover time and limits |
On PostgreSQL, a tool such as Patroni uses a consensus store like etcd to elect the primary and automate failover. On SQL Server, availability groups sit on top of Windows Server Failover Clustering and use its quorum model.
Synchronous or asynchronous
With synchronous replication the primary waits for a standby to confirm each commit, giving an RPO of near zero but adding latency to every write. Keep synchronous standbys close, such as in another zone of the same region. Asynchronous replication does not wait, so it is fast and suits distant disaster recovery sites, but a failover may lose the last seconds of data (see replication and sharding).
A common pattern is one synchronous standby nearby for HA and one asynchronous standby in another region for disaster recovery.
Making failover work for applications
- Connect through a stable endpoint: a virtual IP, a DNS name, an availability group listener or a proxy, never a fixed server address.
- Use connection retry logic in the application, because in-flight transactions fail during failover.
- Keep DNS TTLs short if failover changes DNS records.
- Fence or demote the old primary so it cannot accept writes after recovery.
- Monitor replication lag and alert before it threatens your RPO.
- Rehearse failover and measure the real RTO.
Tip: high availability is not backup. Replicas copy mistakes and ransomware encryption instantly. Keep point-in-time backups, ideally immutable, and test restores.
From a security view, encrypt replication traffic, restrict replication accounts to standby hosts only, and protect backup copies as carefully as the live database.
Practice
The business says it can lose at most 5 minutes of orders. Which objective is that?
Why place a synchronous standby in the same region rather than across the world?
Why should applications connect to a listener or virtual IP rather than a server name?
Key takeaways
- Agree RPO (data loss) and RTO (downtime) first.
- Streaming standbys, clusters, availability groups and managed services are the main options.
- Synchronous replication protects data but adds write latency.
- Applications need stable endpoints and retry logic.
- Replicas are not backups; keep tested point-in-time backups.
Original content written by Mohamed Fayas for FM TecHub. © 2026 FM TecHub. You're welcome to share a link to this page; please don't copy the text elsewhere without permission.