High availability in SQL Server: Always On options

First, two numbers: RPO and RTO

RPO (Recovery Point Objective): acceptable data loss. Something like "at most 5 seconds of transactions may be lost".

RTO (Recovery Time Objective): acceptable downtime. Something like "the service must be back within 60 seconds".

Without these two values, an HA discussion is incomplete; every solution's cost and complexity should be weighed against these targets.

Failover Cluster Instance (FCI)

Instance-level failover on shared storage. All databases move together; clients connect to the same virtual network name.

• Pro: transparent to the application; instance-level objects (logins, jobs, linked servers) in one place.
• Con: shared storage can be a single point of failure; a few tens of seconds of downtime on failover; no read scaling.
• Good for: moderate RTO, low RPO; the need for "the whole instance moves as one piece".

Always On Availability Groups (AG)

Replication at the database-group level. Each replica has its own storage; synchronous or asynchronous copying can be chosen.

• Synchronous + automatic failover: low RPO (near zero) and low RTO; usually 2–3 synchronous replicas and a quorum witness.
• Asynchronous replica: disaster recovery to a remote region; network latency does not affect primary performance, but RPO > 0.
• Readable secondary: offloads reporting and backup load from the primary.
• Listener: the application connects to a single name; routing is automatic.
• Con: instance-level objects must be kept in sync manually on each replica; licensing and operational complexity are higher.

Log shipping and backup-based DR

The transaction-log backup is moved to a second server and applied at intervals. It is simple, cheap and durable; however, RPO is the backup interval and failover is manual. In modern setups it usually sits as an extra safety layer on top of an asynchronous AG replica.

Comparison summary

Solution	Typical RPO	Typical RTO	Read scaling
FCI	≈ 0	15–60 s	No
AG (synchronous, auto failover)	≈ 0	5–30 s	Yes
AG (asynchronous)	seconds–minutes	manual: minutes	Yes
Log shipping	backup interval	manual: minutes–hours	Limited

Values vary by environment and configuration; measuring on your own hardware with a drill is essential.

HA isn't HA until it's drilled

The most common mistake we see: the setup is done, not documented, and never tested. Recommendations:

• Run planned and unplanned failover scenarios at least quarterly; record the real RTO.
• On the application side, use connection retry, short timeouts and idempotent writes.
• Review listener/DNS TTL, quorum configuration and witness placement.
• Keep the runbook current: who runs which command, in what order?

We can design your HA/DR architecture together and prove it with drills — see our Managed Database service.