What Changes When You Move to a New Major Version

Moving to a new major version feels like one change, but it is really two. The engine binaries move first, while database compatibility and application behavior each need their own decision on their own day.

Separate Engine and Database Changes in a New Major Version

Installing a new SQL Server major version changes the engine that runs the instance. Restoring or attaching a database on that newer engine can upgrade its internal format. The database compatibility level is a separate setting. It can stay at an older level until you change it deliberately, subject to what the target version supports.

This separation is useful during migration. It lets you move the database, verify basic function, and then plan a compatibility level change as another tested step. It is not a promise that every behavior stays identical. Engine fixes, security defaults, and system components can change even while compatibility level remains old.

Record the source engine build and every database compatibility level before moving. Capture the same values after restore. A migration ticket that says only “SQL Server upgraded” hides two different changes.

SELECT
    SERVERPROPERTY('ProductVersion') AS EngineVersion,
    name,
    compatibility_level
FROM sys.databases
WHERE database_id > 4
ORDER BY name;

Expect Query Plan Differences on a New Major Version

The optimizer changes with each new major version. A compatibility level can control important optimizer behavior, including which cardinality estimation model applies to a query. Other factors still matter: statistics, indexes, parameters, and instance settings. A changed plan deserves investigation, not an automatic blame on the new engine.

Almost every major upgrade I help with has at least one query that gets slower, and it is almost never the one anybody predicted.

Capture a baseline before migration. Use Query Store where appropriate and keep actual plans for critical queries. After the move, run the same workload with representative parameters. Compare duration, CPU, reads, memory grants, and errors using measurements from your own servers. Do not rely on a synthetic number copied from another blog.

If a query regresses, isolate the change. Check compatibility level first, then statistics and settings. Query Store can help find a known good plan while you work on a durable fix. A major upgrade plan should include time for this work, not assume every query improves by default.

Review Features Deprecated or Removed in the New Major Version

A feature marked deprecated can continue to work for a while, but it is a warning to plan a replacement. A removed feature can stop a deployment or application path outright. Read the target release documentation and run the supported assessment tools against your current instance. Search application code, jobs, and scripts for flagged syntax.

Do not limit the search to stored procedures. SSIS packages, linked server providers, drivers, SQL Server Agent steps, backup tools, and monitoring scripts all touch the instance. A login path can fail before the application runs its first query. Include those components in the inventory.

Assign each finding an owner. Some fixes are easy, such as changing an old syntax form. Others need a vendor release or a redesign. The earlier you find them, the less likely the migration window becomes an emergency coding session.

Move Server Objects Deliberately

A user database does not carry every instance dependency. Logins, credentials, Agent jobs, linked servers, operators, alerts, and server configuration need separate migration work. Preserve login identities where required so database users map correctly. Test secrets through an approved credential process rather than copying a password into a script.

Check tempdb, max server memory, parallelism settings, backup paths, and service accounts on the target. A new instance with defaults can behave differently from the source even before compatibility changes. Document what you intend to preserve and what you intend to improve.

Review high availability architecture. A new major version can alter supported topology and upgrade sequence. Use the documented rolling upgrade path for an availability group. A side by side migration requires a cutover and reverse path for data. Choose the method based on recovery requirements.

Test the Application Contract

Restore a recent production backup into the target test instance. Run the critical workflows and jobs. Check data types, result shapes, error handling, and transaction behavior. Performance is only one part of acceptance. A fast query that returns the wrong result is still a failure.

Almost every upgrade I review passes the database tests and then trips on something the application assumed. Who owns the list of what your application expects from SQL Server? If nobody does, start that list today.

Test the exact driver and connection string used by the application. Authentication and encryption defaults can change over time. Check reporting, ETL, and vendor tools. A database that opens in Management Studio does not certify the rest of the system.

Have the application owner sign off on specific scenarios. Keep the test evidence tied to the target build and compatibility level. If either changes, identify which checks need repeating. Clear scope prevents a passing old test from being used to approve a new setup.

Plan Compatibility as Its Own Change

After the database runs on the new engine, decide when to change compatibility level. Test that step in staging with production like data and workload. It can activate optimizer behavior changes and other database level features. Record the before and after values and compare actual workload results.

I leave the compatibility level where it was on cutover day and move it later, on its own. Change two things at once and when something slows down you cannot tell which one to blame.

Do not leave old compatibility indefinitely without a reason. It can delay access to useful behavior and make future upgrades harder. Put a review date and owner on the choice. A staged migration is a plan, while forgotten compatibility is drift.

The change itself is simple SQL, but it should follow evidence. Substitute your tested target level only after confirming support on the new engine. Keep a backup and a response plan for regressions.

SELECT
    name,
    compatibility_level
FROM sys.databases
WHERE database_id > 4
ORDER BY name;

Know the Return Route

After a database moves to a newer major engine format, you cannot simply attach its files to the older engine. A reverse move needs a pre upgrade backup plus a plan for changes made after cutover, or a logical migration route. Define the recovery point and data loss tolerance with the business before the move.

Keep the source instance available for the agreed period if you use a side by side cutover. Stop writes cleanly and verify application routing. Test the return procedure rather than describing it as “switch DNS back.” DNS does not reverse database changes.

Finish with version, compatibility, application, backup, and monitoring checks. A major upgrade is complete when the service is stable and supportable on the new engine.

Related reading on this blog: What Is Compatibility Level in SQL Server? and Finding Deprecated Features Before They Bite.

A major upgrade is not a new installer screen, it is a tested change to the whole application path.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.