What Breaks When You Upgrade, and How to Find It First

A SQL Server upgrade can expose old assumptions even when every database restores successfully. Check syntax, connections, collation, and query plans before the production move. Keep the old server available for comparison.

Inventory the Starting Point

Record the source edition, build, and database compatibility levels. Include the target release and supported upgrade path. A direct in-place upgrade and a database migration can have different supported starting points. Read the matrix for the route you intend to use.

SELECT
    SERVERPROPERTY('ProductVersion') AS source_version,
    SERVERPROPERTY('Edition') AS source_edition,
    SERVERPROPERTY('Collation') AS server_collation;

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

Keep the inventory with the application owners and their connection targets. Identify jobs, linked servers, credentials, and integration processes that live outside a user database. Restoring a database doesn’t bring all those dependencies along. An upgrade rehearsal should include the whole application path.

Find Deprecated and Removed Behavior

Deprecated means a feature is marked for future removal, not that it already fails today. Removed means the target no longer provides it. Treat those categories differently. Use Microsoft’s target-version compatibility guidance and a supported assessment tool to identify known issues.

SELECT instance_name AS deprecated_feature,
       cntr_value AS observed_uses
FROM sys.dm_os_performance_counters
WHERE object_name LIKE N'%:Deprecated Features%'
  AND cntr_value > 0
ORDER BY cntr_value DESC;

These counters show observed use in their current lifetime. A zero doesn’t prove the application never uses a feature. A monthly process might not have run since startup. Static assessment and runtime observation cover different gaps, so use both where they help.

The current SSMS migration component provides upgrade assessment capabilities. Review its findings alongside your own workload tests. An assessment can flag known patterns, but it cannot prove every generated SQL statement or external script will behave correctly.

Check Code and Changed Defaults

SELECT
    OBJECT_SCHEMA_NAME(object_id) AS schema_name,
    OBJECT_NAME(object_id) AS object_name,
    definition
FROM sys.sql_modules
WHERE definition LIKE N'%sp_change_users_login%';

This is one example of searching for an older interface, not a complete deprecation scanner. Text search can match comments and miss dynamic SQL assembled elsewhere. Encrypted modules hide their definitions. Include application code and scheduled scripts in the review.

Read target-release behavior changes, not only removed features. Defaults in drivers and tools can change independently of the engine. Encryption requirements, authentication, and connection options deserve a test through the real application. A successful SSMS connection doesn’t exercise every driver the application uses.

Avoid fixing compatibility errors by disabling security checks without understanding the new requirement. A certificate trust problem needs a certificate and connection design review. It doesn’t prove that the database upgrade itself was wrong.

Watch Collation and Temporary Objects

A side-by-side migration can introduce a different server collation while retaining the user database’s collation. Temporary tables normally use tempdb’s collation unless defined otherwise. Comparisons between temporary and permanent character columns can then fail or behave differently.

SELECT
    CONVERT(sysname, SERVERPROPERTY('Collation')) AS server_collation,
    CONVERT(sysname, DATABASEPROPERTYEX(DB_NAME(), 'Collation')) AS database_collation,
    CONVERT(sysname, DATABASEPROPERTYEX(N'tempdb', 'Collation')) AS tempdb_collation;

Test the queries that join temporary data with application tables. Don’t change every database’s collation as a blanket repair. Collation affects comparison rules and can require substantial schema work. Use a targeted design that preserves the intended meaning of the data.

Separate Engine and Compatibility Changes

Keeping a supported earlier compatibility level can reduce some behavioral changes during the engine move. It doesn’t emulate the entire old server or restore removed features. Plan a separate compatibility-level test rather than leaving the decision implicit.

Capture Query Store history before the move when available. Run representative workloads on the target and compare plans and resource use. A new optimizer can improve many queries while regressing a particular one. Test the important parameter shapes, not only the easiest demonstration.

Don’t invent a single expected percentage improvement for an upgrade. Measure your workload and record the conditions. Keep the application results as well as timings. A faster query returning the wrong business answer is not a successful regression test.

Rehearse the Cutover and the Return

Restore a representative copy and test with the application’s normal account. Include maintenance, recovery, and less frequent integrations. Record what was not exercised. A clean weekday smoke test doesn’t cover every month-end process.

Decide how to return if acceptance fails, including writes made after cutover. Backups from the newer engine cannot be restored to the older engine. Preserve the original recovery path and define the point where returning requires data reconciliation.

I want the production change to repeat a tested sequence. The old server is useful evidence while it still exists. Use that opportunity to replace assumptions with checks instead of discovering the hidden dependencies during downtime.

An upgrade is not a restore command, it is a test of the assumptions around your database.

This post was rewritten from scratch in September 2026. The original, published on 2008-12-18, was a short announcement about something that no longer exists. The address is the same, the subject is now something worth keeping.

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