In this lesson, you will learn the foundations of High Availability (HA) and Disaster Recovery (DR)
in SQL Server. These topics are not just “infrastructure.” They directly affect performance, stability, uptime, data protection,
and how you design monitoring and maintenance.
Big Idea: HA and DR are about risk management. The first DBA question is not “Which feature is best?”
The first question is: What level of downtime and data loss is acceptable? Your business requirements drive your architecture.
120–180 minutes (architecture + operations + lab planning)
Many teams confuse HA and DR. A DBA must be precise:
HA is designed to reduce downtime from common failures (server failure, instance crash, patching).
The focus is fast failover and keeping the service available.
DR is designed to recover from major events (data center outage, region failure, ransomware, catastrophic storage failure).
The focus is being able to restore or fail over to a separate environment and keep the business running.
Key teaching point: HA is not a replacement for backups. DR is not a replacement for HA.
You often need both.
Two metrics define “how good” your HA/DR strategy must be:
Key teaching point: Many organizations demand “no data loss” without understanding the cost.
“Zero data loss” usually implies synchronous commit and comes with latency/performance tradeoffs.
SQL Server provides multiple technologies. You choose based on RPO/RTO, licensing, complexity tolerance,
and whether you need database-level or instance-level protection.
FCI protects the SQL Server instance. It uses Windows Server Failover Clustering with shared storage.
If the active node fails, SQL Server starts on another node using the same shared storage.
AG protects one or more databases. Each database has a primary replica and one or more secondary replicas.
Data is replicated to secondaries using the transaction log.
Replication is typically used for data distribution or reporting scenarios, not as the primary HA strategy for OLTP.
It is powerful but complex and not a “simple failover” solution.
Key teaching point: FCI is about keeping the instance alive; AG is about keeping specific databases available.
They solve different problems.
DR is not one feature. It is a complete plan: data protection + restore/failover + validation + communication.
These are the most common DR approaches in SQL Server environments:
Log shipping copies transaction log backups to a secondary server and restores them on a schedule.
It is a proven, cost-effective DR approach.
AG can include a remote replica (in another data center or region). This supports quicker failover than restore-based DR.
These can help, but they do not replace SQL Server backup strategy. DBAs must ensure transaction consistency and
must still test recovery procedures.
HA/DR designs directly influence performance. If you do not understand the tradeoffs, you can build “high availability”
that becomes “high latency.”
If the primary generates log faster than the secondary can receive and redo it, queues grow.
Large queues indicate the secondary is behind, which affects DR readiness and failover confidence.
Using a secondary for reporting can be helpful, but it may increase CPU and I/O on the secondary,
and it can create operational complexity. DBAs must monitor performance and ensure the secondary remains ready for failover.
Key teaching point: “No data loss + long distance” is usually a performance conflict.
Business requirements must decide the tradeoff.
Many organizations build HA/DR but never test it. Then during a real incident, the failover or restore fails.
A professional DBA treats HA/DR as a system that must be continuously validated.
These queries help you check your backup history and basic Availability Group health.
They are safe and do not modify anything.
/* Backup history overview per database */ SELECT bs.database_name, MAX(CASE WHEN bs.type = 'D' THEN bs.backup_finish_date END) AS last_full_backup, MAX(CASE WHEN bs.type = 'I' THEN bs.backup_finish_date END) AS last_diff_backup, MAX(CASE WHEN bs.type = 'L' THEN bs.backup_finish_date END) AS last_log_backup FROM msdb.dbo.backupset bs GROUP BY bs.database_name ORDER BY bs.database_name;
How to interpret: If a database has no recent backups, your RPO is at risk.
If log backups are not running, point-in-time recovery may be impossible.
/* Availability Group replica health (run on primary) */ SELECT ag.name AS ag_name, ar.replica_server_name, ars.role_desc, ars.synchronization_state_desc, ars.connected_state_desc, ars.last_connect_error_description FROM sys.availability_groups ag JOIN sys.availability_replicas ar ON ag.group_id = ar.group_id JOIN sys.dm_hadr_availability_replica_states ars ON ar.replica_id = ars.replica_id AND ar.group_id = ars.group_id ORDER BY ag.name, ar.replica_server_name;
How to interpret: You want replicas connected and synchronized (when configured for sync).
Connection errors or disconnected replicas indicate HA/DR risk.
/* Queue sizes (high values can indicate replica lag) */ SELECT DB_NAME(drs.database_id) AS database_name, drs.is_primary_replica, drs.synchronization_state_desc, drs.log_send_queue_size, drs.redo_queue_size, drs.redo_rate, drs.log_send_rate FROM sys.dm_hadr_database_replica_states drs ORDER BY drs.log_send_queue_size DESC, drs.redo_queue_size DESC;
How to interpret: Large queues suggest the secondary is behind.
That may be normal during heavy load, but it must be monitored, especially for DR readiness.
Students will design an HA/DR strategy for a fictional company and write a simple runbook with monitoring requirements.
A company runs an e-commerce application on SQL Server. Peak hours are evenings and weekends.
Requirements: RTO = 30 minutes, RPO = 10 minutes.
They can afford one secondary server in another location and need a plan that is realistic to operate.
Prompt:
“If a business demands ‘zero data loss’ and ‘instant failover’ but also wants the DR site in another region,
what performance tradeoffs will appear? What questions should a DBA ask the business before agreeing?”
In the next lesson, we will cover SQL Server Security and Compliance.
You will learn core security concepts (authentication, authorization, least privilege), common misconfigurations,
auditing, encryption options, and how security choices affect performance and operations.
Not a member yet? Register now
Are you a member? Login now