In-Place vs Side-by-Side Upgrade, Compatibility Levels, Breaking Changes, and Rollback Strategy

Learning outcomes

ServiceHub has a healthy SQL Server estate but must move to SQL Server 2025 without turning “upgrade night” into an irreversible experiment. An engine upgrade, a database-file metadata upgrade, a database compatibility-level change, an edition change, and an application rollout are separate changes even when a wizard can perform several of them in one maintenance window. The safest plan gives each change an observable boundary, an acceptance gate, and a rollback mechanism that still works after the target engine has touched the database.

1. Five changes that operators often collapse into one word

Engine version is the installed SQL Server executable build. Edition controls licensed capability. The database has an internal storage/metadata version that SQL Server can upgrade when an older database is attached or restored. Compatibility level is a database-scoped behavioral contract for query-processing and language features; SQL Server 2025 supports compatibility levels 100 through 170. Finally, clients bring their own driver version, encryption defaults, authentication behavior and application assumptions. Changing all five at once destroys diagnostic leverage.

USE master;GOIF DB_ID(N'ServiceHubUpgradeLab') IS NULLBEGIN    CREATE DATABASE ServiceHubUpgradeLab;END;GOALTER DATABASE ServiceHubUpgradeLab SET COMPATIBILITY_LEVEL = 160;ALTER DATABASE ServiceHubUpgradeLab SET QUERY_STORE = ON(    OPERATION_MODE = READ_WRITE,    QUERY_CAPTURE_MODE = AUTO,    WAIT_STATS_CAPTURE_MODE = ON);GOUSE ServiceHubUpgradeLab;GOIF SCHEMA_ID(N'lab24') IS NULL EXEC(N'CREATE SCHEMA lab24 AUTHORIZATION dbo;');GOIF OBJECT_ID(N'lab24.WorkOrder', N'U') IS NULLBEGIN    CREATE TABLE lab24.WorkOrder    (        work_order_id bigint IDENTITY(1001,1) NOT NULL CONSTRAINT PK_lab24_WorkOrder PRIMARY KEY,        customer_id int NOT NULL,        region_code char(3) NOT NULL,        status varchar(16) NOT NULL,        priority tinyint NOT NULL,        opened_at datetime2(0) NOT NULL,        description nvarchar(300) NULL,        row_version rowversion NOT NULL,        CONSTRAINT CK_lab24_WorkOrder_Status CHECK(status IN ('OPEN','ASSIGNED','CLOSED','ESCALATED')),        CONSTRAINT CK_lab24_WorkOrder_Priority CHECK(priority BETWEEN 1 AND 5)    );    ;WITH n AS    (        SELECT TOP (12000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rn        FROM sys.all_objects a CROSS JOIN sys.all_objects b    )    INSERT lab24.WorkOrder(customer_id,region_code,status,priority,opened_at,description)    SELECT        CASE WHEN rn <= 5000 THEN 42 ELSE 100 + (rn % 2000) END,        CASE rn % 3 WHEN 0 THEN 'N01' WHEN 1 THEN 'W02' ELSE 'E03' END,        CASE rn % 4 WHEN 0 THEN 'OPEN' WHEN 1 THEN 'ASSIGNED' WHEN 2 THEN 'CLOSED' ELSE 'ESCALATED' END,        1 + (rn % 5),        DATEADD(minute,-rn,'2026-08-20T12:00:00'),        N'ServiceHub migration lab row ' + CONVERT(nvarchar(20),rn)    FROM n;    CREATE INDEX IX_lab24_WorkOrder_CustomerStatus      ON lab24.WorkOrder(customer_id,status,opened_at)      INCLUDE(region_code,priority);END;GO

SELECT    SERVERPROPERTY('ProductVersion') AS product_version,    SERVERPROPERTY('ProductLevel') AS product_level,    SERVERPROPERTY('Edition') AS edition,    SERVERPROPERTY('EngineEdition') AS engine_edition,    SERVERPROPERTY('HostPlatform') AS host_platform;GOSELECT    name,    compatibility_level,    state_desc,    recovery_model_desc,    is_query_store_onFROM sys.databasesWHERE name = N'ServiceHubUpgradeLab';

A database on SQL Server 2025 at compatibility 160 is still running on the SQL Server 2025 engine. Compatibility level is not an “emulated SQL Server 2022 instance.” It primarily controls database-level behavior such as optimizer features and some T-SQL surface, while engine fixes, security behavior, storage code and server features still come from the 2025 binary.

2. In-place versus side-by-side: rollback is the design difference

Microsoft's SQL Server 2025 Windows upgrade matrix allows direct upgrades from supported SQL Server 2014 SP3 and later generations through SQL Server 2022. That establishes what Setup supports; it does not prove that an in-place upgrade is the best operational strategy. Side-by-side is usually easier to reverse because the old engine can remain intact until post-cutover evidence is accepted.

3. Inventory what the database backup does not contain

A user database backup carries the database, not the entire instance. ServiceHub would fail after a technically perfect restore if the target is missing logins/SIDs, Agent jobs, credentials, proxies, server-scoped certificates, linked-server definitions, endpoints, server configuration, external providers or the TDE protector required to open an encrypted database. Inventory is therefore part of correctness.

SELECT name, type_desc, is_disabledFROM sys.server_principalsWHERE type IN ('S','U','G') AND name NOT LIKE '##%'ORDER BY type_desc,name;SELECT name, product, provider, data_source, is_linkedFROM sys.serversWHERE server_id <> 0;SELECT name, value, value_in_use, is_dynamicFROM sys.configurationsWHERE value <> value_in_use OR name IN(N'max server memory (MB)',N'max degree of parallelism',N'cost threshold for parallelism');SELECT name, enabledFROM msdb.dbo.sysjobsORDER BY name;USE ServiceHubUpgradeLab;SELECT name, thumbprint, expiry_dateFROM sys.certificatesWHERE name NOT LIKE '##%';

These queries require suitable metadata permissions and Agent objects may be irrelevant on Express. The important behavior is to save the inventory as an artifact, compare it against the target, and explicitly classify every difference as expected or unresolved.

4. Deliberately wrong: upgrade engine and compatibility together

A common “efficient” plan restores to 2025, immediately raises compatibility to 170, upgrades the application and changes drivers in one window. A regression then has four plausible causes and no clean baseline. Instead, preserve Query Store evidence and keep the original compatibility level initially when supported.

USE master;GOALTER DATABASE ServiceHubUpgradeLab SET COMPATIBILITY_LEVEL = 160;GOUSE ServiceHubUpgradeLab;SELECT compatibility_level FROM sys.databases WHERE name = DB_NAME();-- Run representative workload and capture Query Store evidence here.SELECT customer_id, status, COUNT_BIG(*) AS ordersFROM lab24.WorkOrderWHERE opened_at >= '2026-08-01'GROUP BY customer_id,status;GOALTER DATABASE ServiceHubUpgradeLab SET COMPATIBILITY_LEVEL = 170;GO-- Re-run the same workload, compare plans/waits/runtime evidence, then stage back.ALTER DATABASE ServiceHubUpgradeLab SET COMPATIBILITY_LEVEL = 160;GO

Changing compatibility is not a substitute for an engine rollback, but it can isolate query-processor behavior. If the engine itself must be reversed, a side-by-side design can redirect clients to the still-valid source. A newer-version backup cannot be restored onto an older SQL Server engine, so “we will restore the new backup back to 2022” is not a rollback plan.

5. Production judgment

Choose in-place only after validating supported source version/edition, OS requirements, downtime, system-database implications, third-party components, backup/recovery and operational rollback. Choose side-by-side when rehearsal, cross-platform movement, hardware refresh, edition/topology change or low-risk reversal matters more than temporary infrastructure cost. In either case, preserve a tested recovery path, freeze unrelated configuration changes and define who has authority to abort before the old environment is decommissioned.

Authoritative references

• Supported version and edition upgrades: SQL Server 2025
• ALTER DATABASE compatibility level
• Upgrade SQL Server to the latest version
• RESTORE compatibility support
• Query Store usage scenarios