Insufficient data from Andrew Fryer

The place where I page to when my brain is full up of stuff about the Microsoft platform

SQL Server 2008 R2 Overview

I have a fair few posts on SQL Server 2008 R2 on my blog, but I have got so into individual posts I thought a short summary of the new release might be useful for the casual reader/ IT manager, etc.

SQL Server 2008 R2 has now been released to manufacture, but you may be wondering what is different about the new version and why it is branded R2 and not SQL Server 2010. All of the new capabilities in this version are new add-ons or changes to add-ons rather than changes to the database engine with a couple of small extensions. These extra capabilities are mainly in the business intelligence area and include:

  • PowerPivot – an in memory analysis engine that is an add-in to Excel 2010 and SharePoint 2010, to provide self-service analysis for business users from multiple sources.
  • Reporting Services has been extended to include report parts and new visualisations such as maps and sparklines. There is also a corresponding new version of Report Builder for information workers to work with these new features.
  • Master Data Services – is a master data management tool to allow control of reference data (customer, products etc.) in larger enterprises where this information is held and updated in multiple systems.
  • StreamInsight – complex event processing in memory to allow data streams of many thousands of events per second to be analysed and acted upon in near real time.

There are also better tools for managing applications and multiple servers in this release.

The subtle changes to the database engine are to do with compression. Compression can really improve database performance in certain scenarios like virtualisation where a small hit on CPU at the expense of reduced IO to get data onto and off disk is more than acceptable. The changes themselves are:

  • Enterprise edition now supports compression of Unicode data, which is particularly beneficial on systems like SAP which make extensive use of this data type.
  • Backup compression is now in Standard edition and this not only reduces the size of backups but reduces the time to make them and more importantly restore them.

There are also changes to the editions of SQL Server 2008 R2 including a new Data Center edition. This is actually simplifies licensing because the all the higher editions of SQL Server correspond exactly to the Windows Server editions i.e. Standard, Enterprise and the new Data Center editions of SQL Server 2008 R2 have the same memory, CPU and virtualisation limits as the corresponding edition of Windows Server 2008 R2. Full details of these changes are here.