SQL Server 2005 Version Numbers

What this check looks for

A major version of 9, which is SQL Server 2005, from SERVERPROPERTY('ProductVersion'). The message reports the exact build you are on.

Why it matters

SQL Server 2005 left extended support in April 2016. Nothing has been released for it since, including security fixes.

That changes what this finding means. On a supported version, “updates available” is a task: apply the cumulative update in the next window. On 2005 there is nothing to apply. The final build is SP4 with its last security update, and being current on 2005 still means running software that has received no fix of any kind for the better part of a decade.

What that actually exposes you to:

  • Unpatched vulnerabilities, permanently. Anything found since 2016 is unfixed by definition, and the details of the last fixes made before that date remain a map of what to try.
  • Compliance failure. Unsupported database software is an explicit finding in most audit frameworks, and it is one with no remediation short of migration.
  • No support path. Microsoft will not take a case. When something breaks in a way that needs a vendor, there is nobody to ask.
  • Hardware and operating system limits. 2005 will not install on current Windows Server, so the instance is also pinning an old operating system, which has its own end of support.
  • Driver and client attrition. Current versions of ODBC, JDBC and .NET data providers have dropped 2005 compatibility, so upgrading the application becomes blocked by the database.

And a large amount of engineering you are not getting. Everything since 2005 is absent: Availability Groups, online index rebuilds beyond what Enterprise 2005 offered, column store, the modern cardinality estimator, Query Store, backup compression, TDE, filtered indexes, partition improvements, TRY_CONVERT, window function improvements, and the DMVs that most diagnostic work now relies on.

Why these instances survive is usually a vendor application that was never recertified, and that is the thing to confront rather than the version number.

How to confirm it yourself

The version, plainly:

SELECT SERVERPROPERTY('ProductVersion') AS [build],
       SERVERPROPERTY('ProductLevel')   AS [service_pack],
       SERVERPROPERTY('Edition')        AS [edition],
       @@VERSION                        AS [full_version];

A major version of 9 is 2005. 9.00.5000 and above is SP4, which is as far as it goes.

What is actually on it, which decides how big the migration is:

SELECT [name],
       [compatibility_level],
       [recovery_model_desc],
       [collation_name],
       [create_date]
  FROM sys.databases
 WHERE [database_id] > 4
 ORDER BY [name];

What connects to it, which is the harder half of the question:

SELECT DISTINCT [program_name], [host_name], [login_name], [client_interface_name]
  FROM sys.dm_exec_sessions
 WHERE [is_user_process] = 1
 ORDER BY [program_name];

Run that repeatedly over a week rather than once. The application nobody remembered connects monthly.

And features that will not carry forward unchanged:

-- notification services, database mirroring witnesses, DTS packages,
-- and anything using a deprecated syntax
SELECT [object_name], [counter_name], [instance_name], [cntr_value]
  FROM sys.dm_os_performance_counters
 WHERE [object_name] LIKE '%Deprecated Features%'
   AND [cntr_value] > 0;

That counter list is the single most useful preparation for the move, because it names the syntax the upgrade will break.

How to fix it

The fix is migration to a supported version. There is no patch.

  1. Decide the target. Move to a currently supported version rather than the next one up. The work of migrating is largely the same regardless of how far you jump, and jumping to an older target only means doing it again sooner.
  2. Note the upgrade path constraint. A direct restore from 2005 is not supported by current versions. In practice the route is either a two-step restore through an intermediate version, or a rebuild-and-import: script the schema, create the database on the new instance, and move the data.
  3. Inventory what depends on it, from the session query above, plus linked servers, SSIS or DTS packages, Agent jobs, replication and any application connection string you can find.
  4. Run the deprecated feature list and fix the syntax before the move rather than during it.
  5. Migrate the instance level objects too, which is the part most often missed: logins with their SIDs, Agent jobs, operators and alerts, linked servers, credentials, and any non default sp_configure settings.
  6. Set compatibility level deliberately after the move. Restoring an old database leaves it at a low compatibility level, which keeps old optimizer behavior. Raise it once the application has been tested, and use Query Store to catch regressions:
ALTER DATABASE [YourDatabase] SET COMPATIBILITY_LEVEL = 160;
  1. Run DBCC CHECKDB WITH DATA_PURITY on the migrated database. Databases created on 2005 and earlier do not have data purity checks enabled by default, and the upgrade is the right moment to prove the values are valid:
DBCC CHECKDB ([YourDatabase]) WITH DATA_PURITY, NO_INFOMSGS;
  1. Update statistics with a full scan afterwards. Statistics carried across from an old version are the usual reason a migrated database performs worse for a week.

If a vendor application is the blocker, get their supported version matrix in writing. That document either unblocks the move or is the evidence that the application itself needs replacing, and either outcome is progress.

If migration genuinely cannot happen this year, reduce the exposure: isolate the instance on its own network segment, restrict access to the minimum set of hosts, ensure backups are taken and tested off the machine, and record the risk formally so it is a decision rather than a drift.

How long it takes

The check estimates three hours, which is the assessment and planning. The migration itself is a project measured in weeks, and the size is set by the application testing rather than by the database work.

Report Why you would go there
Server Overview Version, edition and build.
Databases What has to move, and how large.
Logins The instance level objects to recreate.
Linked Servers Dependencies in both directions.
Job Commands Agent work that has to come across.
Connections Which applications actually use it.
Check
Updates for SQL Server available The same finding on a version that can still be patched.
Database compatibility level What to set after the move.
Databases not checked with CHECKDB Worth resolving before migrating.
Old backups The thing to verify before any migration.

Frequently asked questions

Is there a final update I can still apply? SP4 plus its last security update is the end of the line. If you are below that, apply it, but it does not make the instance supported.

Can I restore a 2005 backup straight onto a current version? No. Current versions do not support that upgrade path directly. You either step through an intermediate version or rebuild the schema and move the data.

The application vendor says they only certify 2005. Ask for that in writing along with their roadmap. In most cases the certification is simply old rather than a technical limit, and the written answer is what moves the conversation.

It has run for years without trouble. That is true and it is not the risk. The risk is the unpatched vulnerability, the audit finding, and having no support path on the day it does go wrong.