SQL Server / Advanced / Lesson 16
High availability and disaster recovery
Compare SQL Server availability groups, failover cluster instances, and log shipping from failure scope and business objectives. Include clients and instance dependencies in the design.
On this page
Lesson goals
- Map failure domains to an HA/DR strategy.
- Explain synchronous and asynchronous commit tradeoffs.
- Plan client reconnection and a measured failover drill.
Design around failures, not product names
High availability reduces interruption from component failure; disaster recovery prepares for larger losses such as a site or storage failure. Define what can fail together: power, network, storage, host, region, administrator credentials, or a deployment. Two replicas sharing the same vulnerable dependency do not protect against that dependency's loss. Express target RPO and RTO for each scenario.
A service remains unavailable if the database fails over but the application cannot resolve its listener, authenticate, or reach a dependency. Include DNS, certificates, firewalls, identity, and application routing in the model. Keep recovery objectives realistic about detection, decision, failover, reconnect, and validation time. Feature licensing and platform support must be checked for the actual edition and release.
Understand availability-group scope
An Always On availability group replicates a selected set of user databases to availability replicas. The primary handles read-write traffic, and eligible secondaries can serve configured read-only workloads. Traditional database-level replication does not automatically carry every login, SQL Agent job, linked server, or other instance object. Inventory and manage dependencies separately for the SQL Server 2019 baseline.
Enterprise availability groups and Standard basic availability groups have different capabilities. Cluster-managed HA and read-scale-only configurations also differ; a read-scale replica is not automatically an automatic-failover solution. Windows and Linux use different cluster infrastructure. The read-only query inspects local replica information where the feature exists, but it does not verify end-to-end service health.
SELECT ag.name,ars.role_desc,ars.operational_state_desc,
ars.connected_state_desc
FROM sys.availability_groups AS ag
JOIN sys.dm_hadr_availability_replica_states AS ars
ON ars.group_id=ag.group_id
WHERE ars.is_local=1;A standalone instance may return no rows. Permissions and feature configuration determine visibility.
Understand durability and latency tradeoffs
Synchronous commit waits for required log hardening on a synchronized synchronous secondary before acknowledging the protected commit. This adds network and storage latency. Asynchronous commit avoids waiting for that acknowledgement but permits data-loss exposure if the primary fails before the secondary receives and hardens the latest log. Queue and synchronization state matter more than a mode label alone.
Automatic failover has prerequisites, including supported cluster configuration and appropriate synchronized synchronous replicas. Forced failover can lose data and requires an incident decision based on evidence. A healthy replica yesterday is not proof of readiness now. Inspect per-database synchronization, send and redo queues, and the operational context before any real failover action; this course performs none.
Compare clustering, log shipping, and backups
A failover cluster instance protects an instance service through cluster nodes and shared-storage design; it does not inherently provide an independent second database copy against every storage failure. Log shipping moves and restores log backups on another server, typically with operationally managed failover and latency related to backup/copy/restore schedules. Each strategy solves a different failure and management problem.
Replicas are not backups. An accidental delete or corrupt application change can replicate promptly, so retain independent recoverable history. Coordinate backups, integrity checks, patching, and recovery drills with the availability topology. A copy located far away but inaccessible because its keys or credentials were lost is not a usable recovery asset.
Test the client path through failover
Applications should connect through the documented stable endpoint, use appropriate driver capabilities, and reopen connections after transient failures according to a bounded policy. Pooled connections can become invalid after failover. A write interrupted around COMMIT can have an uncertain outcome, so idempotency and reconciliation matter as much as reconnection. Read-only routing has its own configuration and consistency expectations.
A tabletop and isolated drill should verify identity mappings, jobs, backup ownership, listener resolution, reconnect behavior, and known transaction outcomes. Measure the entire service interruption, not just a role-state change. The following local check identifies replica role for one named database when applicable; it is a diagnostic hint, not authorization for an operational switch.
SELECT sys.fn_hadr_is_primary_replica(N'TrainingShop')
AS is_primary_replica;The result can be 1, 0, or NULL depending on database availability-group membership and visibility. It is not a connectivity test.
Common errors and fixes
Failover succeeded but the application failed.
- Why it happens
- Endpoint, identity, or instance dependencies were omitted.
- How to fix it
- Validate the complete client and dependency path.
- Prevent it next time
- Include application readiness in drills.
An asynchronous failover lost recent orders.
- Why it happens
- Acknowledged primary work had not hardened on the target.
- How to fix it
- Reconcile with protected evidence and review the accepted RPO.
- Prevent it next time
- Monitor lag and document failover loss decisions.
A replicated delete removed every copy.
- Why it happens
- Replication was mistaken for historical recovery.
- How to fix it
- Restore independent protected history through the recovery plan.
- Prevent it next time
- Keep backups separate from availability replicas.
Check your understanding
1. Does a synchronous secondary guarantee zero data loss during any forced failover?
Show worked solution
No. Protection depends on actual synchronization and supported conditions. Forced failover to an unsynchronized target can lose work. Inspect evidence and follow the incident decision process.
2. The database role changes in five seconds but clients recover in two minutes. What is the measured service RTO?
Show worked solution
The meaningful service interruption includes client recovery and validation, roughly two minutes in this scenario, not just the five-second role change. Investigate DNS, connection handling, and dependencies.
Key takeaways
- Choose topology from failure domains and business objectives.
- Commit mode does not replace readiness checks.
- Availability replicas do not replace protected backups.