SQL Server 2008 R2: New Manageability Features

Here's a quick look at the improvements to the R2 release that I think will be useful come the May release date.

• By Joshua Jones
• 01/25/2010

Microsoft is nearing completion of the next version of SQL Server. The R2 release features quite a few upgrades to existing functionality, as well as a few new features. Through the next several weeks, I'll talk about the new features, particularly those that apply to database administrators, using the November CTP. First up, we'll look at the new manageability features.

SQL Azure: Many of you may have heard about Microsoft's cloud offerings. An essential piece of the Azure platform is the SQL Azure service, which provides a cloud-based data platform for businesses to use. While developing for Azure is slightly outside our topic here, connecting to it isn't. SQL Server 2008 R2 includes connectivity to SQL Azure from the SQL Server Management Studio. This allows developers/DBAs to develop and deploy scripts to SQL Azure, as well as limited exploration capabilities.

SQL Server Utility: In the August CTP of SQL Server 2008 R2, Microsoft introduced the SQL Server Utility. This utility expands upon the Central Management Server introduced in SQL Server 2008. It essentially provides a central repository where administrators can store all configuration and performance data for their servers that are enrolled in the SQL Server Utility. You pick a single instance to be “promoted” to be the Utility Control Point (UCP). From here you can apply policies, analyze performance data and perform trending. It provides a nice collection of dashboards and detailed reports that can help you determine server utilization and health, which can help with hardware forecasting and consolidation efforts. In the November CTP, Microsoft included sample scripts for creating the UCP, as well as enrolling and removing instances.

Data-tier Applications: New to R2 is the Data-tier Application. A Data-tier Application (or DAC, not to be confused with the Dedicated Administrator Connection) is a collection of schemas and other database-related objects that support a specific application. Administrators can use the DAC packages built by developers to deploy all changes to objects that apply to a specific application. They can also use DAC's in combination with the SQL Server Utility to monitor the performance of specific applications. While DACs are primarily geared towards developers, the impact on the administrator is that it will be easier to discern how database objects are being used, and will make change control much easier.

SQL Server Audit: This new feature allows administrators to build and customize audits of Database Engine events. Using extended events, information about transactions can be recorded and later analyzed. The audits can be scoped to the server or database level, and multiple audits can exist in an instance of SQL Server. You can specify that audits be written to XML files in the file system, or to the Windows Event log.

Next time, we'll look at how some of the existing manageability features have been extended to make the DBA's life easier.