Tips for Upgrading From SQL 2008 to 2012 or 2014

By | In Blog, SQL Server | April 08th, 2015

Upgrade SQL Server Tips from DatavailIt’s 2015 and you can now establish totally respectable MS SQL DBA credibility just by mentioning you have been in the game since SQL Server version 9. You may even get the same gasps of shock from some colleagues that used to be reserved for the version 6 veterans. It’s true – SQL 2005 is 10 years old and if you’ve been working with SQL Server that long it’s likely you’re a seasoned pro. However unlike DBAs, a given SQL version does not get better with age (well not after SP4 anyway.)

Take SQL 2008 for instance, mainstream support for this version expired in 2014 and its technology has been superseded by the new features and valuable improvements of recent SQL versions. Sure, some SQL 2008 instances will be around – and DBAs will be supporting them for many more years. But over the next few years many other SQL 2008 (and earlier) instances and databases will be making the migration to SQL 2012 or SQL 2014. Here’s part one of a post on bringing these older versions up to date as smoothly and succinctly as possible. Make sure to stay tuned for part two.

Read our latest white paper Upgrading SQL Server to 2012 and Beyond on ways to optimize performance and learn about new features to help improve security.

Upgrade Techniques

Of course we want the upgrade project to be as streamlined and efficient as possible. An in-place upgrade provides this, since it simply versions-up the binaries of the existing install. But a version upgrade can be an opportunity to make some potentially important changes. Should the instance also be migrating to newer OS versions or moving to improved hardware? Can several instances be consolidated? A side-by-side migration upgrade allows these kinds of tasks to be included in the upgrade project.

You Can’t Get There From Here

To begin, let’s identify what we cannot do. Some of these show stoppers may apply only to in-place upgrades but let’s get them all out on the table:

  • The OS must be at least 2008. SQL 2014 cannot be installed on Windows 2003.
  • The platform must be the same (in-place only). An instance cannot go from 32 to 64 nor 64 to 32.
  • Editions cannot downgrade (in-place only). An instance cannot go from EE to SE.
  • You can only migrate from 2008. Upgrading SQL 2005 will be a 2 step process since it must first be upgraded to 2008.

The Magician’s Table

In-place upgrades always make me think of the old Magician’s Table trick that everyone tried when they were kids. For SQL upgrades it’s like this: The older version, SQL 2008, is the table cloth. The SQL Install Executable sets the table with all the SQL 2014 china plates and crystal glasses. Then in one smooth and quick motion it pulls the table cloth out from under the dishes leaving a SQL 2014 instance where SQL 2008 used to be. It is like magic and it almost always works fine. But if things do go wrong, picking up the pieces can really ruin your chances for a lunch break (to say the least). Here are some pros and cons.

Pros

  • One and done! It’s very easy and fairly quick to click through the installer and get the job done.

Cons

  • No rolling back (not practically anyway.)
  • No opportunity for additional changes or optimizations. For example make a change to the OS version or the SQL edition.

Stepping Stones To The Next Version

Side-by-side migrations are more like stepping stones that allow us to get from one version to the next. The original 2008 version is stone A, and well ahead of the actual migration date we can set stone B, the SQL 2014 instance, in place. What is great about side-by-side migration is that if necessary, we can easily step back to the original instance. What’s more, we have the opportunity to pick and choose many aspects of the ‘new’ stone. Our migration can go down edition from enterprise to standard, cross platform from 32bit to 64bit, or to more optimal hardware, like faster CPUs. There is even an opportunity to choose a new hosting solution for the instance; a physical server, a virtual machine, or the cloud. This flexibility really stands out as an advantage over in-place upgrades. Here are the pros and cons:

Pros

  • Advance preparation – its easy to set up the new target server and instance ahead of time and pre-configure, allow a burn in period, do testing, etc.
  • Rollback options – if an upgrade migration does not go to plan, the original instance and data are intact and exist as a fail back option.
  • Opportunity to upgrade hardware – this may be especially relevant considering SQL Server is now licensed per core rather than socket. Current high core count servers can be migrated to low core count servers.
  • Opportunity to upgrade the operating system – ideally SQL 2012 and 2014 should be running on Windows 2012r2.
  • Opportunity to consolidate – newer hardware technologies and current SQL Server features may allow many older SQL instances to be consolidated onto a single machine.

Cons

  • Must manually move over all server objects – data migrations are typically automated at the database level but any critical server level objects must also be part of the migration plan and migrating these require some additional thought and project steps.
  • Must redirect the application connections after the migration. Typically this means a short outage as the application is switched to point to the new instance.
  • Additional hardware. The new server and the old server must be up and running concurrently. A workaround might be a VM or cloud host.

The Ideal Solution

Back in 2005 there was no cloud to speak of and VMs were just getting started. In 10 years we have two totally new technologies where we can host a SQL Instance. Physical servers have come a long way too; core count and speed, available RAM, and SSD disks have all changed the way SQL Server works on physical boxes. Let’s not leave out the OS. Perhaps the migration will be to a Windows 2012 Failover Cluster or to Windows 2012 Server Core. And don’t forget the recent changes in SQL Server licensing.

For any version-up migration it’s well worth the time to consider the all the available choices and pathways when making the move. If there are a minimum of complications then in-place is as easy as it gets. However, so much has changed between 2008 and 2014 versions, in most cases in-place migrations will not be practical and side-by-side will be the only viable option.

So tune in for my next post when we’ll take a closer look at the details of side-by-side migrations. Meanwhile if you are planning a migration project take a look at SQL Server 2014 Upgrade Technical Guide and/or check into my backlog of helpful blog posts.

Andy McDermid
Principal SQL Server DBA
Andy is a MCITP certified MS SQL DBA who delivers and manages delivery of DBA services to many diverse clients. He enjoys helping his clients by finding and deploying pragmatic and practical solutions for their database issues. Andy is always working to improve and expand his DBA skills and he likes to share the experience via writing.

Leave a Reply

Your email address will not be published.
Required fields are marked (*).

10 thoughts on “Tips for Upgrading From SQL 2008 to 2012 or 2014”
  1. Hi Andy,

    Awesome article.

    I am using sql server 2008 R2 and have replication setup there. Now planning to upgrade SQL Server 2014. Do I need to setup the replication again or any changes I need to do? Please help

    1. Hi Ashutosh,
      If you are doing a side by side upgrade, then yep, you’ll need to recreate the replication. If you are upgrading in-place you can use these steps:
      1. Stop/Disable Log Reader Agent
      2. Monitor Distribution Agent to make sure ALL transactions are replicated
      3. Stop/Disable Distribution Agent.
      4. Upgrade SQL Server.
      5. Enable/Start Log Reader
      6. Enable/Start Distribution Agent
      (thanks to my colleague Mike K. for this tip)
      Good luck!

    1. Hi Zahir, yes but per MS License Guide (http://bit.ly/1ChE6Ji) only if you have software assurance: “Version Upgrade Rights are offered as a Software Assurance (SA)
      benefit for qualified licenses and allow customers access to upgrade
      their deployments at no additional cost. Existing SQL Server 2012
      software licenses covered by SA are automatically upgraded to
      licenses for the corresponding SQL Server 2014 edition.”
      –HTH, Andy

      1. Hello, Andy. can you please help me this.Actually, we are using 2008r2 SQL server so planning to get in higher version so can anyone let me know the “Best course to get to higher version”

  2. We have an requirement to upgrade SQL database via SCCM. Can this be possible via SCCM? if so, please let me know the approach.

    Thanks

  3. Hi Andy. I work in an organization, where we have a huge database in SQL Server 2008 and we have to upgrade it in 2016. We all are using Windows 10. Can you suggest me the steps in short?

  4. We have a Microsoft SQL Server 2008 (SP4) – 10.0.6000.29 (X64) & a Microsoft SQL Server 2008 R2 (SP3) – 10.50.6000.34 (X64). 2 Databases each of 30 GB size.
    If we have to restore this data to the new Azure based IaaS SQL 2014 VM, how much complexity do you expect. Would it be smooth or do you see functional lapses and it may not work? Please advise,
    The Db objects we use are:
    Db Objects
    Tables
    Views
    Synonyms
    Stored Procedures
    Functions
    Triggers
    Indexes
    User Defined Tables