Change window open and you need the checklist or the rollback criteria now? Start Here goes straight to them. The rest is the paperwork either side of the window: how a change gets approved, proved safe before you start and undone if it goes wrong, which is what decides whether it goes ahead on a Tuesday or waits three weeks for another CAB. Every document is in the repo, ready to copy and edit, and deliberately boring, because a change order is not the place to be creative.
Start Here
The Four Stages of a Planned Change
They run in order, and each one exists because skipping it costs you something specific.
📋 Change Orders
Written before anything is touched, so approval is a decision rather than a formality.
- Change metadata, summary and a risk assessment
- Pre-change validation written as an explicit go or no-go
- Implementation steps someone else could follow
- Post-change validation, and the rollback plan agreed up front
✅ Checklists
Used during the window, when nobody is in a state to improvise.
- Step by step, tick as you go, no prose to read at 2am
- Separate paths for a planned DR test and an actual disaster
- Explicit return-to-primary steps, which is the part people forget
- Covers version upgrades, server replacement and AlwaysOn migration
📖 Runbooks
The full sequence for the big jobs, phased so the risky part is short.
- Pre-migration assessment run days before the window, not during it
- Target preparation, including matching configuration and TempDB
- Standalone, AG and cluster, OS upgrade, version upgrade, edition change
- Written to be handed to someone else
🔄 Rollback
Decided before the change, because mid-incident is the worst time to invent criteria.
- Who decides, and how long the rollback window stays open
- Trigger criteria written down in advance
- Different procedures for side-by-side versus in-place
- The point past which rolling back costs more than going forward
SQL Templates for Common Changes
Each one is a change record in SQL form: purpose, pre-checks, the statements, validation and rollback. Copy it, replace the placeholders, read it, run it, and the change documents itself. Pick the card for the job.
Most of these change the server. The TDE one creates a master key and certificate. Back up the certificate and private key straight away, the step people discover they skipped at the worst possible moment: MS Docs is plain that without those backups a restored copy cannot be opened.
🖥️ Pre-OS-Upgrade Readiness
Pre-OSUpgrade-Readiness.sql
Version, database settings, file sizes and recent backups, saved to the change record before the Windows team touches the box.
🩹 Patching
change-template-patching.sql
Version, sessions, running jobs and last backups before the CU. Version, services, databases online and the error log after.
📦 Installation
change-template-installation.sql
Disk and existing instances first. Then version, TempDB layout, TCP, Agent and key sp_configure settings. It turns on show advanced options.
🔐 Configure TDE
Configure-Tde-Template.sql
Master key, certificate, the certificate and private key backup, then encryption on. Run the master section first.
📝 Configure CDC
Configure-Cdc-Template.sql
Turns on Change Data Capture for a database and one table. Needs SQL Server Agent running and a primary key on the table.
🛡️ Availability Group
Configure-AlwaysOn-AvailabilityGroup-Template.sql
The change record plus checks of replica endpoints, availability mode and failover mode. It validates an AG, it does not create one.
🪞 Database Mirroring
Configure-Mirroring-Template.sql
The change record plus a check of mirroring state, partner, safety level and witness, for estates still running it.
💾 Restore with NORECOVERY
Restore-Database-NoRecovery-Template.sql
Full then log restore, left in NORECOVERY for log shipping or AG seeding. The shape most restores actually take. Uses REPLACE, so it overwrites a database of the same name.
✅ Consistency Check
Database-Consistency-Check-Template.sql
DBCC CHECKDB on one database with every error message, kept as evidence before an upgrade or migration.
📊 Update Statistics
Update-Statistics-Template.sql
Sampled statistics update on one table, after a big data change or an index change.
⚙️ Recompile a Procedure
Recompile-Procedure-Template.sql
sp_recompile on one procedure, when its cached plan went bad after a schema, index or statistics change.
Mirroring still works, but MS Docs lists it for removal in a future version. All 11 templates sit in the change-templates folder.
Capture What Changed, Before and After
A change order says what should change. Two scripts record what did, and they are the first thing to run when something behaves differently the morning after.
- Get Instance Configuration Snapshot, every sp_configure setting with its configured and running value and a pending_restart flag. Save it before the window and again after, and a setting nobody meant to touch shows up as a difference rather than a theory.
- Get Schema Change History, every CREATE, ALTER and DROP the default trace still holds, with the login, host and application behind it. Needs ALTER TRACE. The trace keeps a fixed volume, not a fixed period, so run it straight after the window rather than a week later.
Why This Matters
- A change that cannot be explained on paper does not get approved, and the delay is usually longer than the work
- Rollback criteria invented during an incident are decided by whoever is loudest, not by what the business agreed
- A runbook is how a migration stops depending on one person being awake and available
- The validation step is what turns “it finished” into “it worked”, and they are not the same claim
- Auditors ask for evidence of the process, not evidence of the outcome
Frequently Asked Questions
Is this overkill for a small environment?
Scale it down, but do not skip the rollback criteria. On a small estate you are usually the only person who understands the change, which makes writing it down more valuable rather than less. A one page change order is still a change order.
Our organisation has no CAB. Is a change order still worth writing?
Yes, and arguably more so. Without a review board the document is the review. Writing the pre-checks and the rollback plan forces the thinking that a board would otherwise force, and it gives you something to point at afterwards.
What is the difference between a checklist and a runbook here?
A runbook explains the whole job including the parts before and after the window. A checklist is what you actually hold during the window: short, ordered, no explanation. Reading a runbook at 2am is how steps get skipped.
When should rollback criteria be agreed?
Before the window opens, in the change order, with the person who owns the business impact. Criteria agreed during an incident are not criteria, they are opinions.
Do these replace the scripts on the rest of the site?
No, they wrap them. The scripts tell you the state of a server; these decide what to do about it, in what order, with whose approval, and how to undo it.
Summary
Change management is the least glamorous part of running SQL Server and the part that most often decides whether a change is remembered as routine or as an incident. The documents here are the boring version on purpose. Copy them, edit the placeholders, and keep the completed ones, because the finished change order is the only proof that the process happened.
The change orders above wrap the procedures below. Each one is a full runbook, start to finish.
- SQL Server Version Upgrade Runbook, in-place and side-by-side, with the validation that closes the window.
- SQL Server Migration Runbook: Standalone, moving a standalone instance to new hardware.
- SQL Server Migration Runbook: Availability Groups and Clusters, the same move with replicas and cluster roles in play.
- SQL Server Edition Change Runbook, Standard to Enterprise and back, and what a downgrade blocks.
- Windows Server OS Upgrade Runbook for SQL Server, the operating system underneath the instance.
Leave a Reply