What’s the Best Upgrade Path from SQL 2005 to SQL 2014?

I get this question a decent amount in my Intro to SQL Server Management Session (called “I’ve got a SQL Database Now What?” or “SQL Server Management for the non Database Administrator”).

Frankly it doesn’t really matter. As long as you attach the database to a SQL Instance that is somewhere between SQL Server 2005 and SQL Server 2014 you’ll be fine. So why do we need to do this? Because Microsoft in their infinite wisdom has decided that they will only support upgrades from SQL Server 2008 and higher into SQL Server 2014. This presents a problem for those running SQL Server 2000 or SQL Server 2005 that want to jump directly into SQL Server 2014.

In order to make this upgrade happen, you need to attach the databases to a supported server. In the case of upgrading to SQL Server 2014 that means that you need to attach the database to a database instance running SQL Server 2008 through SQL Server 2012. If you are upgrading from SQL 2005 you can attach to any of those instances. If you are upgrading from SQL Server 2000 you’ll need to attach the databases to a SQL Server 2008 or SQL Server 2008 R2 instance. This is because SQL Server 2012 doesn’t support a direct upgrade from SQL Server 2000.

It makes no difference which version you attach to on the way to SQL Server 2014. The upgrade steps which need to be done between versions will be done in either location. And you don’t have to leave the database attached for very long. Just attach it, then detach it, then move it to the SQL 2014 server and attach it there for the final upgrade.

Just keep in mind that once you take that SQL 2005 instance and attach it to a SQL 2008 or higher instance you are committed and there’s no rolling back.

Denny