Which Version Should Customers Upgrade to and Why?
Extended support for SQL Server 2005 ends on April 12, 2016, which means if you are still running 2005 after this time you will no longer receive security updates and hotfixes from Microsoft. According to the company, “Unsupported software means increasing vulnerability to data issues and exposing other devices and applications of SQL Server to these same vulnerabilities.”
Sure, migrating your infrastructure can seem daunting, but it’s imperative for organizations to upgrade to a modern data platform before the support deadline in order to maintain security and compliance.
We asked several SQL experts to share some insights on the various migration options available (such as SQL Server 2014), as well as the pros and cons of each. Here’s what they had to say…
Bill Ennis, Principal Database Consultant, Server Side Technology Solutions
When upgrading from SQL Server 2005, most should consider a hardware upgrade as well.
This allows for a side-by-side upgrade and has several advantages over an in-place upgrade.
Chances are you have been running on that hardware for several years and the cost/benefit of upgrading to newer hardware is worth the return on investment. Several options can be considered. CPU, RAM, and solid state storage costs have come down recently. An analysis of your current environment and identifying system bottlenecks can assist in knowing where to consider hardware upgrades. If a hardware upgrade is used to migrate to a newer SQL Server version, a simple backup/restore can be used for the migration (or log shipping if you need minimal downtime). Fallback is also greatly simplified with this approach in that your existing instance is left intact in case there is a need to roll back to the previous state.
Andy McDermid, SQL Server Database Administrator, Datavail
A number of things can complicate or prohibit an in-place upgrade directly to SQL 2014 from SQL 2005. These include hardware and OS requirements. For example, the supporting OS may be Windows Server 2008r2, but practically speaking, the right choice for SQL Server 2014 is Windows Server 2012. Also, it’s possible your license cost could significantly increase if you upgrade an existing box to SQL Server 2014 due to the Microsoft SQL Server licensing changes in 2012. Once you do commit to upgrading via a migration, aka a side-by-side, new opportunities open up and new questions arise. Perhaps the Edition can be downgraded to reduce license cost? Is it possible to consolidate the databases with others onto an enterprise instance? Is the instance a candidate for in-house virtualization or an Azure VM? Might the databases be moved to Microsoft Azure? So much is new since 2005 it’s worth at least considering all the options. Collect some baseline data on the old SQL 2005 server to help provision the preferred destination and be sure to account for future growth. Plan the migration according to acceptable down-time: attach/detach, backup/restore, log shipping, and database mirroring are viable methods depending on the requirements.
Debbi Lyons, Sr. Product Marketing Manager, Microsoft
Upgrading SQL Server 2005 is a great opportunity to optimize database infrastructure investment. For instance, several smaller databases can be virtualized and consolidated onto a larger server. If it’s time for a hardware refresh, evaluate the economics of keeping the system on-premises vs. moving to a cloud provider. SQL Server 2005 and newer databases have a direct upgrade path to SQL Server 2014, and can be upgraded either in-place or side-by-side. Many customers choose the side-by-side method for its potential to minimize downtime and ability to roll back if needed. It also works well with the hardware refresh and migration options outlined above. Whatever your target infrastructure, upgrading SQL Server 2005 databases is a great way to take advantage of the new features, faster performance, and higher scale available through newer versions of the product.
Ali Din, dinCloud’s CMO says, “There are a lot of enhancements from SQL Server 2005 to SQL Server 2012, but if you are going to go through an upgrade, I suggest taking advantage of the technology with a proven methodology.”
Din says organizations should:
- Make the move to the latest version on SQL Server (in this case, SQL Server 2014) in order to take advantage of the latest features and functionality available today.
- Use a SQL database expert to help with the planning, migration, and optimization – get expert resources to do it right and do it well.
- Go with the Enterprise edition to take advantage of In-Memory, Always On capabilities and expanded robust features – in the long run this saves you from having to buy multiple smaller or less feature-rich databases.
Based on Transaction Processing Performance Council’s benchmark results published for SQL Server 2014 (www.tpc.org/4069) and SQL Server 2005 (www.tpc.org/4001), SQL Server 2014 runs up to 13 times faster than SQL Server 2005. Additional benefits and updates in 2014 include mission critical performance with programmability, T-SQL enhancements, security, manageability, and high availability; faster insights on ANY data, reporting, analysis, master data and integration services; and data platform modernization with hybrid cloud solutions and an easy, complete transition from on-premises to cloud.