Migration Dashboard

SQL Server 2016 Enterprise to 2022 Enterprise - interactive runbook with discovery, cutover, validation, and rollback scripts.

Excel export: Use Export Excel for a formatted multi-sheet workbook (frozen headers, AutoFilter, color-coded Done/Priority/Risk, column widths, landscape print). Downloads a real .xlsx workbook (Open XML) that Excel opens natively. Progress checkboxes in this HTML are included as Done = Y/N.
Used in migration summary report
Overall Progress0%
0 / 0 steps complete

Why migrate off SQL Server 2016?

SQL Server 2016 reached end of extended support on July 14, 2026. After that date Microsoft no longer provides security updates, cumulative updates, or standard product support for 2016. Continuing to run unsupported database engines is a business, compliance, and operational risk - not just a technical preference.

Why it is crucial to move ASAP:
  • No security patches - newly discovered CVEs will not be fixed on 2016; exposed instances become permanent attack targets.
  • Compliance / audit failure - many frameworks (ISO, SOC2, PCI, HIPAA, internal IT policy) require supported platforms; auditors flag EOL databases.
  • No Microsoft support for Sev-A incidents - production outages may leave you without vendor escalation paths.
  • Insurance and cyber coverage risk - some policies limit payouts when known-unsupported software is in use.
  • Vendor / application pressure - ISVs certify newer SQL versions only; delayed migration blocks app upgrades.
  • Skills and tooling decay - monitoring, drivers, and SSMS features increasingly assume 2019/2022+.
What happens if you do not migrate soon? Short term you may still run fine. Medium term you accumulate unpatched risk and fail audits. Long term you face forced emergency migration under incident pressure (ransomware, compliance deadline, hardware failure) with no rollback runway and no vendor help - the most expensive and risky way to upgrade.

Migration strategy - choose based on risk, downtime, and rollback needs

Do not pick a method only because it is popular. Match the approach to RTO/RPO, database size, HA topology, application certification, and how easily you must roll back. Microsoft supports a direct upgrade path from SQL Server 2016 SP3+ to 2022, but production teams often prefer side-by-side for safer rollback.

A. Side-by-side + backup/restore

Downtime: medium • Rollback: easy • Risk: low-medium
  • Build new Windows + SQL 2022; restore databases; rematch logins.
  • Best for: most Enterprise production when downtime window is acceptable.
  • Rollback: point apps back to 2016 instance.

B. Side-by-side + log shipping

Downtime: low • Rollback: easy • Risk: low
  • Keep 2022 secondary applying log backups until cutover.
  • Best for: large databases needing short cutover.
  • Rollback: leave 2016 primary authoritative if go-live fails.

C. Always On AG migration

Downtime: very low • Rollback: moderate • Risk: medium
  • Add 2022 replicas / rebuild AG; failover listener at cutover.
  • Best for: existing AG estates that must stay highly available.
  • Requires careful WSFC/endpoint/listener redesign on 2022.

D. In-place upgrade

Downtime: one window • Rollback: hard • Risk: medium-high
  • Setup upgrades the same instance (requires 2016 SP3+).
  • Best for: non-prod, small systems, strict hardware reuse.
  • Rollback is not supported by setup - rely on VM snapshot / rebuild.

E. Replication / CDC sync cutover

Downtime: low • Rollback: moderate • Risk: medium
  • Stream changes to 2022; cut over when lag is inside SLA.
  • Best for: very large DBs or selective database migration.
  • Higher operational complexity; validate identity and DDL carefully.

F. Azure MI Link / hybrid migrate

Downtime: varies • Rollback: varies • Risk: low-medium
  • Use Managed Instance link or DMA-style migrate for cloud DR/exit.
  • Best for: hybrid strategy or future Azure landing zone.
  • Still requires driver, cert, and app certification work.

How to decide: If rollback must be fast and you can build new hardware/VMs, prefer A or B. If you already run Always On and need near-zero downtime, evaluate C. Use D only when downtime is approved and a tested full-instance restore/snapshot exists. Use E/F for scale or cloud goals after a successful UAT. Full plan: sql_server/docs/sql_2016_to_2022_migration_plan.md.

Key Changes That May Affect Migration (2016 to 2022)

Click a row to expand. Blue underlined script names are clickable links (open SQL in viewer). Tags: Blocker must fix before/at cutover   Behavior plan/CE risk   Feature optional adopt   Ops config/HA

Myth check (common “breaks in 2022” lists): SQL Agent T-SQL job steps are not removed. Remote Admin Connections / DAC are not removed. RAISERROR with formatting still works (prefer THROW for new code). What is discontinued: ActiveX Agent steps, PolyBase Hadoop, Stretch DB.
Removed / will fail:
  • ActiveX Scripting SQL Agent job steps — convert to CmdExec or PowerShell (not T-SQL steps).
  • PolyBase Hadoop/HDFS external data sources — recreate with S3/Azure/supported connectors.
  • Stretch Database — unstretch before migration.
Not removed (ignore bad checklists):
  • SQL Agent T-SQL job steps — still fully supported.
  • Remote admin connections / DAC — still available (sp_configure 'remote admin connections').
  • RAISERROR printf-style formatting — still works; Microsoft prefers THROW for new development.
Changed behaviors (test in UAT):
  • Cardinality Estimator / plans when raising compat (130→150/160).
  • TempDB metadata concurrency (evaluate memory-optimized TempDB metadata).
  • Batch mode on rowstore / memory grants under newer compat + IQP.
  • String truncation / ANSI_WARNINGS — apps relying on silent truncation can fail.
  • TRUSTWORTHY / cross-db chaining — still supported; treat as security review, not automatic break.
  • Trace flags — re-validate; many legacy flags are obsolete on 2022.
  • New database / catalog collation choices at install (UTF-8 options).
DB / DBA actions:
What breaks in the field: tools around SQL often lag the engine upgrade.
  • Monitoring — old agents may use deprecated DMVs → upgrade to SQL 2022-supported build.
  • Backup software — may not understand 2022 backup formats → vendor-check + prove restore on UAT.
  • ORM frameworks — may emit deprecated SQL → upgrade ORM and regression-test generated queries.
  • SSIS 2016 — SNAC/connection issues → upgrade SSIS to 2022; update connection managers to ODBC 18 / OLE DB 19.
  • Replication to older versions — not a durable topology → upgrade/re-home subscribers before 2022 publisher cutover.
  • Linked servers to 2008/2012 — TLS/provider issues → OLE DB 19 / ODBC 18 updates and explicit UAT tests (or retire links).
DB / DBA actions:
What changed: SQLNCLI11 and legacy SQLOLEDB are not shipped with SQL Server 2022 or SSMS 19+. Apps, SSIS, and linked servers using these providers can fail to connect after cutover.
DB / DBA actions:
  • Inventory linked servers and SSIS connection managers for SQLNCLI / SQLOLEDB.
  • Replace with Microsoft ODBC Driver 18 or OLE DB Driver 19 on all app/ETL tiers.
  • Recreate linked servers with the new provider; retest TLS 1.2+ connectivity.
  • Script: 09_linked_servers_and_jobs.sql
What changed: Stretch Database was discontinued (July 2024). Stretched tables block a clean upgrade path and must be returned on-premises first.
DB / DBA actions:
  • Scan for is_remote_data_archive_enabled tables before migration.
  • Unstretch / migrate remote data back to local tables; drop Stretch config.
  • Re-validate table sizes and backup windows after unstretch.
  • Script: 11_stretch_database_check.sql
What changed: Hadoop/HDFS external data sources and PolyBase scale-out groups are not supported in SQL Server 2022. Scale-up PolyBase and S3/Azure connectors remain.
DB / DBA actions:
  • List external data sources / tables of type Hadoop.
  • Recreate external data sources with supported connectors (S3-compatible, Azure, Oracle, etc.).
  • Rebuild dependent external tables and refresh SSIS/ETL packages.
  • Script: 12_polybase_hadoop_check.sql
What changed: Migrated DBs often stay at compat 130 on the 2022 engine until you change them. Raising to 140/150/160 enables newer CE and Intelligent Query Processing, which can improve or regress plans. Jumping 130 → 160 is a top community-reported regression source.
DB / DBA actions:
  • Cut over at current compat (usually 130) and hold 7-14 days (Option A backout) unless UAT signed off higher.
  • Enable Query Store first, baseline, then stage 140/150, then 160.
  • For confirmed CE regressions: temporary LEGACY_CARDINALITY_ESTIMATION = ON (database scoped) while remediating.
  • You cannot restore a 2022 database back to 2016 — prefer compat/scoped-config rollback over physical failback.
  • Scripts: 19_query_store_enable_and_status.sql 20_compatibility_level_report.sql 21_rollback_evidence_capture.sql
What changed: SQL Server 2017-2022 added adaptive joins, batch mode on rowstore, Parameter Sensitive Plan (PSP), DOP feedback, CE feedback, and persistent memory grant feedback. Behavior depends on compat level and Query Store. Early CUs had PSP edge-case bugs (CPU spikes / timeouts).
DB / DBA actions:
  • Never run production on RTM-only — install the latest CU before UAT and cutover.
  • Do not enable all IQP features on day one in prod without UAT.
  • After Query Store baseline and compat raise, evaluate DOP_FEEDBACK / plan forcing carefully.
  • Watch for sudden plan flips on parameterized queries (PSP); capture scoped configs with evidence script.
  • Related scripts: 19_query_store_enable_and_status.sql 21_rollback_evidence_capture.sql
What changed: New databases on 2022 get Query Store ON by default. Restored/upgraded databases retain prior Query Store settings (often OFF on 2016).
DB / DBA actions:
  • Explicitly enable Query Store (READ_WRITE) on all migrated user databases before compat changes.
  • Size capture policy appropriately (AUTO or CUSTOM) to control overhead.
  • Use Query Store for day-1 regression detection and rollback of bad plans.
  • Script: 19_query_store_enable_and_status.sql
What changed: SQL Server 2022 supports TDS 8.0 (encryption-aligned) and TLS 1.3. Legacy clients, SSL inspection appliances, or old drivers may fail or negotiate poorly.
DB / DBA actions:
What changed: TDE continues, but side-by-side restore fails without the correct certificates and service master key hierarchy on the target.
DB / DBA actions:
What changed: Side-by-side restore does not automatically rematch SQL logins by SID. Orphaned users and broken jobs/proxies are common cutover failures.
DB / DBA actions:
What changed: AG topology does not transfer unchanged. 2022 adds Contained AGs, DAG network improvements, and Query Store on readable secondaries. Legacy mirroring should move to AG.
DB / DBA actions:
  • Inventory AG/mirroring/log shipping/replication on SOURCE.
  • Rebuild endpoints, listeners, seeding, and jobs on 2022 WSFC/AG.
  • Validate sync health and RPO before and after cutover.
  • Scripts: 06_ha_dr_topology.sql 22_ag_health_check.sql
What changed: ADR (2019+, improved in 2022) speeds crash recovery and long-transaction rollback via persistent version store (PVS).
DB / DBA actions:
  • Plan PVS capacity before enable — in the primary filegroup or redirected to a dedicated filegroup.
  • Optional post-migration enable per database after validating disk for PVS growth.
  • Monitor version store size and cleanup; do not enable blindly on all DBs day one.
  • Useful for databases with long-running transactions or slow recovery times.
  • Post-cutover health: 18_post_migration_validation.sql
What changed: 2022 improves GAM/SGAM latch concurrency, VLF creation algorithm, and allows Instant File Initialization for transaction log growth up to 64 MB. Memory-optimized TempDB metadata (2019+) remains valuable for latch contention.
DB / DBA actions:
  • Pre-size TempDB with equal files (often up to 8, then multiples of 4); avoid tiny autogrowth.
  • On 2022, evaluate MEMORY_OPTIMIZED TEMPDB_METADATA = ON (restart required) for latch-heavy workloads.
  • Grant Perform volume maintenance tasks (IFI) to SQL service account.
  • Monitor dm_db_log_info and TempDB waits after cutover.
  • Scripts: 02_server_configuration.sql 15_post_install_config_audit.sql
What changed: Setup recommends max memory differently; SQL service set to Automatic may actually use Automatic (Delayed Start). Old 2016 MAXDOP/CTFP/trace flags may be wrong on new hardware/NUMA.
DB / DBA actions:
What changed: R/Python/Java runtimes are no longer installed by SQL Server 2022 setup. Existing sp_execute_external_script workloads break until runtimes are installed separately.
DB / DBA actions:
What changed: Features remain supported but do not auto-rebuild on a new instance. Agent jobs, distributors, and watermarks must be planned for cutover.
DB / DBA actions:
What changed: SQL Server 2022 extends BACKUP/RESTORE TO/FROM URL with an S3 connector via REST API, in addition to Azure Blob.
DB / DBA actions:
  • Optional: redesign backup targets to S3-compatible storage after cutover is stable.
  • Do not change backup strategy on cutover night unless pre-tested.
  • Always verify first full backup + restore test on 2022.
  • Script: 14_backup_chain_verification.sql
What changed: SQL Server 2022 adds Microsoft Entra authentication, Ledger, improved Always Encrypted enclaves, granular UNMASK, and new least-privilege server roles.
DB / DBA actions:
  • Do not require these for cutover success; adopt after stabilization.
  • If Always Encrypted enclaves are already in use, validate host/driver versions on 2022.
  • Plan least-privilege role cleanup after go-live (reduce sprawling sysadmin).
  • Baseline security inventory: 07_logins_and_server_roles.sql 02_server_configuration.sql

Rollback Decision Tree

Use after evidence capture (21_rollback_evidence_capture.sql). Prefer Option A (compat / scoped config) before connection or physical failback. You cannot restore a 2022 database to 2016.

Performance / app issue after cutover? | v Optimizer rollback (Option A) LEGACY_CARDINALITY_ESTIMATION / hold compat 130 | v Still bad? | v Connection rollback to 2016 SOURCE (only if no critical writes OR Option B catching them) | v Writes occurred on 2022? | +-- No --> Keep 2016 authoritative; freeze 2022 for RCA | +-- Yes --> Reverse replication / ETL planned (Option B)? | +-- Yes --> Physical failback path | +-- No --> Manual reconciliation / incident

Quick Links - Post-Migration Troubleshooting

Click any link to open the embedded SQL in the script viewer (works offline from this HTML / unzipped package).

📋 Initiation

🔍 Discovery

🛠 Target Design

🚀 Build Target

🔬 UAT Pilot

📝 Prod Prep

⚡ Cutover

📈 Stabilization

⏪ Rollback

Script Library

All scripts are embedded in this HTML. Click a name to view/copy. Loose .sql files are also in the zip under scripts/ for SSMS.

Migration scripts (2016 to 2022)

Post-migration troubleshooting (Prod_Migration)