Relicbridge

Upgrading SQL Server under a legacy app before you migrate the app

Two dates put this on a lot of calendars. SQL Server 2016 reaches end of extended support on 14 July 2026, and SQL Server 2014 already passed the same line in July 2024. Microsoft publishes both on its product lifecycle pages, and you should check your exact build there rather than trusting an internal wiki.

When an audit finding names the database engine, the reflex is to bundle the engine upgrade into the application rewrite: new stack, new database, one cutover. We argue against that most of the time. The database upgrade and the application migration are two different risks, and combining them means a failed cutover tells you nothing about which one broke.

This tutorial does the database half on its own. At the end you have the same WebForms, Classic ASP, VB6 or Access application, unchanged, running on a supported SQL Server, with evidence that it behaves the same. That clears the audit finding, and it removes a variable from the migration that comes later.

You need: read access to the current instance, a place to restore a copy, and a maintenance window you can use twice.

Inventory what actually talks to the database

The application is not the only client. Before anything else, find every connection. Query the instance while it is under normal load:

SELECT DISTINCT
    c.client_net_address,
    s.host_name,
    s.program_name,
    s.login_name,
    DB_NAME(s.database_id) AS database_name
FROM sys.dm_exec_sessions AS s
JOIN sys.dm_exec_connections AS c ON c.session_id = s.session_id
WHERE s.is_user_process = 1
ORDER BY s.program_name;

Run it a few times a day for a week, including the overnight window, and append the results to a table. One run misses the month-end job and the Access file on the controller's desktop.

program_name is the useful column. It tells you which driver each client is using, which is what breaks:

  • Microsoft Access or 2007 Microsoft Office system means an Access front end with linked tables.
  • Microsoft (R) Windows (R) 2000 DTS or similar means a DTS package, which no supported SQL Server will run.
  • SQLAgent - TSQL JobStep means a scheduled job that has to move with the database.
  • Blank or .Net SqlClient Data Provider is usually the web application.
  • Anything you cannot identify is the one that will page you at 2am.

Also collect the things that live outside the database files: linked servers, SQL Agent jobs and their schedules, operators and alerts, logins and their SIDs, database mail profiles, server-level triggers, and any file share paths referenced by BULK INSERT or xp_cmdshell. A restored backup carries none of these.

Measure the compatibility gap with the Data Migration Assistant

Don't read the code looking for problems. Measure. Microsoft's Data Migration Assistant reads the database and reports breaking changes and behavior changes for a chosen target version. It is free and it runs read-only against a copy.

Restore a recent full backup to a scratch instance first, then point DMA at the copy. Two reasons: assessment runs cost real CPU, and you want to be able to run it repeatedly without asking permission.

DMA gives you two lists that matter:

Breaking changes. These stop things working. On databases this old the recurring ones are removed syntax and removed features: the old *= and =* outer join operators, RAISERROR in its integer-string form, FASTFIRSTROW and other deprecated table hints, sp_dboption, SET ROWCOUNT against INSERT/UPDATE/DELETE, references to sys.sysobjects-era compatibility views, and TEXT/NTEXT/IMAGE columns used with functions that no longer accept them.

Behavior changes. These are worse, because nothing errors. The big one is the cardinality estimator: SQL Server 2014 introduced a new estimator, and a query plan that was fine for a decade can regress on a database whose statistics were shaped by the old one. A nightly report that took four minutes takes fifty. Nobody notices in the maintenance window; everybody notices at month end.

Export the DMA report and keep it in the repository next to the migration notes. It is the list you will work down, and it is the evidence you had a reason for each change.

Record what the queries do today

The DMA report tells you what might break. It does not tell you what your application actually sends. Capture that.

An Extended Events session, running on the old instance for a full business cycle, is enough:

CREATE EVENT SESSION [legacy_workload] ON SERVER
ADD EVENT sqlserver.rpc_completed (
    ACTION (sqlserver.client_app_name, sqlserver.database_name)),
ADD EVENT sqlserver.sql_batch_completed (
    ACTION (sqlserver.client_app_name, sqlserver.database_name))
ADD TARGET package0.event_file (
    SET filename = N'D:\\xe\\legacy_workload.xel',
        max_file_size = 512,
        max_rollover_files = 20)
WITH (MAX_DISPATCH_LATENCY = 30 SECONDS, STARTUP_STATE = ON);

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

Budget for the disk; a busy legacy application generates a few gigabytes a day at this granularity. Note the duration and row counts along with the statement text, because duration is the number you compare after the upgrade.

If the application has a characterization harness already — the HTTP-replay kind described in Golden-master tests for legacy web apps — run it against the restored copy too. The database upgrade is exactly the kind of change that harness was built to catch, and it costs nothing to reuse it here.

Do the upgrade as a side-by-side restore

Install the new SQL Server on new hardware or a new VM. Do not upgrade in place. In-place upgrade works, and when it doesn't, your rollback is a restore under time pressure with an audience.

Side by side, the sequence is boring on purpose:

  1. Restore the full backup of each database onto the new instance.
  2. Script out and apply the server-level objects from the inventory: logins, jobs, linked servers, mail profiles, alerts.
  3. Fix orphaned users, which happens on every single one of these:
ALTER USER [app_user] WITH LOGIN = [app_user];
  1. Leave the compatibility level where it was. Check it:
SELECT name, compatibility_level, collation_name,
       is_read_committed_snapshot_on, page_verify_option_desc
FROM sys.databases
WHERE database_id > 4;

A database restored from SQL Server 2014 onto SQL Server 2022 keeps compatibility level 120. That is supported and it is what you want at first: new engine, old query optimizer behavior. One variable at a time.

  1. Point a copy of the application at the new instance and run the workload. Replay the captured statements, run the harness, have two people who use the system every day do their real work on it for a day.

Then move the compatibility level, separately

Once the application is stable on the new engine, raise the compatibility level as its own change with its own window. Before you do, turn on Query Store, because it is the tool that makes this reversible:

ALTER DATABASE [AppDb] SET QUERY_STORE = ON;
ALTER DATABASE [AppDb] SET QUERY_STORE
    (OPERATION_MODE = READ_WRITE, QUERY_CAPTURE_MODE = AUTO);

Let it collect a baseline for a week under the old compatibility level. Then raise it:

ALTER DATABASE [AppDb] SET COMPATIBILITY_LEVEL = 160;
UPDATE STATISTICS ... -- or a full statistics refresh with FULLSCAN

Now compare, don't guess. Query Store will show you the regressed plans directly:

SELECT TOP (25)
    q.query_id,
    SUBSTRING(t.query_sql_text, 1, 200) AS query_text,
    rs.avg_duration / 1000.0 AS avg_ms,
    rs.count_executions,
    p.plan_id
FROM sys.query_store_runtime_stats AS rs
JOIN sys.query_store_plan AS p ON p.plan_id = rs.plan_id
JOIN sys.query_store_query AS q ON q.query_id = p.query_id
JOIN sys.query_store_query_text AS t ON t.query_text_id = q.query_text_id
WHERE rs.last_execution_time > DATEADD(DAY, -1, SYSDATETIME())
ORDER BY rs.avg_duration * rs.count_executions DESC;

For a query that regressed, you have three honest options, in this order of preference: fix the query or its indexes; force the old plan from Query Store (sys.sp_query_store_force_plan); or set LEGACY_CARDINALITY_ESTIMATION = ON at the database scope as a temporary measure with a date on it. The third one is a decision to stop learning about the problem, so write down why.

Rollback for this step is one statement, which is the whole reason for doing it separately:

ALTER DATABASE [AppDb] SET COMPATIBILITY_LEVEL = 120;

What to watch for

  • Drivers, not just the engine. Classic ASP connection strings still carry Provider=SQLOLEDB, which Microsoft deprecated and then replaced with the Microsoft OLE DB Driver for SQL Server (MSOLEDBSQL). VB6 applications built against SQLOLEDB or old ODBC drivers hit the same wall, usually around TLS 1.2 and encryption defaults rather than SQL syntax. Test the driver change on its own, before the engine change, so you know which one caused a failure.
  • Access linked tables. Linked tables cache the server name and driver in each linked table definition. Plan on a relink pass, and check ODBC DSNs on every workstation that opens the front end, including the ones in a different building. This is the same problem discussed in Splitting an Access application and it is worth doing that split first if it is already on the roadmap.
  • DTS packages. If the inventory found DTS, the engine upgrade is where those jobs die. They need rewriting, not migrating, and that is real work with its own schedule.
  • Collation. A restored database keeps its own collation, but temp tables take the new instance's collation. If the server-level collations differ, expect collation-conflict errors in stored procedures that join to #temp tables. Match the instance collation at install time and this never comes up.
  • SET option defaults. Ancient client libraries connect with different ANSI_NULLS and QUOTED_IDENTIFIER settings than modern ones. If an old query relies on = NULL comparing true, a driver change will surface it.

Why this order

Engine first, then compatibility level, then the application migration. Each step is small enough that a problem names its own cause, and each has a rollback you can execute in the window you already have.

It is slower on paper than one combined cutover. In practice it is faster, because a combined cutover that goes wrong costs you the weekend and the credibility to ask for another one.

If you are looking at one of these dates and the application on top is WebForms, Classic ASP, VB6, Access or FoxPro, we do this work as a measured engagement: the inventory and the DMA assessment first, then the upgrade, then the application migration as separate slices. A senior engineer replies to email within one business day.