How to Migrate from SQL 2005 to 2014

On April 12, 2016 Microsoft will no longer offer extended support for SQL Server 2005.  This means no more security updates or hotfixes.  However, the bigger reason to upgrade is the fantastic performance improvements offered in SQL 2014.  SQL Server 2014 has been shown to be 13X faster than 2005, and that is before taking advantage of the in-memory OLTP.  In addition there are features such as AlwaysOn availability groups, updateable columnstore indexes, T-SQL Intellisense and backups directly to the cloud, just to name a few.  For a more complete listing read What’s New in SQL Server 2014 since SQL Server 2005.

Preparation is key.  Microsoft has provided a number of tools to make migrating your databases easier.  You’ll want to use these tools to assess and plan your migration well ahead of time.  Support for Windows Server 2003 is also ending soon, which may impact your decision making.  Part of your assessment will be to ensure your environment is suitable for SQL Server 2014 before migrating your databases. SQL Server 2014, of course, has minimum hardware and software requirements.   Microsoft recommends a minimum of 4GB of memory, to be increased as database size increases, and processor speed of 2.0GHz or faster. The OS depends on which version of SQL Server you are installing. 

There are two ways to migrate from SQL Server 2005 to SQL Server 2014.  You can do a clean install and restore from backup, or you can do an in-place upgrade. The clean install may sound appealing from a Windows registry and system stability standpoint.  However it means restoring all of your databases, and any custom settings and properties you may have changed.  Over 10 years a lot of tweaks to settings can have been made, and it could be very time consuming to restore them all.  It may even mean purchasing another OS license.

You can do an in-place upgrade to SQL Server 2014 from any version as far back as SQL Server 2005.  You cannot do the in-place upgrade for versions prior to that.  You would need to upgrade a prior version to 2005 or 2008 and then upgrade again to 2014. You do need to consider the edition you are upgrading from, Standard, Developer, Enterprise, etc to determine the supported upgrade path.  With the SQL Server 2014 in-place upgrade you will be able to upgrade SSIS packages, SSAS and SSRS.

I highly recommend you install and run the Upgrade Advisor prior to doing the in-place upgrade.  This tool will analyze components of your previous version and identify issues you may need to address before upgrading to SQL Server 2014.  Upgrade Advisor can be installed remotely to analyze all supported components of SQL Server, except SSRS. The remote computer where you are installing Upgrade Advisor must meet the Upgrade Advisor prerequisites.  If you want to analyze SSRS you must install Upgrade Advisor on the report server itself.

You can install the Upgrade Advisor from the the SQL Server setup, or download it from Microsoft. 

image

In addition to the Upgrade Advisor, I found this great resource, Upgrading to SQL Server 2014: A Dozen Things to Check, written by Thomas LaRock. It is not specific to migrating from SQL Server 2005, but it has some great tips for preparing for a smooth SQL upgrade.

Once you have addressed any pre-installation issues, you are ready to perform the upgrade.  I won’t run through the step-by-step as there are many articles documenting this, such as this one.

There are many great reasons to upgrade your SQL Server, and with the end of extended support in sight, now seems like a good time.  Preparation for your upgrade is key, and Microsoft has provided lots of tools to make it easier.