Part of the DBA-Tools Project.
Before You Upgrade: What’s Actually Going to Break
Migration Risk Assessment covers per-database risk findings when moving to different hardware or infrastructure. This post covers a narrower, earlier question: on THIS server, staying on the same infrastructure, what needs attention before a version upgrade?
Three scripts, three angles on the same question: Version Upgrade Readiness is the wide instance-level summary (version, compat levels across every database, configuration items worth reviewing, sizing for the migration window). Compatibility Level Audit is the focused, single-purpose drill-down on just the compat level question. Deprecated Features In Use answers a completely different question: has anything actually called a feature that’s going away.
Why Version Upgrade Readiness Matters
- Databases left on an old compatibility level after an instance upgrade don’t get the new optimizer behavior, silently missing out on improvements the upgrade was partly meant to deliver
- Deprecated feature usage is invisible until something breaks post-upgrade, this is the one honest way to check whether anything’s actually calling a feature scheduled for removal
- Configuration defaults that made sense years ago (cost threshold for parallelism, ad hoc plan caching) are worth revisiting at upgrade time, not left on autopilot forever
- Direct in-place upgrade paths are version-specific and change over time, assuming “it’ll just upgrade” without checking the actual supported path is how upgrades get stuck mid-project
When to Run These Scripts
- Early in any version upgrade planning conversation, before committing to a timeline
- Alongside Migration Risk Assessment when the upgrade also involves new hardware or infrastructure
- Periodically even without a planned upgrade, to catch compat-level drift and deprecated feature usage before they pile up
- Before quoting a migration window to stakeholders, the sizing summary in Version Upgrade Readiness gives real numbers to work from
The Scripts
Get-VersionUpgradeReadiness — The Wide Instance-Level Summary
Returns four result sets in one pass: instance summary with supported upgrade paths, a compatibility level matrix across every database, a configuration review, and a sizing summary for migration window planning.
/*
Script Name : Get-VersionUpgradeReadiness
Category : migration
Purpose : Pre-upgrade readiness summary for SQL Server version upgrades.
Complements Get-DeprecatedFeaturesInUse.sql (feature detail) and
Get-MigrationRiskAssessment.sql (per-database risk). Run on SOURCE.
Author : Peter Whyte (https://sqldba.blog/dba-scripts-get-version-upgrade-readiness/)
Requires : VIEW ANY DATABASE, VIEW SERVER STATE
*/
-- Blog: https://sqldba.blog/dba-scripts-get-version-upgrade-readiness/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;
DECLARE @major INT = CAST(SERVERPROPERTY('ProductMajorVersion') AS INT);
DECLARE @version NVARCHAR(20) = CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(20));
DECLARE @level NVARCHAR(20) = CAST(SERVERPROPERTY('ProductLevel') AS NVARCHAR(20));
DECLARE @edition NVARCHAR(128)= CAST(SERVERPROPERTY('Edition') AS NVARCHAR(128));
DECLARE @collation NVARCHAR(128)= CAST(SERVERPROPERTY('Collation') AS NVARCHAR(128));
DECLARE @nativeCompat SMALLINT;
DECLARE @upgradeNote NVARCHAR(400);
SET @nativeCompat =
CASE @major
WHEN 17 THEN 170 -- SQL 2025
WHEN 16 THEN 160 -- SQL 2022
WHEN 15 THEN 150 -- SQL 2019
WHEN 14 THEN 140 -- SQL 2017
WHEN 13 THEN 130 -- SQL 2016
WHEN 12 THEN 120 -- SQL 2014
WHEN 11 THEN 110 -- SQL 2012
WHEN 10 THEN 100 -- SQL 2008/2008R2
WHEN 9 THEN 90 -- SQL 2005
ELSE NULL -- newer than this script knows about, or older than SQL 2005
END;
SET @upgradeNote =
CASE @major
WHEN 17 THEN 'SQL 2025 is current GA release. No direct upgrade target beyond this.'
WHEN 16 THEN 'Direct upgrade supported to: SQL 2025.'
WHEN 15 THEN 'Direct upgrade supported to: SQL 2022, SQL 2025.'
WHEN 14 THEN 'Direct upgrade supported to: SQL 2019, SQL 2022, SQL 2025.'
WHEN 13 THEN 'Direct upgrade supported to: SQL 2019, SQL 2022, SQL 2025.'
WHEN 12 THEN 'Direct upgrade supported to: SQL 2016, SQL 2017, SQL 2019, SQL 2022.'
WHEN 11 THEN 'Direct upgrade supported to: SQL 2016, SQL 2017, SQL 2019, SQL 2022.'
WHEN 10 THEN 'Direct in-place upgrade NOT supported to SQL 2017+. Upgrade to SQL 2014 or SQL 2016 first, or use side-by-side migration.'
WHEN 9 THEN 'Very old version — side-by-side migration strongly recommended. No direct in-place upgrade path to current versions.'
ELSE 'Newer than this script''s known version table — update Get-VersionUpgradeReadiness.sql with this release before trusting the compat-level and upgrade-path columns.'
END;
SELECT
@version AS current_version,
@level AS product_level,
@edition AS edition,
@collation AS server_collation,
@nativeCompat AS native_compat_level,
(SELECT value_in_use FROM sys.configurations WHERE name = 'max server memory (MB)')
AS max_server_memory_mb,
(SELECT value_in_use FROM sys.configurations WHERE name = 'max degree of parallelism')
AS maxdop,
(SELECT sqlserver_start_time FROM sys.dm_os_sys_info)
AS last_restart,
DATEDIFF(DAY, (SELECT sqlserver_start_time FROM sys.dm_os_sys_info), GETDATE())
AS days_since_restart,
@upgradeNote AS upgrade_paths;
-- Compatibility level matrix, configuration review, and sizing summary follow
-- in the same script; see the full source for all four result sets.
Get-CompatibilityLevelAudit — The Focused Compat-Level Drill-Down
/*
Script Name : Get-CompatibilityLevelAudit
Category : migration
Purpose : Lists all user databases with current compatibility level, equivalent SQL version name, and the instance's native compatibility level. Use to plan compat level upgrades before or after migration.
Author : Peter Whyte (https://sqldba.blog/dba-scripts-get-version-upgrade-readiness/)
Requires : VIEW ANY DATABASE
*/
-- Blog: https://sqldba.blog/dba-scripts-get-version-upgrade-readiness/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;
DECLARE @instance_major INT = CAST(SERVERPROPERTY('ProductMajorVersion') AS INT);
DECLARE @instance_compat SMALLINT =
CASE @instance_major
WHEN 17 THEN 170 -- SQL Server 2025
WHEN 16 THEN 160 -- SQL Server 2022
WHEN 15 THEN 150 -- SQL Server 2019
WHEN 14 THEN 140 -- SQL Server 2017
WHEN 13 THEN 130 -- SQL Server 2016
WHEN 12 THEN 120 -- SQL Server 2014
WHEN 11 THEN 110 -- SQL Server 2012
WHEN 10 THEN 100 -- SQL Server 2008/R2
WHEN 9 THEN 90 -- SQL Server 2005
ELSE NULL -- newer than this script knows about, or older than SQL 2005
END;
SELECT
d.name AS database_name,
d.compatibility_level AS current_compat,
CASE d.compatibility_level
WHEN 170 THEN 'SQL Server 2025'
WHEN 160 THEN 'SQL Server 2022'
WHEN 150 THEN 'SQL Server 2019'
WHEN 140 THEN 'SQL Server 2017'
WHEN 130 THEN 'SQL Server 2016'
WHEN 120 THEN 'SQL Server 2014'
WHEN 110 THEN 'SQL Server 2012'
WHEN 100 THEN 'SQL Server 2008/2008 R2'
WHEN 90 THEN 'SQL Server 2005'
WHEN 80 THEN 'SQL Server 2000'
ELSE 'Unknown'
END AS current_compat_version,
@instance_compat AS instance_native_compat,
CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(20))
AS instance_version,
CASE
WHEN @instance_compat IS NULL THEN 'UNKNOWN — update this script''s version table'
WHEN d.compatibility_level < (@instance_compat - 20) THEN 'NEEDS UPGRADE'
WHEN d.compatibility_level < @instance_compat THEN 'BELOW NATIVE'
WHEN d.compatibility_level = @instance_compat THEN 'AT NATIVE'
ELSE 'ABOVE NATIVE'
END AS compat_status,
CASE @instance_compat
WHEN 170 THEN 'See SQL Server 2025 documentation for compat level 170 changes'
WHEN 160 THEN 'Parameter-sensitive plan optimization, DOP feedback, CE model 160'
WHEN 150 THEN 'Scalar UDF inlining, table variable deferred compilation, batch mode on rowstore'
WHEN 140 THEN 'Batch mode memory grant feedback, interleaved execution, adaptive joins'
WHEN 130 THEN 'Live query statistics, DML with OUTPUT INTO reads inserted'
ELSE NULL
END AS features_unlocked_at_native_compat
FROM sys.databases d
WHERE d.name NOT IN ('master', 'model', 'msdb', 'tempdb')
AND d.state = 0
ORDER BY d.compatibility_level, d.name;
Get-DeprecatedFeaturesInUse — Has Anything Actually Called a Deprecated Feature
/*
Script Name : Get-DeprecatedFeaturesInUse
Category : migration
Purpose : Lists deprecated SQL Server features used since the last service restart, ranked by usage count. Zero rows means no deprecated features have been called.
Author : Peter Whyte (https://sqldba.blog/dba-scripts-get-version-upgrade-readiness/)
Requires : VIEW SERVER STATE
*/
-- Blog: https://sqldba.blog/dba-scripts-get-version-upgrade-readiness/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;
SELECT
pc.instance_name AS deprecated_feature,
pc.cntr_value AS usage_count_since_restart,
CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(20)) AS instance_version,
CAST(SERVERPROPERTY('ProductLevel') AS VARCHAR(20)) AS product_level,
si.sqlserver_start_time AS last_restart,
DATEDIFF(DAY, si.sqlserver_start_time, GETDATE()) AS days_since_restart
FROM sys.dm_os_performance_counters pc
CROSS JOIN (
SELECT sqlserver_start_time FROM sys.dm_os_sys_info
) si
WHERE pc.object_name LIKE '%Deprecated Features%'
AND pc.cntr_value > 0
ORDER BY pc.cntr_value DESC, pc.instance_name;
This reads a performance counter, not a static feature list, so it only ever shows features that have genuinely been called since the last restart, zero rows is a real, honest “nothing deprecated has run” result, not a sign the check didn’t work.
How To Run From The Repo
Clone DBA Tools, initialize and run any of the three:
# Clone dba-tools repo:
git clone https://github.com/peterwhyte-lgtm/dba-tools
# Initialize environment:
cd dba-tools
.\Initialize-Environment.ps1
# Wide instance-level upgrade readiness summary:
.\run.ps1 Get-VersionUpgradeReadiness
# Focused compatibility level audit, every database, one pass:
.\run.ps1 Get-CompatibilityLevelAudit
# Has anything actually called a deprecated feature since the last restart:
.\run.ps1 Get-DeprecatedFeaturesInUse
# To run against a remote sql server:
.\run.ps1 Get-VersionUpgradeReadiness -ServerInstance SQLSERVER01
These scripts live in the repo at:
sql/migration/Get-VersionUpgradeReadiness.sqlsql/migration/Get-CompatibilityLevelAudit.sqlsql/migration/Get-DeprecatedFeaturesInUse.sql
Example Output
Real output from this lab instance (SQL Server 2025), not staged:
Instance summary:
| current_version | native_compat_level | upgrade_paths |
|---|---|---|
| 17.0.4045.5 | 170 | SQL 2025 is current GA release. No direct upgrade target beyond this. |
Compatibility level audit, all 20 databases:
Every database on this instance is at compat level 170, AT NATIVE, a genuinely clean result, nothing left behind after this instance’s own setup.
Deprecated features in use, since last restart:
| deprecated_feature | usage_count_since_restart |
|---|---|
| syslogins | 760 |
| Data types: text ntext or image | 457 |
| String literals as column aliases | 300 |
| ‘::’ function calling syntax | 225 |
| XP_API | 100 |
| fn_trace_gettable | 76 |
| fn_trace_getinfo | 75 |
| sql_dependencies | 3 |
| fulltext_catalogs.path | 1 |
| numbered_procedures | 1 |
| sysdatabases | 1 |
That deprecated-features result is a real, unmanufactured finding, 11 distinct deprecated features called on this lab instance since its last restart, fn_trace_gettable and fn_trace_getinfo among them because several scripts elsewhere in this repo still read the default trace.
Two Real Bugs Found Writing This Post
Testing these scripts against this lab instance (SQL Server 2025, major version 17) surfaced two genuine bugs, both the same root cause: neither script’s version-mapping table had been updated since SQL Server 2025 released.
Get-VersionUpgradeReadiness.sql reported native_compat_level = 90 and upgrade_paths = "Very old version, side-by-side migration strongly recommended" on a brand-new SQL Server 2025 instance, because its CASE @major mapping only went up to major version 16 (SQL 2022) and silently fell through to the ELSE 90 branch for anything newer. Fixed by adding an explicit WHEN 17 THEN 170 case, and changed the fallback ELSE to return NULL with an honest note rather than a confidently wrong old-version number.
Get-CompatibilityLevelAudit.sql had the identical bug in its own separate @instance_compat mapping, plus a second copy of the same gap in its current_compat_version CASE (no WHEN 170 entry), so every database correctly at native compat level 170 was reported as current_compat_version = 'Unknown' and compat_status = 'ABOVE NATIVE', exactly backwards from reality. Fixed the same way, plus added the missing WHEN 170 label.
Both fixes were re-verified against HPAI01 and now correctly report SQL Server 2025, compat level 170, and AT NATIVE. This is a good example of why “version-mapping tables in scripts need updating when a new SQL Server version ships” isn’t a hypothetical maintenance chore, it’s exactly the kind of bug that silently produces confidently wrong output on the newest supported version, the one place you’d least expect to find a bug like this.
Understanding the Results
- upgrade_paths on the instance summary — check this before committing to any upgrade timeline, direct in-place support is genuinely version-specific and changes with each release
- compat_status = NEEDS UPGRADE — a database more than 20 points behind native compat level; missing multiple releases of optimizer improvements, not just the most recent one
- compat_status = ABOVE NATIVE — should not normally happen; if you see this, check whether the instance was actually downgraded or the compat level was set manually to something higher than the instance supports
- Any row at all from Deprecated Features In Use — cross-reference against Microsoft’s deprecated features list for the target version specifically, not just “deprecated in general,” some features are deprecated for years before actual removal
Best Practices
- Run all three well before committing to an upgrade date, not during the maintenance window itself
- Fix compat-level drift opportunistically even outside a migration project, a database several versions behind native is missing real optimizer improvements right now
- Treat any deprecated feature usage as a concrete pre-upgrade task: find what’s calling it, and confirm a replacement exists before the target version removes it
- Re-run Deprecated Features In Use after a reasonable production workload window, not immediately after a restart, since it only reflects activity since the last restart
Related Scripts
You may also find these scripts useful:
Frequently Asked Questions
Do I need to update compatibility level as part of a version upgrade?
Not automatically, and not required for the upgrade itself to succeed. But leaving databases on an old compat level after upgrading the instance means missing the new optimizer behavior the upgrade was partly meant to deliver. Test at the new compat level in a non-production environment before flipping it in production.
What’s the difference between this and Migration Risk Assessment?
Migration Risk Assessment covers per-database risk when moving to different infrastructure. This post is narrower and earlier: on the same infrastructure, is this instance and its databases ready for a version upgrade specifically, compat level drift, deprecated feature usage, and configuration review.
Summary
A version upgrade fails quietly in a few predictable ways: databases left behind on an old compat level, a deprecated feature nobody knew was still in use, or a configuration default nobody’s reviewed since the last upgrade. These three scripts check all of it in one pass. Finding two real version-mapping bugs while testing them on the newest supported SQL Server version is itself the whole point of this series, actually running a script against a real instance surfaces exactly the bugs a code review alone would miss.
Run all three early in any upgrade conversation, and keep the version-mapping tables in both scripts updated the next time a new SQL Server release ships.
Leave a Reply