End of Support Migrations Away from SQL Server 2016: The Field Guide for Production DBAs

SQL Server 2016 was a big release. It introduced Query Store, Operational Analytics, Temporal Tables, Native JSON parsing, and dynamic data masking. For many infrastructure teams, it served as the rock-solid, set it and forget it enterprise platform for nearly a decade.

With the retirement of its Extended Support phase, running production instances on SQL Server 2016 transitions from an operational liability issue to an immediate security and compliance concern. Out-of-support database engines no longer receive zero-day security fixes, regulatory compliance certifications (such as PCI-DSS, SOC 2, and HIPAA) automatically fail audits, and modern client tools gradually phase out backward connectivity.

Migrating legacy instances to modern target platforms—such as SQL Server 2022, SQL Server 2025, or Azure SQL Managed Instance—is not a simple in-place upgrade. In these newer versions, the basic beliefs or rules a system used to find the best solution have completely changed, security protocols are tighter, and high-availability setups have new requirements.

Here is a practical, production-tested migration roadmap designed to bring critical databases off SQL Server 2016 cleanly without performance slowdowns or extended downtime.

Choosing Your Target: Architecture Decision Framework

Before scheduling downtime or provisioning VMs, evaluate where your workloads should land based on architecture, operational overhead, and licensing.

Target Platform	Best Fit For	Primary Operational Hurdle	Licensing & Cost Impact
SQL Server 2022 / 2025 (IaaS / On-Prem)	Highly customized OS setups, heavy SSIS/SSRS reliance, cross-database distributed queries.	Host-level patching, manual Windows Server failover cluster maintenance.	Perpetual core licensing or Azure Hybrid Benefit (AHB) with Software Assurance.
Azure SQL Managed Instance (PaaS)	Full T-SQL surface area requirement, native cross-database joins, automated backups, zero OS maintenance.	Network peering complexity (ExpressRoute/VPN), network delay for chatty legacy client applications.	Predictable vCore subscription pricing; eliminates Windows and SQL maintenance overhead.
Azure SQL Database (PaaS)	Green-field microservices, single-tenant SaaS architectures, modern cloud-native apps.	No cross-database queries (USE [db] unsupported), no SQL Agent, no linked servers.	Elastic pools or serverless compute tiers.

For most enterprise teams managing stateful on-premises or co-located databases with deep third-party dependencies, standardizing on SQL Server 2022 or 2025 Enterprise/Standard editions running on modern Windows Server or enterprise Linux remains the primary target.

Pre-Migration Baseline & Discovery Phase

The main rule of database migrations: Never migrate an unstandardized database. If you do not record query execution performance from SQL Server 2016 before the migration, resolving a post-migration slowdown becomes a guessing game.

Enabling Query Store on Legacy 2016 Workloads

If Query Store is not already running on your SQL Server 2016 production instances, turn it on at least two to four weeks prior to migration to capture workload cycles, end-of-month reporting batches, and peak performance numbers:

USE [master];
GO

ALTER DATABASE [Sales_Production]
SET QUERY_STORE = ON 
(
    OPERATION_MODE = READ_WRITE,
    CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),
    DATA_FLUSH_INTERVAL_SECONDS = 900,
    MAX_STORAGE_SIZE_MB = 2048,
    INTERVAL_LENGTH_MINUTES = 60,
    SIZE_BASED_CLEANUP_MODE = AUTO,
    QUERY_CAPTURE_MODE = AUTO
);
GO

Auditing Deprecated Features and Orphaned Logins

Audit your legacy server for configurations that break or behave differently on SQL Server 2022/2025:

• TLS 1.0/1.1 and Legacy Ciphers: SQL Server 2016 originally shipped when TLS 1.0 and 1.1 were common. Modern SQL Server editions enforce TLS 1.2 or TLS 1.3 by default. Verify that legacy application servers connecting to the database support modern secure handshakes; otherwise, connections will fail before reaching authentication.
• Network Protocol & Encryption Defaults: Client connections to SQL Server 2022 and 2025 default to mandatory encryption (Encrypt=Mandatory, or Encrypt=Strict with TDS 8.0). If certificates are self-signed or unverified, older connection strings without TrustServerCertificate=True will immediately terminate.
• Orphaned SIDs: Capture logins, server-level permissions, and job schedules ahead of time using community tools like dbatools (Export-DbaUser, Export-DbaLogin) to preserve exact password hashes and security identifiers (SIDs).

The Main Problem: Cardinality Estimator (CE) Shifts

The single biggest technical hurdle during a SQL Server 2016 migration is how the query engine estimates work.

SQL Server 2016 ran primarily under Compatibility Level 130, which by default used the New Cardinality Estimator (introduced in SQL Server 2014) or was deliberately pinned to the legacy 70 CE to avoid performance slowdowns. Upgrading directly to SQL Server 2022 (Compatibility Level 160) or SQL Server 2025 (Compatibility Level 170) introduces multiple layers of rewriting the code of a software optimizer to make it cleaner, faster, and easier to maintain without changing how it actually works:

1. Correlation vs. Independence Assumptions: The older engine assumed column filters were completely independent; the modern engine assumes partial correlation. Queries using complex multi-table joins on unevenly distributed data can produce completely different row estimates, causing the engine to choose poor execution paths.
2. Ascending Key / Out-of-Bounds Stats: The modern engine handles auto-incrementing primary keys better, but queries with table variables or complex multi-statement table-valued functions (MSTVFs) can still run into bad execution plans.

The Phased Compatibility Level Strategy

Do not change the database compatibility level on the day of the migration cutover. Follow this phased stabilization plan:

Step 1: Migrate Database -> Keep Compatibility Level 130 -> Validate Operations (Week 1-2)
│
Step 2: Upgrade Compatibility Level to 160/170 + Enable Legacy CE Scoped Config -> (Week 3)
│
Step 3: Gradually Transition to Modern CE via Query Store Validation -> (Week 4+)

Execute this configuration script post-cutover:

USE [master];
GO

-- 1. Attach/Restore database to new SQL Server 2022/2025 instance
-- 2. Keep the database at level 130 during initial cutover verification
ALTER DATABASE [Sales_Production] SET COMPATIBILITY_LEVEL = 130;
GO

-- 3. Verify Query Store is active and retained across migration
ALTER DATABASE [Sales_Production] SET QUERY_STORE = ON;
GO

When you are ready to enable modern engine features without destabilizing execution plans, raise the compatibility level while holding the query optimizer to legacy assumptions using database-scoped configurations:

USE [Sales_Production];
GO

-- Raise compatibility to modern engine to unlock modern features
ALTER DATABASE [Sales_Production] SET COMPATIBILITY_LEVEL = 160;
GO

-- Shield complex queries by enforcing Legacy Cardinality Estimation instance-wide
ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON;
GO

-- Enable parameter sensitive plan optimization
ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SENSITIVE_PLAN_OPTIMIZATION = ON;
GO

This hybrid state gives your database engine access to memory grant feedback, automatic tuning, and parallel query improvements while preventing major query plan slowdowns caused by new row-count estimation formulas.

Step-by-Step Production Cutover Runbook

Minimizing downtime on multi-terabyte production databases requires avoiding standard backup-and-restore pauses. Use Log Shipping or Distributed Availability Groups to pre-seed the target environment.

Pre-Seeding (Days Before Cutover)

1. Restore a full database backup on the target SQL Server 2022/2025 instance with NORECOVERY.
2. Apply all transactional log backups sequentially, keeping the database in STANDBY or NORECOVERY.
3. Script and deploy all server-level items: logins, server triggers, linked servers, database mail profiles, and SQL Server Agent jobs. Ensure newly deployed SQL Agent jobs are set to Disabled on the target server.

Cutover Maintenance Window

1. Stop Application Traffic: Stop application pools, container services, and worker queues to prevent new data writes.
2. Execute Final Tail-Log Backup on SQL Server 2016:

USE [master];
GO
-- Take database offline to guarantee zero active transactions
ALTER DATABASE [Sales_Production] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO

BACKUP LOG [Sales_Production] 
TO DISK = N'\\StorageShare\Backups\Sales_Production_TailLog.trn' 
WITH NORECOVERY, FORMAT, COMPRESSION, STATS = 10;
GO

3. Restore Tail-Log and Recover on Target Instance:

USE [master];
GO

RESTORE LOG [Sales_Production] 
FROM DISK = N'\\StorageShare\Backups\Sales_Production_TailLog.trn' 
WITH RECOVERY, STATS = 10;
GO

4. Update System Statistics with Full Scan: Because internal statistics layouts change across engine versions, always rebuild statistics immediately following the cutover:

USE [Sales_Production];
GO

-- Perform a clean full-scan statistics rebuild across all tables
EXEC sp_updatestats;
GO

5. Enable SQL Agent Jobs and Redirect Connections: Repoint DNS aliases, connection strings, or load-balancer listeners to the new host.

Post-Migration Monitoring & Plan Remediation

Within the first 72 hours post-cutover, monitor the new instance for slow queries and wait-state discrepancies.

Rapid Regressed Plan Detection via Query Store

Execute this diagnostic query to spot queries whose total CPU or duration increased significantly compared to the baseline:

USE [Sales_Production];
GO

SELECT TOP (10)
    q.query_id,
    qt.query_sql_text,
    p.plan_id,
    rs.avg_duration / 1000.0 AS [AvgDuration_ms],
    rs.avg_cpu_time / 1000.0 AS [AvgCPU_ms],
    rs.count_executions AS [ExecutionCount],
    rs.last_execution_time
FROM sys.query_store_query q
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
JOIN sys.query_store_plan p ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id
WHERE rs.last_execution_time >= DATEADD(HOUR, -24, GETUTCDATE())
ORDER BY rs.avg_cpu_time DESC;
GO

If an expensive query slows down due to an inefficient execution plan on the new engine, stabilize it immediately by forcing the known good plan preserved in Query Store:

-- Force the historical fast plan ID without requiring code changes
EXEC sp_query_store_force_plan @query_id = 412, @plan_id = 108;
GO

Alternatively, use database engine hints to target individual queries without changing application source code:

-- Apply a targeted CE hint to a problematic query via Query Store
EXEC sp_query_store_set_hints 
    @query_id = 412, 
    @query_hints = N'OPTION(USE HINT(''FORCE_LEGACY_CARDINALITY_ESTIMATION''))';
GO

Frequently Asked Questions (FAQ)