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

Share

2 Responses

  1. Hi Denny,

    I need to move my programs and database SQL 2005 from
    my current host to Go Daddy SQL 2014. I understand what you are saying
    conceptually. How do you do this exactly if you do not mind.

    best regards,

    David
    ( 514) 588-8324

  2. Do you have the ability to download a backup of the database, or the actual mdf/ldf files; and can you upload those files to GoDaddy and restore them to the database server?

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Trust DCAC with your data

Your data systems may be treading water today, but are they prepared for the next phase of your business growth?