It’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.
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.
- One and done! It’s very easy and fairly quick to click through the installer and get the job done.
- 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:
- 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.
- 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.
EPM applications help measure the business performance. This post will help you choose the best EPM solutions for your organization’s needs and objectives.
Imagine there are over one hundred logins in the source server and you need to migrate them all over to the destination server. Wouldn’t it be awesome if we could automate the process by generating the scripts for the required tasks?