DBA Scripts: Get Version Upgrade Readiness

---

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;

/*
  DESIGN: Returns four result sets:
    1. Instance summary — current version, edition, and supported direct upgrade paths
    2. Compatibility level matrix — which databases are behind native compat level
    3. Configuration delta — sp_configure items that have changed defaults or behaviour in newer versions
    4. Sizing summary — data/log totals per database for migration window planning

  Use alongside:
    Get-DeprecatedFeaturesInUse.sql — deprecated features called since last restart
    Get-MigrationRiskAssessment.sql — per-database risk findings (compat, settings, AG, sizing)
    Get-EditionFeatureUsage.sql — Enterprise-only features (if changing edition at same time)
*/

-- ── 1. Instance summary ───────────────────────────────────────────────────────

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;

-- ── 2. Compatibility level matrix ─────────────────────────────────────────────

SELECT
    d.name AS database_name,
    d.compatibility_level AS current_compat_level,
    @nativeCompat AS native_compat_level,
    @nativeCompat - d.compatibility_level AS compat_gap,
    CASE
        WHEN d.compatibility_level >= @nativeCompat THEN 'OK — at native level'
        WHEN d.compatibility_level = @nativeCompat - 10 THEN 'INFO — 1 version behind'
        WHEN d.compatibility_level = @nativeCompat - 20 THEN 'WARN — 2 versions behind'
        ELSE 'HIGH — severely behind native level'
    END AS compat_status,
    d.recovery_model_desc AS recovery_model,
    d.state_desc AS database_state
FROM sys.databases d
WHERE d.database_id > 4
ORDER BY compat_gap DESC, d.name;

-- ── 3. Configuration items to review for target version ───────────────────────

;WITH config_review AS (
    SELECT
        name AS config_name,
        CAST(value_in_use AS BIGINT) AS current_value,
        CAST(minimum AS BIGINT) AS minimum,
        CAST(maximum AS BIGINT) AS maximum,
        is_advanced,
        CASE
            -- max server memory at SQL Server default (likely unconfigured)
            WHEN name = 'max server memory (MB)' AND value_in_use >= 2147483647
                THEN 'HIGH — Unconfigured. Set this before cutover to target to prevent memory pressure.'
            -- MAXDOP 0 (uses all CPUs) — newer guidance prefers explicit value
            WHEN name = 'max degree of parallelism' AND value_in_use = 0
                THEN 'INFO — MAXDOP = 0 (uses all CPUs). Set to min(8, CPU count / 2) unless validated.'
            -- Cost threshold default (5) — very low for modern hardware
            WHEN name = 'cost threshold for parallelism' AND value_in_use <= 5
                THEN 'INFO — Cost threshold = 5 (default). Consider 50+ on modern hardware to reduce parallelism noise.'
            -- Optimize for ad hoc workloads — should be ON
            WHEN name = 'optimize for ad hoc workloads' AND value_in_use = 0
                THEN 'WARN — Disabled. Enable to reduce single-use plan cache bloat (sp_configure ''optimize for ad hoc workloads'', 1).'
            -- Backup checksum — should be ON for new installs
            WHEN name = 'backup checksum default' AND value_in_use = 0
                THEN 'INFO — Backup checksums off. Enable for stronger backup integrity checks.'
            -- Remote query timeout default is 600s — may want to review
            WHEN name = 'remote query timeout (s)' AND value_in_use = 600
                THEN 'INFO — Remote query timeout at default 600s. Review if linked servers are in use.'
            ELSE 'OK'
        END AS review_note
    FROM sys.configurations
    WHERE name IN (
        'max server memory (MB)',
        'min server memory (MB)',
        'max degree of parallelism',
        'cost threshold for parallelism',
        'optimize for ad hoc workloads',
        'backup compression default',
        'backup checksum default',
        'remote query timeout (s)',
        'remote login timeout (s)',
        'lightweight pooling',
        'priority boost',
        'clr enabled',
        'clr strict security',
        'cross db ownership chaining',
        'Database Mail XPs',
        'xp_cmdshell'
    )
)
SELECT *
FROM config_review
ORDER BY
    CASE
        WHEN review_note LIKE 'HIGH%' THEN 1
        WHEN review_note LIKE 'WARN%' THEN 2
        WHEN review_note LIKE 'INFO%' THEN 3
        ELSE 4
    END,
    config_name;

-- ── 4. Sizing summary — for migration window planning ─────────────────────────

SELECT
    d.name AS database_name,
    d.recovery_model_desc AS recovery_model,
    d.compatibility_level AS compat_level,
    CAST(SUM(CASE WHEN mf.type = 0 THEN mf.size ELSE 0 END) * 8.0 / 1024 / 1024 AS DECIMAL(12,2)) AS data_size_gb,
    CAST(SUM(CASE WHEN mf.type = 1 THEN mf.size ELSE 0 END) * 8.0 / 1024 / 1024 AS DECIMAL(12,2)) AS log_size_gb,
    CAST(SUM(mf.size) * 8.0 / 1024 / 1024 AS DECIMAL(12,2)) AS total_size_gb
FROM sys.databases d
INNER JOIN sys.master_files mf ON d.database_id = mf.database_id
WHERE d.database_id > 4
GROUP BY d.name, d.recovery_model_desc, d.compatibility_level
ORDER BY total_size_gb DESC;

---

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.sql
• sql/migration/Get-CompatibilityLevelAudit.sql
• sql/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 in the Version-Mapping Tables

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 PWSQL01 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:

• Migration Risk Assessment
• Edition Feature Usage
• Patch Level
• Database Inventory
• DBA Scripts: The Complete Guide, the map across every script on this site

---

Frequently Asked Questions

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.