High Availability and Disaster Recovery

43.1 HA/DR Concepts

High availability: minimize downtime (local failover).

Disaster recovery: survive a site loss (remote copies).

Metrics: RPO, RTO, availability "nines" (99.99% ≈ 52 minutes of downtime per year).

43.2 Options Overview

Log Shipping All except Express Yes (with witness) No (snapshots) Failover Cluster Instance (FCI) Std (2 nodes), Ent Always On Availability Groups Ent; Basic AG in Std Contained AG (2022) Group + instance objects Distributed AG Managed Instance link SQL Server ↔ Azure SQL MI

43.3 Log Shipping

Steps: back up the log on the primary → copy to the secondary → restore on the secondary (SQL Agent jobs).

Optional monitor server. Secondaries can be in NORECOVERY or STANDBY.

Failover is manual: apply the remaining logs, RESTORE ... WITH RECOVERY, and redirect the applications.

43.4 Failover Cluster Instances (FCI)

Windows Server Failover Cluster (WSFC) or Pacemaker (Linux).

Shared storage (SAN, S2D, SMB, cluster shared volumes). One instance moves between nodes.

Protects against server failure, not storage failure.

43.5 Always On Availability Groups

Primary replica (read-write) plus up to 8 secondaries (2016+). Basic AG supports 1 database and 2 replicas.

Availability modes: synchronous commit (zero data loss, HA) vs asynchronous commit (DR).

Failover modes: automatic, planned manual, forced (possible data loss).

Listener: a virtual network name that clients connect to (MultiSubnetFailover=True).

Readable secondaries: offload reporting, backups, and DBCC. Read-only routing (ApplicationIntent=ReadOnly).

Seeding: automatic or manual (backup/restore).

Requirements: WSFC (or clusterless/read-scale AGs, or Pacemaker on Linux), FULL recovery model, a full backup.

Instance-level objects (logins, jobs, linked servers) don't sync unless you use a contained AG.

CREATE AVAILABILITY GROUP AG_Shop
WITH (AUTOMATED_BACKUP_PREFERENCE = SECONDARY, DB_FAILOVER = ON)
FOR DATABASE ShopDB
REPLICA ON
  N'SQL1' WITH (ENDPOINT_URL = N'TCP://sql1.corp.local:5022', AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
                FAILOVER_MODE = AUTOMATIC, SEEDING_MODE = AUTOMATIC,
                SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY)),
  N'SQL2' WITH (ENDPOINT_URL = N'TCP://sql2.corp.local:5022', AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
                FAILOVER_MODE = AUTOMATIC, SEEDING_MODE = AUTOMATIC,
                SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));

ALTER AVAILABILITY GROUP AG_Shop ADD LISTENER N'ShopListener' (WITH IP ((N'10.0.0.50', N'255.255.255.0')), PORT = 1433);
ALTER AVAILABILITY GROUP AG_Shop FAILOVER;   -- run on the target secondary

-- Monitoring
SELECT ag.name, ar.replica_server_name, ars.role_desc, ars.synchronization_health_desc
FROM sys.availability_groups ag
JOIN sys.availability_replicas ar ON ar.group_id = ag.group_id
JOIN sys.dm_hadr_availability_replica_states ars ON ars.replica_id = ar.replica_id;
SELECT * FROM sys.dm_hadr_database_replica_states;  -- log_send_queue_size, redo_queue_size

43.6 DR Planning

Document runbooks, test failovers regularly, monitor replication lag, and plan for logins, jobs, SSIS packages, linked servers, and certificates on every replica.

Hybrid DR: Azure SQL Managed Instance link, Azure VM replicas, backup to URL.