17 High Availability and Disaster Recovery

High availability and disaster recovery for SQL Server are presented as essential yet complex responsibilities, and the chapter shows how dbatools streamlines the setup, monitoring, and day-to-day management of common HADR patterns. It focuses on three core options—Log Shipping, Windows Server Failover Cluster Services, and Availability Groups—and emphasizes that while each has prerequisites and moving parts, dbatools wraps the intricate, error-prone steps into repeatable PowerShell commands with sensible defaults. The result is faster provisioning, clearer diagnostics, and safer operations across Windows, Linux, and even containerized labs.

The Log Shipping section explains the concept—backing up and shipping transaction logs from a primary to one or more secondary databases—and demonstrates how a single command can configure it with just a few required parameters. It highlights prerequisites such as SQL Agent service account access to shared paths, default 15-minute job schedules when timings aren’t specified, and the practical challenges of using traditional tooling. For troubleshooting, a dedicated command aggregates errors and points to root causes like access issues, while a recovery command performs a controlled cutover to bring secondaries online; the chapter also notes that dbatools does not automatically take the original primary offline, which can be handled separately if needed.

For Windows Server Failover Cluster Services, the chapter shows how dbatools queries cluster nodes, roles, and resources without additional modules or GUIs, making discovery and auditing straightforward. Availability Groups receive a deep dive: enabling the feature on instances, then creating an AG with a single command that checks prerequisites, configures replicas, handles seeding via backup/restore or automatic methods, manages permissions, and optionally adds a listener. The authors also provide a container-based lab option for quick hands-on practice. Once AGs are running, dbatools offers comprehensive visibility into groups, replicas, listeners, and databases, as well as operational controls to perform safe or forced failovers and to suspend or resume data movement during maintenance—along with the caution that suspensions can cause primary transaction log growth until replication resumes.

Figure 17.1. Database is in recovery on destination instance

Figure 17.2. Enable AlwaysOn Availability Groups in the SQL Configuration Manager GUI

Figure 17.3. Availability Group running on containers in our lab

Figure 17.4. Data movement suspended for all the databases in our AG.

• If you have a WSFC handy run the New-DbaAvailabilityGroup command to set up an Availability Group.
  
• If not run through the notebooks to get an Availability Group set up running on containers.
  
• Once your AG is setup explore with Get-DbaAgReplica and Get-DbaAgDatabase
  
• Cause a failover with Invoke-DbaAgFailover
  
• Suspend data movement and then resume it with Suspend-DbaAgDbDataMovement and Resume-DbaAgDbDataMovement
  

FAQ