Testing SQL Server 2016 upgrades on a restored copy

Four cores of Standard ESU: $6,657.60 a year

SQL Server 2016 extended security updates (ESU) are that subscription. Microsoft's ESU FAQ limits them to updates the Microsoft Security Response Center rates Critical, released if and when available, with technical support covering only the updates themselves. A vulnerability rated Important stays open on a 2016 server whether you pay or not. Without ESU, a 2016 instance can't open a Microsoft support ticket at all, even under a support plan.

Only Enterprise and Standard on SP3 qualify. The license needs Software Assurance under an Enterprise Agreement, Enterprise Subscription Agreement, Server and Cloud Enrollment or Enrollment for Education Solutions, or it has to move to a pay-as-you-go (PAYG) license through Azure Arc. A perpetual license without Software Assurance doesn't qualify, and a Server+CAL instance has to switch to PAYG first.

Through Arc, ESU bills per core hour, with a four-core minimum per VM and Standard capped at 24 cores. The public Azure Retail Prices API lists the Std edition - ESU 2016 meter at $0.19 per core hour and Ent edition - ESU 2016 at $0.74, both effective April 1, 2026:

curl -s "https://prices.azure.com/api/retail/prices?currencyCode=USD&\$filter=contains(meterName,'ESU%202016')"

Four cores for a year, at 8,760 hours, against SQL Server 2022 Standard at Open No Level (NL) list prices:

A year of Standard ESU on four cores costs 2.35 times a year of the 2022 subscription and 1.9 times a year of 2022 on PAYG. SQL Server 2022 licenses an individual VM per core only with subscription licenses or active Software Assurance, so the perpetual row covers a VM only with Software Assurance added on top. The PAYG license carries new version upgrade rights, so a shop without Software Assurance that goes PAYG for ESU already pays for the right to run 2022 or 2025. Once it upgrades, the ESU meter stops and the Std edition - PAYG meter keeps billing $292 a month for four cores.

Charges started at midnight UTC on July 15, 2026, and a subscription opened later carries a one-time bill back to that date. Four Standard cores accrue $18.24 a day, so a server that subscribes on October 1 opens with about $1,423 of back billing. Year two and year three prices aren't listed yet, so budget one year at a time.

Three Arc billing details catch people out:

• Arc tracks each machine by its VM ID, and a rebuild, migration, hardware change or clone produces a new one, billed as a new machine with a fresh bill back. Build the test server as a new VM, since a clone of the enrolled production VM brings its Arc agent along.
• SQL Server 2016 on an Azure VM pays the same ESU rates, where SQL Server 2014 got ESU free there.
• A third-party host's SQL Server gets ESU only through Arc, and an instance licensed through the host's SPLA uses Arc's license-only type, which has no ESU subscription. Ask the host which license type yours carries.

ESU is the right buy when the vendor has no certified release due within the year, or when the application retires before ESU ends on July 17, 2029.

Six questions for the software vendor

Get the vendor's answers in writing, because they set both the target version and the compatibility level. Microsoft's compatibility certification guidance says a database certified on SQL Server 2016 "was implicitly certified on database compatibility level 130", and a move from 2016 to 2022 runs into no discontinued Transact-SQL. The smallest useful request is support on a newer engine with the database left at level 130.

1. Current certified versions by edition, and the date SQL Server 2022 or 2025 joins the vendor's certified list.
2. Support for the application on SQL Server 2022 or 2025 with the database at compatibility level 130.
3. Any installer or schema upgrade script that checks for major version 13 and would refuse 16 or 17 at the next application update.
4. Client drivers on the application server, since SQL Server Native Client doesn't ship with 2022 or later, and ODBC Driver 18 and OLE DB Driver 19 encrypt connections by default.
5. Server-level objects the vendor installed: CLR assemblies and whether they're signed, linked servers, Agent jobs, Database Mail profiles, SSRS reports and SSIS packages.
6. Evidence the vendor's support team accepts from a test copy, such as build numbers, the compatibility level, exact error text and the Query Store regression list.

Settle who gives the vendor access to the test copy and who removes it afterward. For one customer's hosted third-party application, Petrichor engineers keep the application patched and give the software vendor access when it calls.

Some vendors treat every version they haven't certified as unsupported, and production then waits while the test results make the case for the ESU line in the budget. A refusal that comes with a certification date still helps, because the date sets how many months of ESU to buy.

Match the test environment to production

The test means something only when the database, the application tier and the instance settings match production, and when nothing on the test side can reach a live system. Record the restore the way database restore tests lays out.

SQL Server 2022 or 2025

Pick the newest version the vendor will support, then check the Windows Server release underneath it.

A 2022 instance on Windows Server 2016 inherits that operating system's end of support on January 12, 2027, so give it Windows Server 2019 or later. Fall back to SQL Server 2019, supported to January 8, 2030, only when the vendor certifies nothing newer. SQL Server 2017 ends extended support on October 12, 2027, three months after ESU year one closes. An in-place upgrade from 2016 SP3 to 2025 is a supported path, but a restore onto a new server leaves production untouched as the way back.

The last full backup before month end

Restore the full backup taken immediately before last month's close, since production already produced that close's correct output and the rerun has known totals to match. If the last full backup ran days earlier, restore the log backups after it and stop with STOPAT at the minute before the first close task started. Keep the production database name on the test instance, because vendor code can reference the database by name in connection strings and three-part names.

RESTORE FILELISTONLY FROM DISK = N'D:\backup\LedgerProd_full.bak';

RESTORE DATABASE LedgerProd
  FROM DISK = N'D:\backup\LedgerProd_full.bak'
  WITH MOVE N'LedgerProd' TO N'E:\data\LedgerProd.mdf',
       MOVE N'LedgerProd_log' TO N'F:\log\LedgerProd_log.ldf',
       STATS = 5;

SELECT name, compatibility_level
FROM sys.databases
WHERE name = N'LedgerProd';

The backup records production's file paths, which sit under an MSSQL13 folder, and a new instance's default folders carry its own version number, so every logical file that FILELISTONLY returns needs a MOVE. The restored database is upgraded to the new engine during recovery and keeps compatibility level 130. That upgrade runs one way. No SQL Server backup restores to an earlier version, so neither this copy nor production after cutover can go back to 2016. If production uses Transparent Data Encryption or encrypted backups, restore the certificate on the test server first.

Before the first timed run, run DBCC CHECKDB and update statistics with sp_updatestats, so a slow task points at the engine change. Production needs the same two steps after cutover.

On Petrichor, you file this restore as a ticket and a Petrichor engineer runs it.

Recreate logins, Agent jobs and linked servers

Logins live in master, and Agent jobs, proxies and Database Mail profiles live in msdb, so the restore brings none of them. The sp_help_revlogin script from KB 918992 recreates SQL logins with their original SIDs and hashed passwords. Windows logins map by their Active Directory SID, so they resolve only when the test server sits in the same domain. Then look for users still orphaned and remap them:

SELECT dp.type_desc, dp.sid, dp.name AS user_name
FROM sys.database_principals AS dp
LEFT JOIN sys.server_principals AS sp ON dp.sid = sp.sid
WHERE sp.sid IS NULL
  AND dp.authentication_type_desc = 'INSTANCE';

ALTER USER [ledger_app] WITH LOGIN = [ledger_app];

The restore also makes the login that ran it the database owner, and code that runs as the owner picks up that login's permissions. Compare SUSER_SNAME(owner_sid) in sys.databases with production and set it back with ALTER AUTHORIZATION ON DATABASE::LedgerProd TO [owner_login];.

Before the first copied Agent job runs, leave Database Mail unconfigured and point linked servers and export paths at test targets. The restored database also carries the vendor's own settings tables, which often hold production mail hosts, export folders and bank or tax service endpoints, so block outbound mail and file transfer from the test network too. Skip either step and last month's payroll export runs again from the test server.

An application server at the current release

Month end runs from the vendor's application server, so the test needs one. For one software customer's QA team, the stack Petrichor builds is an application server and a SQL server with external access, so a tester can load a web page and run the application in test mode. An upgrade test calls for the same pair: the vendor's application server at the production version, its connection strings pointed at the test instance, and its scheduled services running from it. The client driver, its encryption defaults and the vendor's batch jobs hit the new instance from that server, and a database-only restore never exercises any of them.

Developer edition timings and the Standard feature gap

Developer editions are free and licensed for development and test only. In SQL Server 2022, Developer carries every Enterprise feature, while 2025 splits it into Enterprise Developer and Standard Developer.

The split matters for timings. Batch mode on rowstore, batch mode adaptive joins, memory grant feedback and automatic tuning are Enterprise only in both versions, and Standard caps batch mode parallelism at two. A Standard production server tested with Enterprise features posts month-end timings it will never match. For a 2025 target with Standard in production, test on Standard Developer. A 2022 target has no Standard Developer. Installing Standard with PAYG billing through Arc for the test month costs about $292 on four cores and produces timings production can match, at the price of letting the test server reach Azure for metering. Connected through an Azure dev/test subscription, the same Standard instance bills nothing, since Arc zeroes the SQL Server meters there. On 2022 Developer instead, the timings flatter the new version, and the totals check should outweigh the clock.

Where a restored database breaks first

The first failures come from the instance and the client drivers, before any plan changes, so they show up at level 130 and cost little to find.

Two of the fixes belong to the vendor: signing the assemblies and changing the provider. Finding them early lets the vendor's fix date set how much ESU to buy.

Last month's close on the 2016 optimizer

Take a hypothetical distributor, Harwell & Pike, running a vendor ERP on a four-core SQL Server 2016 Standard VM. Its close runs invoice batch posting, inventory valuation, receivables aging, the payroll export and the general ledger close, in that order.

Before the restore, capture three things on production:

• Task durations from the application's logs or msdb.dbo.sysjobhistory.
• Deprecated Features counters after a month end, read before any restart because they count from the last service start. SELECT * FROM sys.dm_os_performance_counters WHERE object_name LIKE '%SQL%Deprecated Features%'; returns them, and a nonzero "Using OLEDB for linked servers" row flags linked servers still on SQLOLEDB.
• Instance settings from sp_configure, at least max server memory, MAXDOP and cost threshold for parallelism, to copy onto the test instance.

Right after the restore, turn on Query Store. It's on by default only for databases created on SQL Server 2022 or later, and a database restored from 2016 keeps its own setting, which is off unless someone changed it. Set the capture mode to ALL, because many close queries run once a month and the default AUTO mode skips queries that run rarely and cheaply. Raise the size cap too: a store that fills switches itself to READ_ONLY and stops recording halfway through the close.

ALTER DATABASE LedgerProd
SET QUERY_STORE = ON (
  OPERATION_MODE = READ_WRITE,
  QUERY_CAPTURE_MODE = ALL,
  MAX_STORAGE_SIZE_MB = 4096,
  WAIT_STATS_CAPTURE_MODE = ON);

Run the close through the application server, in the order the finance team runs it. Compare invoice counts, valuation totals and ledger balances with what production produced last month, since matching totals are the correctness check and timings come second. For each task, record the production duration, the test duration at 130, whether the totals matched, and any error text.

A trace replay can't stand in for that run. Distributed Replay isn't available with SQL Server 2022 and later, and a replay skips the drivers and batch jobs in the table above. The ostress tool in RML Utilities can add concurrency to a later run.

QA teams at one Petrichor account keep the baseline results from each test run as history. Treat the 130 run the same way, and back up the test database when the close finishes, because its Query Store holds that run's plans and runtime statistics.

Plan shape protection at an unchanged compatibility level assumes comparable hardware, the same database and the same workload, so size the test VM to production's cores and memory and copy the instance settings. A close run by one person on a quiet server shows no blocking from other users, and it costs the finance team the hours a close takes.

Switch to level 160 or 170 with Query Store running

Raising the compatibility level is the step where query plans change. Microsoft's Query Store usage scenarios page gives the order: upgrade without changing the level, capture a baseline for a full business cycle, raise the level, force the last good plan for any query that regressed, and revert the level if forcing fails.

That order assumes one database whose Query Store holds both runs. The close writes to the ledger, so the second pass needs a fresh restore of the same pre-close backup, and that restore starts with an empty Query Store. Restore the backup of the 130 run beside it as LedgerProd_130, turn Query Store on in the fresh copy with the same settings, raise its level, and rerun the close. Use 170 on SQL Server 2025.

ALTER DATABASE LedgerProd SET COMPATIBILITY_LEVEL = 160;

EXEC sys.sp_query_store_set_hints
  @query_id = 48,
  @query_hints = N'OPTION (USE HINT (''QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_130''))';

Aggregate sys.query_store_runtime_stats by query_hash in each copy and join the two results, and every query whose duration grew at the new level goes on the list. A regressed query's 130 plan sits in the other copy's store, out of reach of plan forcing, so pin the query with a Query Store hint that holds it to the 2016 optimizer without touching vendor code. Query IDs belong to one database's store, so carry the list as query hashes with their text, and look up each query's new ID wherever the hint goes next. In production, where one database holds plans from both levels, sp_query_store_force_plan does the same job with the older plan.

Skip the LEGACY_CARDINALITY_ESTIMATION = ON database setting for this upgrade. It selects the SQL Server 2012 estimator, which is older than the one the vendor certified at level 130.

Hints and forced plans depend on the indexes they use, so check them after every vendor update. force_failure_count and last_force_failure_reason_desc in sys.query_store_plan report forced plans that stopped applying, and sys.query_store_query_hints reports failed hints. The regression list reflects one month end on test hardware, which makes it evidence for the vendor and falls short of certification.

Can production keep the 2016 behavior after cutover?

Yes. SQL Server 2022 accepts levels 100 to 160 and 2025 accepts 100 to 170, and a restored 2016 database keeps 130. At that level the new engine keeps SQL Server 2016's cardinality estimation, which is the behavior the vendor certified.

The level has two limits. Discontinued features get no protection from it, and optimizer improvements from newer versions apply only at the newest level. Optimizer fixes shipped in cumulative updates stay off at 130 until ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = ON; runs in the database. The Deprecated Features counter also lists levels 80 through 140 as deprecated, so 130 works today and has a planned end. Put a date for raising it inside the new version's support life, and hold the vendor to that date.

What each result means for the budget

Covering only the months until cutover still bills back to July 15. A four-core Standard server that cuts over on December 31, 2026 pays for 169 days, about $3,083.

Send the vendor SELECT @@VERSION; output from both servers, the database's compatibility level, the task timings from both runs, the regressed queries with their hashes and text, and exact error text.

Two situations skip the test. An application that retires before July 13, 2027, the end of ESU year one, needs a year of ESU and nothing more. When the vendor already certifies 2022 or 2025 and publishes an upgrade guide, follow the guide and use the restored copy as its rehearsal. The third case changes the target. A server already on an Azure VM beside other Azure services, under a vendor that certifies Azure SQL Managed Instance, belongs on Managed Instance. Migrating to Azure SQL ends the ESU charge, and Managed Instance applies engine updates itself. Run the same restored copy test there and stay on Azure.

Once the test passes, production becomes a cutover problem. Migration rehearsal covers the timings and the rollback for writes made after cutover, which matters here because a database upgraded to 2022 or 2025 never restores to 2016.

We built our cloud to run software that was never designed for a cloud. Our engineers can stand up a version of a customer's application in a Petrichor stack so the customer can see how it runs there.

Bring the vendor's certified version list and last month's close timings to the call. Book a call with a consultant