SQL Server migration cutover runbook: minimal downtime, go/no-go and rollback

#1. T-minus checklist

#2. Cutover steps (with times recorded)

1. Stop the application (or put it in maintenance mode). Stop SQL Agent jobs that write.
2. Confirm no writes: SELECT * FROM sys.dm_exec_sessions WHERE is_user_process = 1 shows nothing from apps.
3. Final sync, depending on the method:
   • MI link / AG: wait for synchronised, then fail over (planned, no data loss).
   • LRS / log shipping: take a final tail-log backup (BACKUP LOG ... WITH NORECOVERY keeps the source in restoring state, which protects against accidental writes), restore it, recover.
   • AWS DMS / CDC: wait for CDC latency 0, then stop the task.
   • Backup/restore: final differential or log, then restore WITH RECOVERY.
4. Row counts and checksums on key tables (source vs target).
5. Switch connections: update the DNS alias / connection strings / Key Vault or Secrets Manager values.
6. Start the app against the target; enable jobs on the target.
7. Smoke tests by app owners: login, read, write, a report, a batch job.

-- quick row count comparison (run on both sides)
SELECT s.name + '.' + t.name AS tbl, SUM(p.rows) AS rows
FROM sys.tables t JOIN sys.schemas s ON t.schema_id = s.schema_id
JOIN sys.partitions p ON t.object_id = p.object_id AND p.index_id IN (0,1)
GROUP BY s.name, t.name ORDER BY tbl;

#3. Go/no-go points

#4. Rollback

Plan this before the night:

• Before the app writes to the target: just point back to the source and start it. (This is why the source stays intact and read-only.)
• After writes to the target: you need reverse sync (MI link with SQL Server 2022 supports failing back; AWS DMS can run a reverse task; otherwise export the changed data). Decide the "point of no return" time with the business.

#5. After cutover

• Hypercare for 1–2 weeks: daily performance checks, Query Store regressions (see the validation post).
• Backups on the target verified (restore test).
• Source kept read-only or offline for an agreed period, then decommissioned and its licences released.

Tip

Rehearse with production-sized data. The rehearsal gives you real timings, and almost always finds a missing login, job or firewall rule.