Microsoft SQL Server 2008: What's New with R2

Author: Brian D. Egler

Abstract

This white paper explores some of the new, major features included in Microsoft SQL Server 2008 R2. These features that were first announced at the SQL PASS Summit Conference in Seattle in November 2008, and are scheduled for release in the first half of 2010. These features are categorized under three main projects: Project Gemini, Project Madison, and Project Kilimanjaro, and this white paper discusses each of them.

Introduction

Are you ready for Microsoft SQL Server 2008 R2? Microsoft has followed its "R2" strategy, which was used successfully on Windows Server, and now applied it to SQL Server as a functional release to the existing 2008 version. Many of us are still coming up to speed with the great new features of SQL Server 2008 so we are thankful that this will be an incremental release and will be a free upgrade for existing 2008 licenses. Maybe some of the features in R2 will actually persuade more customers to upgrade to 2008. Microsoft hopes so. This white paper highlights some of the major features included in SQL Server 2008 R2 due to be released in the first half of 2010.

Global Knowledge is the worldwide leader in IT and business training, delivering hands-on education via training facilities, private facilities and the Internet, enabling our customers to choose when, where and how they want to receive training programs and learning services. Keep an eye on the Global Knowledge web site for details at http://www.globalknowledge.com.

Your Data - Any Place, Any Time

The Microsoft Data Platform Vision is summed up by the phrase: "Your Data Any Place, Any Time." Within this vision, four underlying themes categorize the Microsoft SQL Server 2008 release:

• Enterprise Data Platform
• Beyond Relational
• Dynamic Development
• Pervasive Insight

In this white paper, we will be exploring a few of the new features of SQL Server 2008 R2 that were first announced at the SQL PASS Summit Conference in Seattle back in November 2008. The major new features were categorized under three main projects: Project Gemini, Project Madison and Project Kilimanjaro.

Other features, too numerous to describe here, are documented on the Microsoft web site at http://www. microsoft.com/sql . You can also download a pre-release version of the software or CTP (Community Technology Preview) from the same location. Project Gemini: Self-Service Business Intelligence

Microsoft's Business Intelligence tool-of-choice on the client-side is Office Excel. This makes sense since the typical Business Analysts live in Excel, so why have them learn a new tool? Using the OLE DB Provider for Analysis Services, the end-user can connect to a Cube directly and build a Pivot Table or Chart using standard Excel functionality. The importance of Excel 2010 in conjunction with SQL Server 2008 R2 lies in the PowerPivot feature that incorporates extensive column compression technology named VertiPaq. It was not too long ago that Excel had a meager 100,000 row limit. Now, Excel 2010 will support hundreds of millions of rows with sub-second sorting capability on the client. In addition to offer this functionality, Analysis Services 2008 R2 introduces a new Cube Viewer and tighter integration with SharePoint 2010 and Excel Services. Project Madison: DATAllegro technology.

Project Madison is the realization of the acquired DATAllegro technology for scale-out technology across multiple servers for VLDB (Very Large Database) support. The new SQL Server 2008 R2 Parallel Data Warehouse product provides support for databases from tens to hundreds of Terabytes using appliances that combine custom hardware and software to provide a fully scalable "shared nothing" technology. The product uses MPP (Massively Parallel Processing) and multiple physical nodes, each with its own instance of SQL Server running with its own CPU, memory, and storage. This means that the appliance can be scaled by simply adding capacity using a patented parallel design called "Ultra Shared Nothing" architecture.

Project Kilimanjaro: Application and Multi-Server Management

In the original release of SQL Server 2008, Microsoft introduced the Data Collector feature which allowed the capture of vital performance metrics into a "Management Data Warehouse" for further analysis using predefined reports. In R2, this concept has been extended to provide a Multi-Server Management capability that allows monitoring of many SQL Servers and Data applications from a central reasoning point called a Utility Control Point (UCP). The repository for this feature is now called a Utility Management Data Warehouse and the servers being monitored are referred to as Managed Instances. Microsoft also introduces the concept of Datatier Application Components (DAC) through Visual Studio 2010 so that application databases can be monitored also.

The new SQL Server Utility (SSU) in Management Studio gives a unified dashboard view of your managed instances so that you can monitor multiple SQL Servers' health from the central UCP server.

There is a chart for Managed Instance Health that shows how many instances are over- or under- utilized. You can also drill down to view servers in either category. The Data-tier Application chart effectively allows monitoring at the database level, too. There are also graphs based on Storage Utilization. You can configure exactly what over- or under-utilized means by setting Global Policies using the Utility Administration page. For instance, the default threshold for over-utilization is 70% evaluated 4 times per hour.

The first step to setting up multi-server management in R2 is to create the UCP using the Create Utility Control Point Wizard. The UCP is the “central reasoning point” of the SSU. The UCP hosts the Utility Management Data Warehouse (UMDW), which is the database where the performance information of multiple servers will be uploaded for centralized monitoring. By choosing the View menu option Utility Explorer, then clicking the Create UCP button, the appropriate wizard is launched. Technically, the default name of the new database created by the wizard is sysutility_mdw.

The next step is to Enroll instances of SQL Server into the SQL Server Utility. Once an instance is enrolled, it becomes a “managed instance,” and you can monitor all the managed instances using the SSU graphic displays. Currently, all managed instances need to be at the SQL 2008 R2 level in order to host the Utility Collection Set, which is uploaded to the central UMDW database.

The 2008 feature of a Data Collection Set is now referred to as a non-utility Collection Set in R2 and can operate on a managed instance, but the feature has to be disabled before enrolling the instance on the UCP. After enrolling, you can then re-enable the non-utility collection set and that performance data will also upload to the central UMDW database as well as the local MDW database. The features can co-exist, which is important.

After setup, the performance data is uploaded from the managed instances every 15 minutes and then the SSU can be used to graphically monitor the overall health of those SQL Servers using the Utility Explorer.

The recommended approach is to set up the UCP and UMDW database on a dedicated instance of SQL Server so that multi-server management can be performed remotely and without an adverse effect on existing database servers. Assuming a large number of SQL Servers, this feature will allow a quick centralized dashboard view of the health of what Microsoft likes to call your “database fabric.”

Conclusion

Microsoft SQL Server 2008 R2 has many great new features that will allow you to develop higher-performing, more scalable next-generation applications using more than just relational data. The fact that the features are largely incremental in nature should reassure users that Microsoft is building on the established foundation of SQL Server 2008. Using the same architecture and management tools, customers will be able to smoothly upgrade their systems and skills based on the need for the new features and according to their own schedule.