Quick Scan Report – SQL Server 2005 End of Life

What this check looks for

A major version of 9, which is SQL Server 2005.

Extended support ended on 12 April 2016. SQL Server 2005 had a run of nearly eleven years and it has been retired for longer than some of the versions that replaced it.

Why it matters

Nothing breaks on the end of support date, which is exactly why an instance is still running this one. It works. It has worked for twenty years. That is the argument that has kept it alive and it is also the reason the risk has compounded.

What being here actually means:

• No security updates, and none coming. Every vulnerability found since April 2016 is open on this server permanently, and the details are published.
• No support. There is no case to open with Microsoft at any price.
• It fails every compliance regime. PCI DSS, HIPAA, SOC 2 and essentially every cyber insurance questionnaire treat unsupported database software as a finding. This is usually what finally funds the upgrade.
• The stack around it is as old. SQL Server 2005 does not run on a currently supported Windows Server, so the operating system underneath is unsupported too, and so are the drivers and client libraries. Modern application frameworks have dropped the client libraries it needs.
• The hardware is old. Twenty year old software is usually on old hardware with no warranty and no spares.

And the upgrade grows with every year of delay. Moving from SQL Server 2005 today means crossing five major versions at once: the cardinality estimator changed in 2014, syntax that 2005 accepted has been removed, and compatibility level 90 is not supported by any current version, so the databases cannot simply be attached and left.

There is one genuinely hard case, and it is the common one: a vendor application that is only certified against SQL Server 2005. That is worth naming honestly rather than pretending away. An application vendor who has not certified anything in a decade is telling you something about the product’s future, and the decision becomes a procurement question rather than a database one.

How to confirm it yourself

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

A ProductVersion starting 9. is SQL Server 2005.

What depends on it, which determines the size of the project:

-- linked servers pointing out of here
SELECT [name], [product], [provider], [data_source] FROM sys.servers WITH (NOLOCK) WHERE [server_id] > 0;

-- what is connecting in
SELECT DISTINCT [program_name], [host_name], [login_name], COUNT(*) AS [sessions]
  FROM sys.dm_exec_sessions WITH (NOLOCK)
 WHERE [is_user_process] = 1
 GROUP BY [program_name], [host_name], [login_name]
 ORDER BY [sessions] DESC;

-- and the databases themselves
SELECT [name], [compatibility_level], [recovery_model_desc], [collation_name], [create_date]
  FROM sys.databases WITH (NOLOCK) WHERE [database_id] > 4;

Run the connection query several times across a week. A quarterly job that connects once will not appear in a single sample, and it is exactly the sort of thing that breaks after a migration.

How to fix it

Migrate. The only real decision is to where and how.

1. Inventory what connects. The step that decides everything else, and the one most often skipped. Applications, reports, linked servers, jobs, ETL, and anything with a connection string mentioning this server.
2. Assess what will break. Run the Migration Planner report, or Microsoft’s free Data Migration Assistant. Both look for removed syntax and behaviour changes. From 2005 expect to find some: *= style outer joins, deprecated system tables, and sp_dboption.
3. Choose the target. The newest version your applications support. You are buying support runway, so take as much as you can.
4. Migrate side by side, not in place. Build a new server, restore the databases onto it, and test. It is reversible, it is testable, and it is an opportunity to fix the other findings in this report rather than carrying them forward. An in place upgrade from 2005 is not supported by current versions in any case, because the version gap is too large.
5. Handle the compatibility level deliberately. Restoring a 2005 database onto a modern instance raises it to the oldest level that version supports, which changes the query optimizer’s behaviour. Test with that in mind, and use Query Store on the new instance to catch regressions.

If you genuinely cannot move yet, reduce the exposure rather than accepting it: put the instance on an isolated network segment, remove any route to it that is not needed, tighten the logins, and make sure the backups work and have been restored. Those are mitigations with an expiry date, not a resolution.

Stedman Solutions does migrations, including from versions this old: databasehealth.com.

How long it takes

A week of effort is realistic for one instance with a few databases, spread over a longer period for testing and a cutover window. The assessment on its own is a day and is worth doing first, because it tells you whether the rest is a week or a quarter.

Related reports

Related checks

Frequently asked questions

It has run without trouble for twenty years. It has, and the risk is not that it stops. It is that every vulnerability found since April 2016 is open, and that no help exists when something does go wrong.

Our vendor only supports SQL Server 2005. Then this is a procurement decision. A vendor who has not certified a database release in a decade is describing their product’s trajectory.

Can we upgrade in place? Not to a current version. The supported upgrade paths do not span that many releases. Side by side is the route, and it is the better one anyway.

Is Express edition affected? Yes. Support dates are per version, not per edition.