Introduction
More than ten years ago, on November 5, 2005, Microsoft released SQL Server 2005 to manufacturing. Over that span of time, it has become one of the most venerable -- and solid -- releases in the product line. All good things must come to an end though and the same is true of SQL 2005. It has been in extended support since April of 2011 but even that will end on April 12, 2016. see Extended support for SQL Server 2005 ends on Aptil 12, 2016 for more information.
This article highlights some of the innovations it brought us but more importantly, shows the developments since November 2005 and SQL.Next: SQL Server 2016.
Highlights of SQL Server 2005
For nostalgia's sake, you might want to review the details at What's New in SQL Server 2005, but here are a few notable enhancements brought to us in that release:
- Service Broker
- CLR integration
- The XML data type
- Ranking functions
- Common Table Expressions
- Pivot and Unpivot
- Database schemas
- Partitioning
- New architecture for Integration Services and dtsx packages
- Reporting Services as a new feature
- Multiple enhancements for Analysis Services
- Replication enhancements including transactional, peer to peer and merge replication and support for heterogeneous environments
Since 2005 ...
SQL Server releases 2008, 2008 R2, 2012 and 2014 greatly extended the power of Microsoft's enterprise-grade RDBMS and SQL Server 2016, due out in just a few months, promises to do the same. It's worth reviewing all the "What's new" pages which you can find using these links:
Just to name a few of the multitude of enhancements since 2005:
- Slipstream installations (2008)
- Filtered indexes (2008)
- PowerPivot (2008 R2)
- Master Data Services (2008 R2)
- Data Tier Applications (2008 R2)
- File tables (2012)
- Semantic search and enhanced full-text search (2012)
- Sequence objects (2012)
- OVER clause and analytic functions (2012)
- Columnstore indexes (2012)
- Always On (2012)
- Data Quality Services (2012)
- Memory optimized tables and compiled stored procedures (2014)
- Data files in Azure (2014)
- Clustered columnstore indexes (2014)
- Stretch database (2016)
- R integration (2016)
- JSON support (2016)
- Query store (2016)
- Temporal tables (2016)
Of course this is just a list of highlights that I happen to like! Please follow the links to get the full history of improvements.
I need to upgrade! But...
Upgrading from SQL Server 2005 to SQL Server 2014 is not an exercise for the faint of heart. To begin with, you cannot upgrade directly from RTM 2005 to 2014. You must have installed Service Pack 4 of SQL Server 2005 before you begin. Also, if you want to upgrade to version 2016, you will be facing a two-part upgrade, since 2016 no longer supports any version of 2005 as a base for upgrading.
In addition, before you plan your SQL Server migration, you may need to plan a Windows Server upgrade. Many SQL Server 2005 installations are still running on Windows Server 2003, but the minimum version of Windows Server supported for SQL Server 2014 is Windows Server 2008 R2 SP1, For SQL Server 2016 the minimum is Windows Server 2012 R2. Note that Windows Server 2003 extended support ended on July 14, 2015, so if you are still running that version and something breaks, you're on your own unless you opt for pay-as-you-go support.
For installations using mirroring or replication, special care must be taken to ensure minimum downtime and avoiding running "exposed" (that is, with only one active and no passive instances). A good discussion of this topic can be found here: Minimize Downtime for Mirrored Databases When Upgrading Server Instances from a 2016 perspective, but the same principles apply for other versions.
Should I stay or should I go now?
When you begin to consider upgrading a SQL Server 2005 instance to SQL Server 2014/16 -- especially when you may also need to upgrade Windows Server 2003 as well -- you may wonder if it is worth it. After all, it will cost you time and resources, not to mention possible changes in licensing costs. Considering the approaching End of Extended support for the product (and the fact that all support for Windows Server 2003 has ended), it's probably a good idea to plan for the upgrade, build a realistic project plan with appropriate budgetary and resource support, get your team trained for the upgrade(s) and the new world of SQL Server 2014/16 and set specific timelines.
On the other hand, if your SQL Server 2005 instance is not supporting business critical processes and you can afford to lose it entirely, you may want to stand pat. But then, why are you still running that system at all?
