What is your SQL Server 2005 Upgrade Plan?

SQL Server 2005 will no longer be supported by Microsoft as of 12 April 2016.

One of the services I offer my customers is an upgrade from any* version of SQL Server to the latest and greatest version.

It is helpful to remember that version upgrades are quite tricky. The database engine does not support upgrades of more than two major version numbers at a time.

That means that, if you were to upgrade to SQL Server 2014 or 2016, you would need to have an intermediate step if you’re coming from anything before 2008.

Note: this rule applies to in-place upgrades only. If you are migrating to a new server and perform a backup/restore or detach/reattach, any database from 2005 and upwards does not require this intermediate step.

Even with upgrading to a higher version, you can still run your database in the same compatibility level as the version you’re upgrading from if it is in that two version window. This gives you compatibility with legacy code and applications that assume they are working with an older version.

For example, if you upgrade a SQL Server 2005 instance to SQL Server 2012, you can still run the database in compatibility level 90, which is equivalent to SQL Server 2005.

In fact, both SQL Server 2014 and 2016 allow you to attach a SQL Server 2005 database, but the minimum compatibility level will be automatically updated (Source).

Thomas LaRock covers some of this in a good checklist to look through when you’re considering an upgrade to SQL Server 2014.

* Any version of Microsoft SQL Server from 6.5 and higher.