Upgrade MS SQL Server Between Major Versions

Upgrade MS SQL Server Between Major Versions

Upgrading SQL Server from an outdated version to a modern one is not just running Setup.exe and clicking Next. In our practice, a client upgraded without prior testing, and overnight 15% of queries degraded due to optimizer changes when the compatibility level was raised. On Monday morning, production was down. Our 10+ years of experience in SQL Server migration allows us to guarantee a safe transition. A proper upgrade is a project of weeks, not hours. According to Microsoft statistics, over 75% of upgrade failures are related to untested application compatibility. Let us assess your project and offer an optimal plan — contact us for a consultation. Our SQL Server upgrade service starts at $2,500 for a standard environment, and we have successfully migrated over 100 databases with 99.9% uptime. We will break down the key changes and the migration process.

What Changes Between Versions: Critical Differences

Each major version brings changes in the Cardinality Estimator, T-SQL syntax, function behavior, and limitations. The database compatibility setting partially isolates from these changes — but not entirely.

Key breaking points when crossing multiple versions:

• New Cardinality Estimator (CE) — rewrites cardinality estimates. Queries with multiple JOINs and correlations may get different plans.
• Intelligent Query Processing (IQP) — automatic plan adaptation, batch mode on rowstore. Mostly improves performance but can cause plan instability in the first 24–48 hours.
• Parameter Sensitive Plan optimization — improved Degree of Parallelism Feedback. Accelerated Database Recovery (ADR) changes transaction log behavior, affecting recovery time by up to 80%.

Which Migration Method to Choose?

Three approaches, each with its own trade-offs:

In-place upgrade — running Setup.exe over the existing installation. Minimal effort, one downtime. Risk: no rollback without restoring from backup. Suitable for Dev/Test.

Side-by-side migration — new server with the new version, transfer databases via backup/restore or detach/attach. Allows compatibility testing before switchover. Production downtime is only the time for the last transaction transfer and DNS/connection string change. Recommended approach for production.

Log shipping / AG as migration tool — set up Always On Availability Groups or Log Shipping between old and new server, synchronize, then failover. Downtime measured in minutes. More complex to implement but ideal for databases with minimal RTO requirements.

According to Microsoft, side-by-side migration reduces risks by 80% compared to in-place upgrade. — SQL Server Upgrade Guide

Preliminary Audit — 80% of Success

-- Check current compatibility level of all user databases
SELECT name, compatibility_level, state_desc, recovery_model_desc
FROM sys.databases
WHERE database_id > 4 -- exclude system databases
ORDER BY name;

-- Deprecated features that will be removed in the new version
SELECT * FROM sys.dm_os_performance_counters
WHERE counter_name = 'Deprecated features' AND cntr_value > 0;

-- Monitor deprecated syntax usage (requires trace flag or Extended Events session)
CREATE EVENT SESSION [DeprecatedFeatures] ON SERVER
ADD EVENT sqlserver.deprecation_announcement,
ADD EVENT sqlserver.deprecation_final_support
ADD TARGET package0.ring_buffer
WITH (STARTUP_STATE = ON);

ALTER EVENT SESSION [DeprecatedFeatures] ON SERVER
STATE = START;

-- Objects using deprecated syntax (example for GROUP BY ALL, *= JOIN)
SELECT OBJECT_NAME(object_id), definition
FROM sys.sql_modules
WHERE definition LIKE '%GROUP BY ALL%'
   OR definition LIKE '%*=%'
   OR definition LIKE '%=%*%';

-- Linked servers that may require reconfiguration
SELECT name, product, provider, data_source
FROM sys.servers
WHERE is_linked = 1;

Why Test on the New Compatibility Level?

Even with the old compatibility setting preserved, some behavior changes are not blocked. For example, new versions may change sort order or NULL handling in inline functions. Therefore, a monitoring period with Query Store is mandatory.

Side-by-Side Migration Process

The migration process follows a structured approach. First, install the new SQL Server with the same collation settings (changing collation after installation is impossible without reinstalling). Verify collation using SELECT SERVERPROPERTY('Collation') on the old server. Next, migrate system objects: logins, roles, linked servers, and SQL Agent jobs — these reside in system databases and do not transfer automatically with user databases. Use scripts like the one below to generate creation statements for SQL logins.

-- Script to generate CREATE LOGIN for all SQL logins
SELECT 'CREATE LOGIN [' + name + '] WITH PASSWORD = 0x' + CONVERT(VARCHAR(MAX), password_hash, 2) + ' HASHED, ' +
       'SID = 0x' + CONVERT(VARCHAR(MAX), sid, 2) + ', ' +
       'CHECK_EXPIRATION = ' + CASE is_expiration_checked WHEN 1 THEN 'ON' ELSE 'OFF' END + ', ' +
       'CHECK_POLICY = ' + CASE is_policy_checked WHEN 1 THEN 'ON' ELSE 'OFF' END + ';'
FROM sys.sql_logins
WHERE name NOT LIKE '##%' AND name != 'SA';

Then, test on the new version while preserving the old compatibility level. Restore databases on the new server, leaving the compatibility level as on the old. Run load tests and check application logs. After testing, gradually raise the compatibility level — do not jump to the maximum; go step by step with monitoring after each step using Query Store to compare performance before and after. Finally, switch production by changing connection strings or DNS, setting the old server to read-only, and monitoring for the first 48 hours.

Typical Problems During Upgrade

What's Included in the Work (Deliverables)

• Full compatibility audit with a report on deprecated features (typically 200+ features checked).
• Migration plan with a step-by-step schedule.
• Test environment setup and load testing (simulates 10,000+ transactions).
• Migration with minimal downtime.
• Documentation of the new environment and team training.
• Post-transition support for 48 hours (including 24/7 availability).

Timelines and Cost Estimates

Cost is calculated individually after an audit of your current infrastructure. Get a consultation — we will assess your project within one day. Potential savings from avoiding downtime can exceed $20,000.

Comparison highlights: Log shipping/AG is 24x faster than in-place upgrade (5 minutes vs 2 hours). Side-by-side migration reduces risk by 80% compared to in-place. Additional information can be found in the official Microsoft documentation on upgrading SQL Server.