Review: Microsoft SQL Server 2008 R2

SQL Server 2008 R2, which ships this month, allows end users to tap into the powerful business intelligence features of SQL Server via tight integration with popular Microsoft applications like Excel.

The downside, of course, is that to reap the full functionality of SQL Server 2008 R2 you also need to be running all the latest versions of Microsoft's product lines – Windows 7, Windows Server 2008 R2, Office 2010, and SharePoint 2010.

Compared with its predecessor, SQL Server 2008, the new version is more powerful, with support for up to 256 logical CPUs. And a new Unicode compression scheme, USC-2, can save up to 50% of storage requirements with Unicode data.

SQL Server 2008 R2 boasts a number of new services, including PowerPivot for Excel and SharePoint, Master Data Services, StreamInsight, ReportBuilder 3.0, Reporting Services Add-in for SharePoint, a Data-tier function in Visual Studio that enables packaging of tiered databases as part of an application, and a SQL Server Utility that manages multiple SQL Servers.-- PowerPivot allows end users to import, manipulate, analyze and export data, as well as create reports, all using the familiar Excel interface and language.-- Master Data Services is a new and powerful feature designed to help an organization create a structure and hierarchy for data, so that data collected and used across multiple departments, silos and databases is standardized and aligned. This ensures that different databases are interoperable.-- StreamInsight is a new platform integrated with .Net and SQL Server for processing streams of data in parallel, allowing for many simultaneous queries running on live data.-- The data-tier functionality in Visual Studio is designed to make it simple to create n-tier applications that use databases on the back end, and package the entire application including the database as a single unit.-- The SQL Server Utility allows for a single management dashboard across multiple servers and databases.

Installation made easy

Installing SQL Server 2008 R2 is much easier than previous versions. The installer takes care of all prerequisites, configuring and downloading a whole bunch of stuff from Microsoft. Once the prerequisite installer runs, deploying SharePoint 2010 is straightforward, a matter of running the installer, entering a key, choosing the default install and applying updates. Finally, you run the installer for SQL Server 2008 R2. It runs through a lengthy but well-integrated process. The only thing that can't be accomplished from within the installer is creating the service accounts.

A reboot completes the overall process. Afterwards, there are a large number of new applications available, including Microsoft SharePoint 2010, Microsoft SQL Server 2008 R2, the SQL Server Business Intelligence Development Studio, the Analysis Services Deployment Wizard, the Master Data Services Configuration Manager, the Report Builder 3.0 and Microsoft Visual Studio 2008.

The overall suite of tools is easy to use, and especially stresses the ability of the end user to apply basic tools they're already familiar with to create business intelligence applications.

Business intelligence

Business intelligence is a major problem for many managers – there's lots of data available, but finding the significant bits and manipulating that data can be difficult. PowerPivot and Excel 2010 allow managers to use a familiar tool to get BI from even very large sets of data.

During my testing, PowerPivot imported 4 million rows from the Microsoft test database. Microsoft says that with enough memory, 100 million rows can be imported. One can use Excel to import data, format the data, and put it into databases. Files can also be exported to SharePoint and shared. Data in SharePoint is connected to the back-end database – any updates to the data are reflected in the SharePoint site.

Data Analysis Expressions (DAX) is a new language available through PowerPivot that lets you manipulate data using expressions similar to standard Excel formulas. Using Excel with PowerPivot to manipulate data allows managers familiar with Excel to collect data, manipulate it and create presentations and reports completely within Excel, while retaining the data tables in SQL Server if desired.

SQL Server is also well integrated with Visual Studio 2008, and includes the SQL Server Integration Services Designer that integrates into Business Intelligence Development Studio (BIDS).

BIDS is a Visual studio shell for developing BI solutions using SQL Server Integration Services (SSIS), SQL Server Analysis Services (SSAS) and SQL Server Reporting Services (SSRS). The result is a fluid solution that allows end users to create databases, find data, process it, analyze it and create reports, all using a very powerful set of tools that don't require a set approach – the user can start with the database, or with the analysis tools, or create a workflow, and connect the parts together on the fly.

The Master Data Services application is accessible through a Web application, providing a simple, standardized interface to create a model for data, making it easy to define data types, groups and a tree structure to contain and organize the data.Once the data structures are defined, hierarchies created and relationships defined, you can set up security between various levels, import data into the model, browse the data, add or refine relationships, then export the data and review the results. The overall process is similar to developing applications in Visual Studio, but oriented specifically towards creating standard data structures.

StreamInsight is a separate application that is installed in addition to the SQL Server, and also requires users be added to the right group to enable the service. StreamInsight is an extension to Visual Studio intended to handle real-time data streams. It can import large volumes of data, handle historical as well as real-time data, and analyze as well as record the data. While the initial thrust of the application is oriented towards recording events such as mouse clicks or other types of user inputs, any sort of analog or digital data can be used. The Visual Studio tools are used to create data structures for real-time events, while the StreamInsight Event Flow Debugger provides tools to trace events.

The data-tier functionality in Visual Studio allows a developer to extract, modify and deploy databases as part of applications. Rather than the process previously required with a separate install of a database application, scripts to create and populate the database, and then more scripts to connect the database to the application, the entire development and deployment process is greatly simplified.

For the administrator, the SQL Server Utility makes administration of multiple databases across multiple servers much easier. The components include a Utility Control Point that collects configuration and performance information from multiple SQL Servers, a Utility Explorer that provides a tree view of the servers, and a dashboard with summary information on the servers.Microsoft SQL Server 2008 R2 provides a host of new features that will be particularly welcomed by users who need business intelligence functionality. The support for up to 256 CPUs and new administrative tools will be helpful to administrators. On the downside, the new functionality may require substantial upgrades in organizations that haven't yet moved to Windows 7, Office 2010 and SharePoint 2010.

Harbaugh is a freelance reviewer and IT consultant in Redding, Calif. He can be reached at logan@lharba.com.