SQL Server with Mr. Denny

Dec 24 2014   5:00PM GMT

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

Denny Cherry Denny Cherry Profile: Denny Cherry

Tags:
SQL
SQL Server
SQL Server 2005
SQL Server 2008
SQL Server 2012
SQL Server 2014
SQL Server upgrades
Upgrades

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

2  Comments on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.
  • gregbems
    I have just this scenario. (4) SQL 2005 DB and a new server with SQL 2014. So I have to find 2008 SQL somewhere and take the databases there and attach. Will it automatically update to whatever it needs for 2008? Then detach and do the same to the 2014 server? It will then upgrade what it needs at the attachment time for that one too?
    10 pointsBadges:
    report
  • Denny Cherry
    Yep, that's exactly it.  When you attach the databases to the new version SQL will take care of upgrading the database. It should only take a few seconds to do the upgrade.
    68,960 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

Share this item with your network: