SQL Server 2008 R2 New Features

SQL Server 2008 R2 is Microsoft’s latest release of its enterprise relational database and business intelligence (BI) platform, and it builds on the base of functionality established by SQL Server 2008. However, in spite of the R2 moniker, Microsoft has added an extensive set of new features to SQL Server 2008 R2. Although the new support for self-service BI and PowerPivot has gotten the lion’s share of attention, SQL Server 2008 R2 includes several other important enhancements. In this article, we’ll look at the most important new features in SQL Server 2008 R2.

New Editions

Some of the biggest changes with the R2 release of SQL Server 2008 are the new editions that Microsoft has added to the SQL Server lineup. SQL Server 2008 R2 Datacenter Edition has been added to the top of the relational database product lineup and brings the SQL Server product editions in-line with the Windows Server product editions, including its Datacenter Edition. SQL Server 2008 R2 Datacenter Edition provides support for systems with up to 256 processor cores. In addition, it offers multiserver management and a new event-processing technology called StreamInsight. (I’ll cover multiserver management and StreamInsight in more detail later in this article.)

The other new edition of SQL Server 2008 R2 is the Parallel Data Warehouse Edition. The Parallel Data Warehouse Edition, formerly code-named Madison, is a different animal than the other editions of SQL Server 2008 R2. It’s designed as a Plug and Play solution for large data warehouses. It’s a combination hardware and software solution that’s available only through select OEMs such as HP, Dell, and IBM. OEMs supply and preconfigure all of the hardware, including the storage to support the data warehouse functionality. The Parallel Data Warehouse Edition uses a shared-nothing Massively Parallel Processing (MPP) architecture to support data warehouses from 10TB to hundreds of terabytes in size. As more scalability is required, additional compute and storage nodes can be added to the data warehouse.

As you would expect, the Parallel Data Warehouse Edition is integrated with SQL Server Integration Services (SSIS), SQL Server Analysis Services (SSAS), and SQL Server Reporting Services (SSRS). For more in-depth information about the SQL Server 2008 R2 Parallel Data Warehouse Edition, see "Getting Started with Parallel Data Warehouse,” June 2010.

The new SQL Server 2008 R2 lineup includes

More detailed information about the SQL Server 2008 R2 editions, their pricing, and the features that they support can be found in Table 1. SQL Server 2008 R2 supports upgrading from SQL Server 2008, SQL Server 2005, and SQL Server 2000.

Table 1: SQL Server 2008 R2 Editions

Support for Up to 256 Processor Cores

On the hardware side, SQL Server 2008 R2 Datacenter Edition now supports systems with up to 64 physical processors and 256 cores. This support enables greater scalability in the x64 line than ever before. SQL Server 2008 R2 Enterprise Edition supports up to 64 processors, and Standard Edition supports up to four processors.

It’s noteworthy that SQL Server 2008 R2 remains one of the few Microsoft server platforms that is still available in both 32-bit and 64-bit versions. I expect that it will be the last 32-bit version of SQL Server that Microsoft releases.

PowerPivot and Self-Service BI

Without a doubt, the most publicized new feature in SQL Server 2008 R2 is PowerPivot and self-service BI. SQL Server 2008 R2’s PowerPivot for Excel (formerly code-named Gemini) is essentially an Excel add-in that brings the SSAS engine into Excel. It adds powerful data analysis capabilities to Excel, the front-end data analysis tool that knowledge workers know and use on a daily basis. Built-in data compression enables PowerPivot for Excel to work with millions of rows and still deliver subsecond response time. As you would expect, PowerPivot for Excel can connect to SQL Server 2008 databases, but it can also connect to previous versions of SQL Server as well as other data sources, including Oracle and Teradata, and even SSRS reports. In addition to its data manipulation capabilities, PowerPivot for Excel also includes a new cube-oriented calculation language called Data Analysis Expressions (DAX), which extends Excel’s data analysis capabilities with the multidimensional capabilities of the MDX language. Figure 1 shows the new PowerPivot for Excel add-in being used to create a PowerPivot chart and PowerPivot table for data analysis.

PowerPivot for SharePoint enables the sharing, collaboration, and management of PowerPivot worksheets. From an IT perspective, the most important feature that PowerPivot for SharePoint offers is the ability to centrally store and manage business-critical Excel worksheets. This functionality addresses a huge hole that plagues most businesses today. Critical business information is often kept in a multitude of Excel spreadsheets, and unlike business application databases, in the vast majority of cases these spreadsheets are unmanaged and often aren’t backed up or protected in any way. If they’re accidentally deleted or corrupted, there’s a resulting business impact that IT can’t do anything about. Using SharePoint as a central storage and collaboration point facilitates sharing these important Excel spreadsheets, but perhaps more importantly, it provides a central storage location in which these critical Excel spreadsheets can be managed and backed up by IT, providing the organization with a safety net for these documents that didn’t exist before. PowerPivot for SharePoint is supported by SQL Server 2008 R2 Enterprise Edition and higher.

As you might expect, the new PowerPivot functionality and self-service BI features require the latest versions of each product: SQL Server 2008 R2, Office 2010, and SharePoint 2010. You can find out more about PowerPivot and download it from www.powerpivot.com.

Multiserver Management

Some of the most important additions to SQL Server 2008 R2 on the relational database side are the new multiserver management capabilities. Prior to SQL Server 2008 R2, the multiserver management capabilities in SQL Server were limited. Sure, you could add multiple servers to SQL Server Management Studio (SSMS), but there was no good way to perform similar tasks on multiple servers or to manage multiple servers as a group. SQL Server 2008 R2 includes a new Utility Explorer, which is part of SSMS, to meet this need. The Utility Explorer lets you create a SQL Server Utility Control Point where you can enlist multiple SQL Server instances to be managed, as shown in Figure 2. The Utility Explorer can manage as many as 25 SQL Server instances.

The Utility Explorer displays consolidated performance, capacity, and asset information for all the registered servers. However, only SQL Server 2008 R2 instances can be managed with the initial release; support for earlier SQL Server versions is expected to be added with the first service pack. Note that multiserver management is available only in SQL Server 2008 R2 Enterprise Edition and Datacenter Edition. You can find out more about multiserver management at www.microsoft.com/sqlserver/2008/en/us/R2-multi-server.aspx.

Master Data Services

Master Data Services might be the most underrated feature in SQL Server 2008 R2. It provides a platform that lets you create a master definition for all the disparate data sources in your organization. Almost all large businesses have a variety of databases that are used by different applications and business units. These databases have different schema and different data meanings for what’s often the same data. This creates a problem because there isn’t one version of the truth throughout the enterprise, and businesses almost always want to bring disparate data together for centralized reporting, data analysis, and data mining.

Master Data Services gives you the ability to create a master data definition for the enterprise to map and convert data from all the different date sources into that central data repository. You can use Master Data Services to act as a corporate data hub, where it can serve as the authoritative source for enterprise data. Master Data Services can be managed using a web client, and it provides workflows that can notify assigned data owners of any data rule violations. Master Data Services is available in SQL Server 2008 R2’s Enterprise Edition and Datacenter Edition. Find out more about Master Data Services at www.microsoft.com/sqlserver/2008/en/us/mds.aspx.

StreamInsight

StreamInsight is a near real-time event monitoring and processing framework. It’s designed to process thousands of events per second, selecting and writing out pertinent data to a SQL Server database. This type of high-volume event processing is designed to process manufacturing data, medical data, stock exchange data, or other process-control types of data streams where your organization wants to capture parts of the real-time data for data mining or reporting.

StreamInsight is a programming framework and doesn’t have a graphical interface. It’s available only in SQL Server 2008 R2 Datacenter Edition. You can read more about SQL Server 2008 R2’s StreamInsight technology at www.microsoft.com/sqlserver/2008/en/us/R2-complex-event.aspx.

Report Builder 3.0

Not all businesses are diving into the analytical side of BI, but almost everyone has jumped onto the SSRS train. With SQL Server 2008 R2, Microsoft has released a new update to the Report Builder portion of SSRS. Report Builder 3.0 (shown in Figure 3) offers several improvements.

Like Report Builder 2.0, it sports the Office Ribbon interface. You can integrate geospatial data into your reports using the new Map Wizard, and Report Builder 3.0 includes support for adding spikelines and data bars to your reports so that queries can be reused in multiple reports. In addition, you can create Shared Datasets and Report Parts that are reusable report items stored on the server. You can then incorporate these Shared Datasets and Report Parts in the other reports that you create.

Other Important Enhancements

Although SQL Server 2008 R2 had a short two-year development cycle, it includes too many new features to list in a single article. The following are some other notable enhancements included in SQL Server 2008 R2:

You can learn more about the new features in SQL Server 2008 R2 at msdn.microsoft.com/en-us/library/bb500435(SQL.105).aspx.

To R2 or Not to R2?

SQL Server 2008 R2 includes a tremendous amount of new functionality for an R2 release. Although the bulk of the new features, such as PowerPivot and the Parallel Data Warehouse, are BI oriented, there are also several significant new relational database enhancements, including multiserver management and Master Data Services. However, it remains to be seen how quickly businesses will adopt SQL Server 2008 R2. All current Software Assurance (SA) customers are eligible for the new release at no additional cost, but other customers will need to evaluate if the new features make the upgrade price worthwhile. Perhaps more important than price are the resource demands needed to roll out new releases of core infrastructure servers such as SQL Server.

That said, PowerPivot and self-service BI are potentially game changers, especially for organizations that have existing BI infrastructures. The value these features bring to organizations heavily invested in BI makes SQL Server 2008 R2 a must-have upgrade.

Table 1: SQL Server 2008 R2 Editions