A data center loses power at 2:47 on a Tuesday morning. The on-call DBA has an Always On availability group with an asynchronous secondary forty miles away, a runbook in SharePoint that nobody has opened in fourteen months, and no idea who holds the authority to say the word "disaster." The failover command takes ninety seconds to run. Reaching the decision to run it takes four hours. (Composite, details changed. The shape of it repeats.)
That ratio is the subject. The technology worked. What failed was a document written once for an audit, never rehearsed, and written by the person who built the system for an audience of the person who built the system. The test of a runbook is whether the engineer who has never touched this cluster can execute it at 3am without calling anyone.
What a runbook contains
A runbook that survives contact with 3am contains eight things. Most documents I am handed have three of them.
1. Declaration authority. Named people, in order, with phone numbers. "The VP of Engineering, or in their absence the Director of Infrastructure, declares a disaster." Include the criteria: primary site unreachable for more than 20 minutes with no ETA from the facility. Without this your outage is a conference call.
2. Roles. One person runs commands, one communicates with the business, one validates.
3. Current topology. Server, instance and listener names, IP addresses per subnet, replica roles, availability modes, witness type and location, backup share paths. Regenerate it from the servers on a schedule rather than typing it, because a hand-maintained topology section is wrong within two change windows and nobody notices until the night it matters. Run this on the primary and paste the output:
SELECT
ag.name AS availability_group,
ag.cluster_type_desc,
ag.is_contained,
ag.automated_backup_preference_desc,
ag.required_synchronized_secondaries_to_commit,
agl.dns_name AS listener,
agl.port,
agl.ip_configuration_string_from_cluster,
ar.replica_server_name,
ars.role_desc,
ar.availability_mode_desc,
ar.failover_mode_desc,
ar.session_timeout,
ar.seeding_mode_desc,
ar.secondary_role_allow_connections_desc,
ar.read_only_routing_url,
ar.backup_priority,
ars.operational_state_desc,
ars.connected_state_desc,
ars.synchronization_health_desc
FROM sys.availability_groups AS ag
JOIN sys.availability_replicas AS ar
ON ar.group_id = ag.group_id
JOIN sys.dm_hadr_availability_replica_states AS ars
ON ars.replica_id = ar.replica_id
LEFT JOIN sys.availability_group_listeners AS agl
ON agl.group_id = ag.group_id
ORDER BY ag.name, ars.role_desc DESC, ar.replica_server_name;
cluster_type_desc tells you whether there is a WSFC underneath at all, which decides whether automatic failover is even on the table. session_timeout and required_synchronized_secondaries_to_commit are the two settings that determine how the primary behaves when a secondary stops answering, and both belong in the document next to the failover commands rather than in somebody's memory. The witness and its location come from Get-ClusterQuorum on the cluster side; paste that output too.
4. The commands, in order, ready to paste. Not "fail over the availability group." The statement:
-- Run on the DR replica, connected to master.
-- Planned failover, both replicas up, synchronous and SYNCHRONIZED:
ALTER AVAILABILITY GROUP [AG1] FAILOVER;
-- Primary is gone, secondary is asynchronous. Accepts data loss:
ALTER AVAILABILITY GROUP [AG1] FORCE_FAILOVER_ALLOW_DATA_LOSS;
For a restore-based recovery, spell out the tail-log step and take it whenever the primary is reachable at all. On an online database you plan to restore over, WITH NORECOVERY puts it in the restoring state and stops further changes. On a database that is offline and will not start, WITH NO_TRUNCATE. On a damaged database, WITH CONTINUE_AFTER_ERROR, and nowhere else. Take the tail and you can drop REPLACE from the full restore, which is what you want: REPLACE overrides the safety check that asks for the tail in the first place.
-- 1. Tail of the log, if the primary is reachable at all.
BACKUP LOG [Sales]
TO DISK = N'\\backup01\sql\Sales_tail.trn'
WITH NORECOVERY, CHECKSUM; -- NO_TRUNCATE if the database is offline
-- 2. Full, then the most recent differential, then every log in order.
RESTORE DATABASE [Sales] FROM DISK = N'\\backup01\sql\Sales_full.bak'
WITH NORECOVERY, CHECKSUM;
RESTORE DATABASE [Sales] FROM DISK = N'\\backup01\sql\Sales_diff.bak'
WITH NORECOVERY, CHECKSUM;
RESTORE LOG [Sales] FROM DISK = N'\\backup01\sql\Sales_log_0215.trn'
WITH NORECOVERY, CHECKSUM;
RESTORE LOG [Sales] FROM DISK = N'\\backup01\sql\Sales_tail.trn'
WITH RECOVERY, CHECKSUM, STOPAT = '2026-09-17 02:44:00';
An availability group is not the only shape this takes. A failover cluster instance moves with the cluster rather than with SQL Server, so the command belongs to Windows: Move-ClusterGroup -Name "SQL Server (INST1)" -Node DRNODE01. A log shipping secondary has no failover command at all. You restore whatever copied log backups the secondary has not applied, then the tail of the log from the primary if you can reach it, then bring the database up with WITH RECOVERY using the sequence above. Write the variant you own. Writing all three because you might someday own all three is how runbooks become unusable.
5. How applications reach the new primary. Three mechanisms, and you need to know which one each application uses:
- The listener. The connection string never changes and the WSFC moves the name and IP. Across subnets the listener holds an IP in each, and clients need
MultiSubnetFailover=Trueto try them in parallel. Without it,RegisterAllProvidersIPmakes legacy clients walk every IP in turn and time out. It defaults to 1 when the listener is created with SSMS, T-SQL or PowerShell and to 0 when somebody built it in Failover Cluster Manager, so read the value rather than assuming it. The workaround setsRegisterAllProvidersIPto 0 and shortensHostRecordTTLfrom its 20 minute default:Get-ClusterResource yourListenerName | Set-ClusterParameter HostRecordTTL 300. That trades failover speed for compatibility, and the listener resource needs a restart before it takes effect. - A CNAME you control. Applications connect to
sql-sales.contoso.comand you repoint the record. Reduce the TTL ahead of time, as a runbook step: 3600 seconds means an hour of clients holding the old address. - Connection strings in config files. Every one, with its file path and restart procedure. During a real failover somebody finds a fourth service nobody knew about.
6. Logins, jobs, and the rest of the instance. An availability group replicates databases. Through SQL Server 2019 it leaves server-level logins, SQL Agent jobs, linked servers, credentials, and server configuration behind. Script them to the secondary on a schedule. SQL Server 2022 added contained availability groups, which give the group its own master and msdb and replicate that metadata, at the cost of replication, log shipping targets, SSIS packages, and maintenance plans inside the group.
7. Validation. Row counts against known tables, a login test from an application server, a smoke transaction end to end, with the queries written in. Write them against business facts rather than server health: "the most recent order in dbo.Orders is within the RPO window" is a check the business can hear, and SELECT @@SERVERNAME is not. Include the expected answer next to each query, because at 4am nobody remembers whether 812,441 rows is the right number.
-- Paste the expected shape next to each check.
SELECT TOP (1) OrderID, OrderDate
FROM dbo.Orders
ORDER BY OrderID DESC; -- expect OrderDate within the agreed RPO
SELECT DATABASEPROPERTYEX(N'Sales', 'Status') AS db_status, -- expect ONLINE
DATABASEPROPERTYEX(N'Sales', 'Updateability') AS updateability; -- expect READ_WRITE
8. Failback. More disruptive than the failover as a rule, and the part nobody writes down. Returning to the original site means seeding in the opposite direction, a second outage window, and a second set of application repointing steps. Write them now, while nothing is on fire.
What a DR test means
"We tested DR" covers three activities worth very different amounts.
A tabletop walkthrough puts the team in a room with the runbook and talks through the outage. It finds missing phone numbers, unclear authority, and steps that assume knowledge one person holds. Cheap, quarterly, and not a test.
A partial test fails over one AG during a maintenance window and fails it back. It proves the command works and the listener moves, and proves nothing about the applications, because you never pointed them anywhere new.
A real test brings DR up with production applications pointed at it, runs business transactions through it, and holds it there long enough to matter. Once a year at minimum, twice for anything with an RTO under four hours. Run it against the runbook rather than your memory: hand the document to an engineer who did not build the system and let them execute it. Every place they stop and ask a question is a defect in the document.
Record three numbers each time. Minutes from the start of the simulated outage to the declaration, minutes from declaration to the database accepting connections, minutes to the first successful business transaction. Compare them to the RPO and RTO you agreed, and take any gap back to the business with evidence instead of both sides guessing.
The order to build and rehearse a runbook in
- Name the declaration authority and their deputy, in writing, with the criteria that let them declare and the phone numbers that reach them.
- Generate the topology section from the servers rather than typing it, and put the generator on a schedule.
- Write the commands out in full, in execution order, with the failover and restore variants separated and labelled.
- Inventory how every application reaches the database: listener, CNAME, or a connection string in a file somebody owns.
- Script the instance-level objects an AG leaves behind and confirm the script runs on the DR side.
- Write the validation queries and the failback steps.
- Tabletop it with the team and fix what they cannot answer.
- Run a full test with applications pointed at DR. Time it, compare it to the committed numbers.
- Take the gap back to the business and let them decide whether to spend more or relax the target.
- Repeat step 8 every year, and every time the topology changes.
Steps 1 and 8 carry the weight. The first decides whether anyone can start the recovery, and the last is the only thing that proves the other nine describe reality.
The numbers this document is measured against, and the choice between availability groups, failover cluster instances and log shipping that determines what the commands in step 3 even look like, come from the other side of this work: setting RPO and RTO and choosing the technology that meets them.
If you would rather hand this off, it is the kind of work I do. See SQL Server DBA services, review client results, or get in touch.