Execute Restore, Failover, Corruption, Performance, and Upgrade Drills—and Defend the Final Design

Learning outcomes

The final lesson is an evidence defense, not a victory lap. A production design deserves trust only after controlled failures reveal what it actually does. ServiceHub will exercise blocking, recovery, corruption response, resource pressure, plan/compatibility regression and HA/cutover reasoning without damaging anything outside the disposable capstone. For every drill, record trigger, first detectable signal, containment, recovery, correctness proof, measured elapsed time, data-loss outcome, unresolved risk and the design change that follows.

1. Drill contract: facts before hypotheses

USE ServiceHubCapstone;GOIF OBJECT_ID(N'governance.DrillEvidence',N'U') IS NULLCREATE TABLE governance.DrillEvidence(  drill_id bigint IDENTITY PRIMARY KEY,  drill_name varchar(80) NOT NULL,  phase varchar(20) NOT NULL,  captured_at datetime2(3) NOT NULL DEFAULT SYSUTCDATETIME(),  fact nvarchar(1200) NOT NULL,  rpo_seconds decimal(18,3) NULL,  rto_seconds decimal(18,3) NULL,  passed bit NULL,  residual_risk nvarchar(1200) NULL);INSERT governance.DrillEvidence(drill_name,phase,fact)VALUES('blocking','PLAN',N'Two-session test; Session A holds row lock, Session B uses LOCK_TIMEOUT and records blocking evidence.');SELECT * FROM governance.DrillEvidence ORDER BY drill_id;

Use UTC timestamps and synchronized host/application clocks. “CPU was high around then” is a hypothesis until correlated with query, wait, plan, host and business evidence.

2. Safe blocking drill: two sessions, bounded timeout

USE ServiceHubCapstone;GOBEGIN TRAN;UPDATE ops.WorkOrder SET priority=priority WHERE work_order_id=(SELECT MIN(work_order_id) FROM ops.WorkOrder);WAITFOR DELAY '00:00:15';ROLLBACK;

USE ServiceHubCapstone;GOSET LOCK_TIMEOUT 5000;BEGIN TRY  UPDATE ops.WorkOrder SET priority=priority WHERE work_order_id=(SELECT MIN(work_order_id) FROM ops.WorkOrder);END TRYBEGIN CATCH  SELECT ERROR_NUMBER() AS error_number,ERROR_MESSAGE() AS error_message,SYSUTCDATETIME() AS detected_at;END CATCH;SET LOCK_TIMEOUT -1;GOSELECT r.session_id,r.status,r.wait_type,r.wait_time,r.blocking_session_id,t.textFROM sys.dm_exec_requests AS rCROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS tWHERE r.session_id<>@@SPID;

Expected symptom: Session B waits on a lock and may return lock-timeout error 1222 after five seconds. That is not a deadlock; a deadlock requires a wait cycle and produces different evidence, including xml_deadlock_report. The design response is workload-specific: shorter transactions, consistent access order, indexes, or isolation redesign—not “turn off locking.”

3. Recovery/corruption/resource drills: prove the response without unsafe damage

Missing backup dependency: attempt a restore only from an intentionally nonexistent path on the disposable drill target and verify the runbook classifies media-not-found without touching the source. Corruption: use CHECKDB plus a known prebuilt corrupted training database in a separately snapshotted environment; never manufacture corruption in a valuable database. Log/disk pressure: observe log usage and long transactions in a bounded lab; do not fill the filesystem. Encryption-key loss: inventory protector backups and rehearse restore using lab keys rather than deleting the only key.

USE ServiceHubCapstone;GOSELECT total_log_size_in_bytes/1048576.0 AS total_log_mb,       used_log_space_in_bytes/1048576.0 AS used_log_mb,       used_log_space_in_percentFROM sys.dm_db_log_space_usage;SELECT name,recovery_model_desc,log_reuse_wait_descFROM sys.databases WHERE name=DB_NAME();DBCC CHECKDB(N'ServiceHubCapstone') WITH NO_INFOMSGS;GOSELECT TOP(10) backup_finish_date,type,first_lsn,last_lsn,checkpoint_lsn,database_backup_lsn,has_backup_checksumsFROM msdb.dbo.backupsetWHERE database_name=N'ServiceHubCapstone'ORDER BY backup_finish_date DESC;

If a drill cannot be made safe and reversible on the available topology, write the exact preconditions and evidence plan instead of pretending it was executed. An architecture gap discovered before production is useful evidence.

4. Plan/upgrade drill: separate engine, compatibility and query changes

USE master;GOALTER DATABASE ServiceHubCapstone SET COMPATIBILITY_LEVEL = 160;GOUSE ServiceHubCapstone;EXEC ops.GetCustomerOrders @customer_code='CUST-HOT';EXEC ops.GetCustomerOrders @customer_code='CUST-00017';GOUSE master;ALTER DATABASE ServiceHubCapstone SET COMPATIBILITY_LEVEL = 170;GOUSE ServiceHubCapstone;EXEC ops.GetCustomerOrders @customer_code='CUST-HOT';EXEC ops.GetCustomerOrders @customer_code='CUST-00017';GOSELECT TOP(30) q.query_id,p.plan_id,p.is_forced_plan,rs.count_executions,rs.avg_duration,rs.avg_cpu_timeFROM sys.query_store_query AS qJOIN sys.query_store_plan AS p ON p.query_id=q.query_idJOIN sys.query_store_runtime_stats AS rs ON rs.plan_id=p.plan_idWHERE q.object_id=OBJECT_ID(N'ops.GetCustomerOrders')ORDER BY rs.last_execution_time DESC;

This does not simulate an engine downgrade. Compatibility is a database behavior gate. A real engine upgrade rollback must respect forward-only backup/storage boundaries and the side-by-side source-of-truth plan from Chapter 24.

5. Defend the final design with an acceptance matrix

USE ServiceHubCapstone;GOINSERT governance.DrillEvidence(drill_name,phase,fact,passed,residual_risk)VALUES('capstone','DEFENSE',N'Final architecture accepted only if local SLO, restore, security, integrity and operational gates are backed by recorded evidence.',NULL,N'Multi-node production HA still requires topology-specific failover/client-reconnect testing.');SELECT * FROM governance.DrillEvidence ORDER BY drill_id;GOUSE master;IF EXISTS(SELECT 1 FROM sys.server_event_sessions WHERE name=N'ServiceHubCapstone_Deadlocks')BEGIN  IF EXISTS(SELECT 1 FROM sys.dm_xe_sessions WHERE name=N'ServiceHubCapstone_Deadlocks')    ALTER EVENT SESSION ServiceHubCapstone_Deadlocks ON SERVER STATE=STOP;  DROP EVENT SESSION ServiceHubCapstone_Deadlocks ON SERVER;END;GOIF DB_ID(N'ServiceHubCapstone_RestoreDrill') IS NOT NULLBEGIN  ALTER DATABASE ServiceHubCapstone_RestoreDrill SET SINGLE_USER WITH ROLLBACK IMMEDIATE;  DROP DATABASE ServiceHubCapstone_RestoreDrill;END;IF DB_ID(N'ServiceHubCapstone') IS NOT NULLBEGIN  ALTER DATABASE ServiceHubCapstone SET SINGLE_USER WITH ROLLBACK IMMEDIATE;  DROP DATABASE ServiceHubCapstone;END;GO-- Backup files are intentionally NOT deleted here. Retention/deletion is an OS/storage policy action.

The final design is defensible when each retained feature has a measurable reason, each rejected feature has a documented tradeoff, every recovery claim has drill evidence, and unresolved topology/operational risks have named owners. That is the end-state of the course: not memorizing SQL Server switches, but operating the platform as a measurable system.

Authoritative references

• SQL Server 2025 build versions
• Query Store usage scenarios
• Troubleshoot deadlocks with Extended Events
• DBCC CHECKDB
• Upgrade SQL Server