Log Shipping, Failover Cluster Instances & Quorum
HADR Options — Complete Reference
HADR Options Overview
Log Shipping
Backup→Copy→Restore cycle. Simple, Standard Edition. Manual failover, 15 min RPO.
Always On AG
DB-level HA. Sync/async replicas, auto failover, readable secondaries. Enterprise on-prem.
FCI
Instance-level HA. Shared storage, system DBs included. Requires WSFC + shared disks.
Failover Groups
Azure PaaS DR. Auto failover, single endpoint, cross-region. For SQL DB and MI.
Log Shipping (SQL VM Only)
The simplest DR solution — automated backup → copy → restore cycle.
Architecture
Log Shipping — 3-Job Cycle
Backup Job (Primary)
Backs up transaction log to file share
Every 15 minutes (default)
Copy Job (Secondary)
Copies .trn files from share to local
Runs on secondary server
Restore Job (Secondary)
Restores .trn files in order
NORECOVERY (not readable) or STANDBY (read-only between restores)
Three Jobs of Log Shipping
| Job | Runs On | Action | Default Interval |
|---|---|---|---|
| Backup | Primary | Backs up transaction log → file share | 15 minutes |
| Copy | Secondary | Copies .trn files from share to local | 15 minutes |
| Restore | Secondary | Restores .trn files on secondary DB | 15 minutes |
Restore Modes
| Mode | Secondary Readable? | Behavior |
|---|---|---|
| NORECOVERY | ❌ | DB in Restoring state, cannot be queried |
| STANDBY | ✅ (read-only) | Users disconnected during each restore cycle, then read access resumes |
🎯 Exam Focus
Log Shipping key facts: 1) NOT automatic failover — manual role change required. 2) RPO = backup interval (typically 15 min). 3) RTO = manual (could be hours). 4) Simpler than AG but less capable. 5) Works on Standard Edition (AG requires Enterprise on-prem).
🏢 Real-World DBA Note
Oracle DBA parallel: Log Shipping = Oracle Data Guard in Maximum Performance mode with manual archive log shipping. The backup-copy-restore cycle is like manually shipping archive logs to a standby. Always On AG is far superior (like Data Guard in Maximum Availability mode with automatic switchover).
Log Shipping vs Always On AG
| Feature | Log Shipping | Always On AG |
|---|---|---|
| Auto failover | ❌ Manual | ✅ Automatic |
| Data loss (RPO) | Minutes (backup interval) | 0 (sync) or seconds (async) |
| Read secondary | ✅ (STANDBY mode, interrupted) | ✅ (always readable) |
| Monitoring | Alert job + history tables | AG dashboard + DMVs |
| SQL Edition | Standard + Enterprise | Enterprise (on-prem), any (Azure) |
| Failover time | Manual — hours | Seconds (automatic) |
| Max secondaries | Unlimited | 9 |
| Configuration | Simple | Complex (WSFC + endpoints) |
Failover Cluster Instance (FCI) on Azure VMs
Provides instance-level HA (whole SQL Server instance fails over), unlike AG which is database-level.
Architecture
Failover Cluster Instance (FCI) — Shared Storage
Active Node
Runs SQL Server. Owns the shared storage and cluster IP. Only one node active at a time.
Shared Storage
Azure Shared Disks, Storage Spaces Direct, or Premium File Share. Both nodes access same data.
Passive Node
Standby. On failover, starts SQL Server and attaches shared storage. System DBs transfer automatically.
FCI vs AG — Key Differences
| Aspect | FCI | AG |
|---|---|---|
| Scope | Entire SQL instance | Per-database |
| Shared storage | Required | Not needed (log shipping) |
| Both nodes run SQL? | ❌ (passive is standby) | ✅ (secondary is readable) |
| System databases | ✅ Shared (master, msdb) | ❌ Not replicated |
| SQL Agent jobs | ✅ Shared (in msdb) | ❌ Must sync manually |
| Instance-level objects | ✅ (logins, linked servers) | ❌ Must recreate |
| Read-only secondary | ❌ | ✅ |
| Cross-region DR | Complex | ✅ (async replica) |
Shared Storage Options on Azure
| Option | Performance | Complexity | Cost |
|---|---|---|---|
| Azure Shared Disks | High (Premium SSD/Ultra) | Low | Medium |
| Storage Spaces Direct (S2D) | Very High | High | High |
| Premium File Share (SMB 3.0) | Medium | Low | Low |
🎯 Exam Focus
When to use FCI over AG:
- You need instance-level failover (logins, jobs, linked servers all fail over together)
- You have Standard Edition (AG Basic only supports 1 DB on Standard)
- You need system database failover When to use AG: everything else (it's more flexible and doesn't need shared storage)
Quorum — Deep Dive
Quorum prevents split-brain — ensures only one partition of the cluster keeps running.
How Quorum Voting Works
Quorum Voting
Majority Rule
N/2 + 1 votes needed. 2 nodes + witness = 3 votes → survives 1 failure (2/3 majority).
Cloud Witness
Recommended for Azure. Blob in storage account. Region-independent tiebreaker. No extra VM.
2 Nodes, No Witness
CANNOT survive any failure! Neither node has majority (1/2). Always add a witness.
Quorum Rule
Majority (N/2 + 1) of total votes must agree for the cluster to stay online.
| Cluster Config | Total Votes | Node Fails | Remaining Votes | Survives? |
|---|---|---|---|---|
| 2 nodes, no witness | 2 | 1 node | 1/2 | ❌ No majority |
| 2 nodes + Cloud Witness | 3 | 1 node | 2/3 | ✅ Majority |
| 3 nodes, no witness | 3 | 1 node | 2/3 | ✅ Majority |
| 3 nodes + Cloud Witness | 4 | 1 node | 3/4 | ✅ Majority |
| 3 nodes + Cloud Witness | 4 | 2 nodes | 2/4 | ✅ Majority |
| 4 nodes + Cloud Witness | 5 | 2 nodes | 3/5 | ✅ Majority |
Witness Types
| Witness | Where Stored | When to Use |
|---|---|---|
| Cloud Witness ✅ | Azure Storage Account | Recommended for Azure VMs — region-independent, no extra VM |
| File Share Witness | SMB file share | Hybrid setups with an on-prem file server |
| Disk Witness | Shared cluster disk | Traditional on-prem with shared SAN |
🎯 Exam Focus
Critical quorum facts for DP-300:
- Cloud Witness is recommended for Azure clusters — it's a blob in a storage account
- 2-node cluster WITHOUT witness = CANNOT survive any failure (neither node has majority alone)
- Even number of nodes → always add a witness (tiebreaker)
- Witness should be in a different region from the cluster nodes for maximum resilience
Monitoring HA/DR Solutions
| What to Monitor | How | Alert Threshold |
|---|---|---|
| AG synchronization state | sys.dm_hadr_database_replica_states | NOT SYNCHRONIZED |
| AG replica role | sys.dm_hadr_availability_replica_states | Unexpected role change |
| Log send/redo queue | sys.dm_hadr_database_replica_states | > acceptable RPO |
| Failover group lag | Azure Monitor metrics | Replication lag > threshold |
| Log shipping status | msdb..log_shipping_monitor_* tables | Backup/restore older than threshold |
| Cluster health | Failover Cluster Manager events | Node offline |
Flashcards
What are the 3 jobs in Log Shipping?
Click to reveal answer
1) Backup Job (on primary — backs up transaction log). 2) Copy Job (on secondary — copies .trn files). 3) Restore Job (on secondary — restores .trn files). Default interval: 15 minutes each.
1 / 8
Quiz
A company uses SQL Server Standard Edition and needs a simple DR solution with up to 15-minute RPO. Which solution fits?