SQL Server 2008 R2 Offers Enhancements, New Management Capabilities

One of the most compelling new features in both Excel 2010 and in SQL Server 2008 R2 is PowerPivot, a new tool that enables spreadsheet-savvy workers to take on larger amounts of data from within a spreadsheet-style interface.

With the late April release of its SQL Server 2008 R2, Microsoft has extended its popular database server product with core platform enhancements, new management capabilities and a raft of features focused on enabling self-service business intelligence scenarios.

On the self-service BI front, SQL Server 2008 R2 relies heavily on integration with Office 2010 and SharePoint Server 2010, which eWEEK Labs reviewed in our June 7 and May 17 issues, respectively. As a result, the new release should pay the biggest dividends to organizations that have deployed all three products.
On its own, the new SQL Server release still packs some worthwhile improvements, particularly at sites running the database on large machines that can take advantage of the new version's support for 256 logical processors. Organizations running SQL Server on Microsoft's Hyper-V virtalization platform should appreciate the product's new support for live migration between Hyper-V hosts.
I was also impressed by the product's new facilities for managing groups of SQL Server instances from a central point, although I'd like to see the feature expand to cover a broader range of SQL Server versions.
SQL Server 2008 R2 is available on a per-processor or per-server and CAL (client access license) basis, and a CAL can be either a user or a device. All told, Microsoft offers eight versions of the database, ranging from the free Express and Compact to the Enterprise Edition, which costs $27,495 per processor. For the full editions and pricing breakdown, click here.

PowerPivot for Excel and Sharepoint

One of the most compelling new features in both Excel 2010 and in SQL Server 2008 R2 is PowerPivot, a new tool that enables spreadsheet-savvy workers to take on larger amounts of data from within a spreadsheet-style interface. In a previous review of PowerPivot, I talked about the basics of using this new tool. I've since tested PowerPivot's SharePoint integration options for sharing the workbooks, along with the options that administrators have for managing those worker-initiated PowerPivot resources that come to be viewed as mission critical for their organizations.

As with other Office documents, I could save a PowerPivot workbook to a SharePoint server through the regular Excel file dialog. Once the file had made its way onto my test server, I could view the PowerPivot worksheet in a gallery on the server. The gallery module taps Silverlight to display the gallery in a handful of different rich layouts, such as one reminiscent of Apple's CoverFlow views of record albums. I wasn't able to locate any plain old PowerPivot worksheet view, however, which meant that my test workbooks weren't viewable from the Firefox on Linux combination that I tried out.

As with my tests of SharePoint's Silverlight enhancements to the Office Web Apps, the Linux-friendly Moonlight plug-in from Novell didn't work for me. Version 2.2 of the plug-in left me with a blank window where the gallery should have stood, and the preview of version 3.0 crashed my copy of Firefox. I was able, however, to make it past the broken-on-Linux gallery by copying the link straight to the PowerPivot workbook from Internet Explorer on Windows into Firefox on Linux, where my test sheet, complete with all its PivotChart and Data Slicer functionality, rendered quite well.

By combining PowerPivot with SharePoint in this way, workers can share the analysis tools they prepare with others in their organization, including those without Office installed on their machines. What's more, in the case of the flawed but workable Linux and Firefox combination I tested, members of an organization can share PowerPivot workbooks with co-workers whose machines couldn't run Office at all.

Beyond these information-sharing gains, one of the benefits of pulling self-service data analysis tasks out of discrete spreadsheets and into PowerPivot and SharePoint is the way that the duo can keep information up-to-date. Back at the PowerPivot gallery view, I was able to configure a data refresh schedule for my workbook, and, once I'd set the schedule, consult a refresh history to confirm that the worksheet had been pulling data down from my SQL Server data source as expected.
The creation, sharing and refresh configuration of PowerPivot workbooks can all be handled on a self-service basis, but if an organization's IT administrators wish to step in to manage these resources, for instance, to adjust the scheduled refresh time to off hours, they can do so through a PowerPivot dashboard hosted from SharePoint.
From this dashboard, I could monitor the performance and use of the PowerPivot workbook hosted on SharePoint and of their associated queries. The dashboard sports a handful of charts for laying out this information, including a handy Workbook Activity chart that displays the users and number of queries for each hosted workbook, with a time slider beneath the chart for tracking the popularity and load associated with these resources. If a significant number of workers within an organization began using a PowerPivot workbook created by one of their colleagues without the assistance of the IT department, this chart could help signal to IT the growing importance of the workbook, enabling the administrators to bring it under management.

ReportBuilder 3.0

The ReportBuilder 3.0 application that ships with SQL Server 2008 R2 bears a striking resemblance to the components of Microsoft's recently released Office 2010 suite, with a now increasingly familiar "Ribbon" interface, and several of the same new data visualization goodies that grace Excel 2010. For instance, I was able to outfit a test report with sparklines: single-cell charts with a knack for presenting data in a small space. I was also able to display data in my test reports as data bars and indicator graphics. In the case of the indicator graphics, I could select from several sets of images and assign numeric- or percentage-based values to highlight the significance of the data. For instance, I chose a trio of red, yellow and green badges to indicate acceptable, warning and alert states in my report data.

SQL Server Utility

Among the new features included in SQL Server 2008 R2's Management Studio application is the SQL Server Utility, which enables administrators to manage and monitor multiple database applications and server instances from a central interface called a Utility Control Point.

I used the SQL Server Utility to group a pair of Server 2008 R2 instances together and track utilization and policy information for the instances from the Management Studio's Utility Explorer interface. I wasn't able to add a SQL Azure instance to my control point, and Microsoft confirmed that the product does not yet support managing SQL Azure instances alongside regular SQL Server databases. I found a similar lack of support for managing SQL Server 2005 instances. Ideally, the UCP feature would support both older SQL Server versions and the newer, cloud-based SQL Azure instances, but I hope at least to see SQL Azure support in future versions.

For CPU and storage utilization measures, I could consult usage charts with day, week, month and year time spans radio buttons for monitoring performance of each instance. I could also consult a dashboard view of the instances enrolled in my UCP, with over- and under-utilization data based on the quotas I'd set in global and individual instance policies for the control point.

I was able also to break out performance characteristics for particular applications hosted from my test instances and organized into Data-tier applications. Data-tier applications in SQL Server offer administrators and developers a way to stay on the same page with regard to their applications' deployment details. A developer can create a database application in Visual Studio and package it up into a DAC package for deployment on a production system. I added a sample Data-tier application to my UCP, where I was able to monitor and set policies around it as with the database instances in the control point.