It’s over, folks.  Microsoft has officially ended support for SQL Server 2005. By the time you read this, April 12 will have come and gone. In earlier posts in this series, we’ve discussed some of the negatives associated with ignoring this deadline. We’ve also talked about some of the options you have for upgrading. Specific details concerning the support lifecycle can be found at Microsoft’s support website.

In this post, we’re going to present you with some of the things you could bring to your environment if you choose to move to a supported SQL platform.

Note: We are explicitly skipping SQL Server 2008 in our examples since there are very few situations where we would recommend upgrading to anything less than SQL Server 2008 R2. And certainly getting to SQL Server 2014 would be the preferred target. We are also skipping the SQL Server 2016 changes since it won’t be RTM’ed by the time we post this, but don’t worry, we’ll have more on SQL Server 2016 in a later post.

Mission Critical Performance Benefits

Harder, better, faster, stronger. The realities of today’s budgets and business challenges are certainly translating into your needing to do more with your database resources than you were being asked to do when you deployed SQL Server 2005. Microsoft has been busy pouring in new features to help increase the performance and scale of SQL server.

  • Transparent Data Encryption comes with SQL Server 2008 R2 so you can encrypt data at rest without making application changes.
  • Columnstore indexes make their appearance in SQL Server 2012 for the first time in the platforms history. This is an in memory device to speed up data warehouse queries from 10X to 100X or more. This works without having to change application code.
  • SQL Server 2014 brings a whole host of memory optimizing technology like buffer pool extensions and memory-optimized tables. In-memory now spans not only data warehouse workloads, but also OLTP.
  • Licensing changes starting in SQL Server 2008 R2 may allow more consolidation at a lower cost per guest for those who are virtualizing their SQL platform.

Improve your 9’s. Keeping your data online and available is a primary function of any good data steward. Microsoft has added a lot of great technology to the SQL database engine to help support your HA/DR needs.

  • If you can get to SQL Server 2008 R2 you’ll have access to things like backup compression and enhancements to Database Mirroring like faster write operations to the secondary and automatic page repair.
  • SQL Server 2012 brings Always On Availability Groups (AGs) which allow you to failover even faster while streamlining the management of the HA/DR process.
  • SQL Server 2014 improves AGs by allowing even more secondaries. In addition, the expansion of SQL Managed backups to Azure allows you an even greater degree of flexibility when creating a disaster recovery plan.

Faster Insights from Any Data

There has been a lot of additional industry attention paid to deriving insights from data since SQL 2005 released. We are currently riding a wave of interest in self-service and other business intelligence tools that is transforming how companies analyze data and make business decisions. According to the latest Gartner report, Microsoft has been identified as a leader in the 2016 Magic Quadrant for Business Intelligence and Analytics Platforms. And they’ve been there for the past 9 years, which is when SQL Server 2008 was released. If you’re still on SQL Server 2005, you’re definitely missing some huge capabilities in this area.

  • In SQL Server 2008 R2, Microsoft introduced PowerPivot. The Vertipaq technology provides an in memory, compressed tabular data model. PowerPivot can be hosted in SharePoint for the first time in this version allowing you to support some powerful collaboration scenarios. And how are you developing BI applications without Change Data Capture and Sparse Columns?
  • SQL Server 2012 brings the ability to support the new tabular model in Analysis Services. You can create PowerView Reports against the data in Analysis Services. This allows you to throw more server horsepower at the analysis you are performing on the data.
  • In SQL Server 2014 Microsoft ramped the capabilities of Analysis Services to handle the tabular and multidimensional data modeling. They also include support for Polybase in the Analytics Platform System (APS) appliance. This allow T-SQL developers to query relational AND non-relational data.

Data Platform Modernization

As technologies such as virtualization and the cloud have become not only a supported reality but a viable extension or strategic platform choice for your IT development and operational platform, there are many opportunities to take advantage of with SQL Server.

  • With SQL Server 2008 R2 Microsoft started to streamline SQL Server Licensing, as well as support consolidation with virtualization.
  • Backing up to and restoring from Azure Blob storage was introduced in SQL Server2012. This could allow you to get off that tape backup system you hate to maintain.
  • In SQL Server 2014 extending to the cloud becomes even easier with wizards to help you set up different disaster recovery scenarios directly into an Azure environment. For example, Always On Availability Group replicas are now fully supported running in Azure.

I know we didn’t hit every new feature that has come along since SQL Server 2005. There are literally hundreds of improvements, but we only had space for a few highlights. For a detailed list of specific SQL goodies included in currently supported versions, please see the following links to find your favorite reason to upgrade:

SQL 2008 R2 – What’s New

SQL 2012 – What’s New

SQL 2014 – What’s New

We Get IT

At CDW we can help you get past the challenges to upgrading. Our licensing experts can prepare you with information on the best ways to purchase SQL Server. We have experienced SQL Server Engineers who can help you overcome the hurdles and select the best method for you to move forward. We can also help advise you of opportunities for consolidation.

Please contact your account manager to discuss the available options to help you take advantage of the capabilities offered in a newer Microsoft SQL Server platform.

Lastly, for full coverage of SQL 2005 EOS, check out BizTech Magazine for even more information.