In-Depth

SQL Server 2008 R2 for the Developer

Microsoft extends its enterprise-capable database platform with powerful new management and scalability features -- and there's plenty in the latest rev for developers.

• By Don Kiely
• 09/01/2010

In April, Microsoft released a new version of its flagship database server, with the unwieldy name SQL Server 2008 R2. Version R2 leaves the core database engine of SQL Server 2008 almost completely untouched, but adds a slew of new features that can dramatically expand the role of SQL Server in managing an enterprise's data.

Microsoft only occasionally uses the R2 moniker for its applications, usually for point releases that add a few new features. By contrast, major new versions deliver a host of new features and fixes, while service packs mostly fix bugs. The nice thing about SQL Server 2008 R2 is that it leaves intact what is now a very stable, 2-year-old database engine. Enterprises should be comfortable installing SQL Server 2008 R2 and benefiting from its new features without worrying about breaking changes to the core engine. With SQL Server 2008 R2, Microsoft is filling out the feature set of a mature database product.

There are two new top-end SQL Server editions with the R2 release: Datacenter for the biggest and most heavily used enterprise databases, and Parallel Data Warehouse with massively parallel processing to support intensively used data warehouses. A few features from the original release of SQL Server 2008 are migrating to less-expensive editions. For example, backup compression, originally available only with the Enterprise Edition, is now part of the Standard Edition. And the largest database supported by SQL Server Express is going up from 4GB to 10GB, a welcome change to allow more flexibility for desktop applications.

When reading the Microsoft marketing materials you might get the impression that SQL Server 2008 R2 is targeted only at DBAs and enterprise system architects, cementing the company's increasingly enterprise-centric focus. But dig more deeply and you'll find plenty of goodies for developers. In this article, I'll briefly cover some of the more interesting new features that you can use in application development, as well as a few features that you may encounter that will affect how you write applications.

Deep security is one of the primary reasons to use a database server product like SQL Server. But the only significant new security feature in SQL Server 2008 R2 is Extended Protection for authentication, used to prevent authentication relay attacks. This is an attack in which a malicious server uses the credentials of an authenticated user contained in client software to masquerade as the user, thereby giving the attacker access to whatever data the user can access.

Extended Protection uses a combination of service and channel bindings to validate that the user is not an attacker and establish a secure channel to verify the authenticity of the client. Extended Protection requires versions of Windows with the feature installed and activated (it's turned off by default).

Application and Multi-Server Management
Some new developer features are tightly coupled to server instance management, as is the case with application and multi-server management. The new SQL Server Utility lets a systems administrator create a utility control point on a single instance of SQL Server 2008 R2, which acts as a central repository for configuration and performance data that it collects for all instances of SQL Server enrolled in the utility.

The utility control point provides a DBA with far more control and monitoring features than have been previously available to apply policies and analyze resource utilization trends, which help predict and prevent failures due to resource depletion. A companion Utility Explorer displays a dashboard and detailed viewpoints to monitor enrolled instances of SQL Server.

As part of these multi-server management features, either a developer or a DBA can define a data-tier application (DAC) that simplifies developing, deploying and managing database objects that support data applications. (Yes, the acronym is DAC and not DTA, which already means Database Engine Tuning Advisor.) A DAC defines all of the database objects -- including schemas -- needed to support an application. By extracting the database objects and creating a single unit of deployment in a single file type, you can migrate them to the centrally managed world of multi-server management as well as apply upgrades to the various objects.

Figure 1 shows the wizard from Management Studio that DBAs use to extract a DAC from an existing set of database objects and generate a DAC package file. You can create a DAC from scratch in Visual Studio 2010 with the SQL Server Data-Tier Application project template. The project Visual Studio creates from this template looks similar to a Database Edition project (the SQL Server 2008 Database Project template), but the end result of building a DAC project is a DACPAC file you can deploy to a SQL Server instance.

[Click on image for larger view.]

Figure 1. The SQL Server 2008 R2 Extract Data-Tier Application wizard lets you extract an existing database as a data-tier application (DAC). You can also register a database as a DAC with the accompanying Register Data-Tier Application wizard.

The SQL Server Data-Tier Application project template hooks into the Code Analysis feature in Visual Studio, shown in Figure 2. Code Analysis helps you improve the quality of your database code every time you build the project. The Design, Naming and Performance categories can help ferret out some of the most basic and common problems that can hurt performance and complicate code maintenance over the long term. As with all code analysis in Visual Studio, you can select the tests you want and choose to correct or ignore any problems they find.

[Click on image for larger view.]

Figure 2. The Code Analysis tab in a DAC project properties window lets you analyze your database code. The checks are straightforward, but can help improve your code and make it more consistent for better maintainability.

It remains to be seen whether this first version of DAC is robust enough to handle more than applying small changes to departmental databases. For now, DACs are best suited for deploying and updating Microsoft SQL Azure databases.

BI for the Power User
Probably the most dramatic and visually striking new feature in SQL Server 2008 R2 is PowerPivot for Excel and for SharePoint. PowerPivot provides self-service business intelligence (BI) so end users can create and manipulate data, letting them slice, dice, analyze, and share data and reports. PowerPivot for Excel is a separate free download for Excel 2007 and 2010 (although you need Excel 2010 to make use of all the PowerPivot features), and PowerPivot for SharePoint adds shared services for working with Excel PowerPivot workbooks that you can publish to SharePoint.

Microsoft calls the set of PowerPivot features "managed self-service business intelligence," and that's an apt description. Enterprise BI is beyond the understanding of mere mortals, yet it's mere mortals who need the information and analyses that BI generates to make business decisions. For power users, PowerPivot provides tools in the familiar Excel environment. It also gives systems administrators the tools they need to provide data in a form that users can utilize, as well as to facilitate sharing the resulting data and analyses. Most importantly, users can make their own analyses without waiting for IT to generate reports.

The Excel add-in provides several components on the user's local machine, including a PowerPivot menu in the Excel ribbon to launch the design window and work with data, and a PowerPivot window that provides all the design tools the user needs. There's also a local Analysis Services engine that compresses and loads data, and other tools for accessing various sources of data. Figure 3 shows most of the visual components at work with the design window in the foreground, after loading data from the FactInternetSales and related tables from the AdventureWorks2008R2 sample database.

[Click on image for larger view.]

Figure 3. Excel power users will be immediately comfortable with using the PowerPivot add-in for Excel. The PowerPivot window shown here lets users access data sources and manipulate data to their hearts' content.

Over the years, Microsoft and other vendors have made various attempts to make BI data available to power users. PowerPivot seems to be the first attempt to deliver sophisticated tools that can fully leverage the information locked in data warehouses. The tool is certainly not targeted at the casual user, but neither does it require a degree in computer science to extract valuable data and information.

I've just scratched the surface of what is new, shiny and exciting in SQL Server 2008 R2. There's a lot more there for DBAs, including features like Master Data Services for streamlining enterprise data into a single version of the truth, and StreamInsight for event processing to handle massive volumes of complex data in real time. Database compression supports Unicode now, and Microsoft has added features to support high-volume data warehouses. Developers, of course, will find plenty to help their applications make better use of better data.