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).

Installed Version Can Upgrade To Compatibility Level
SQL Server 6.5 (65) SQL Server 2000 2000: 80, 70, 65
SQL Server 7.0 (70) SQL Server 2005 2005: 90, 80, 70
SQL Server 2000 (80) SQL Server 2008/R2 2008/R2: 100, 90, 80
SQL Server 2005 (90) SQL Server 2012 2012: 110, 100, 90
SQL Server 2008/R2 (100) SQL Server 2014 2014: 120, 110, 100
SQL Server 2012 (110) SQL Server 2016 2016: 130, 120, 110
SQL Server 2014 (120) SQL Server 2017 2017: 140, 130, 120

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.

Author: randolph

Randolph West is a Microsoft Data Platform MVP, and has worked with SQL Server since the late 1990s. When not consulting, he can be seen acting on the stage and screen, or doing voices for independent video games. Randolph is available for talks on SQL Server, and technology in general. He also offers training for junior DBAs. Connect with Randolph on Google+ or Twitter.