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.

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.

About the Author

Joshua Jones is co-author of A Developer's Guide to Data Modeling for SQL Server: Covering SQL Server 2005 and 2008 (Addison-Wesley Professional, 2008) and is a principal with Denver-based Consortio Services LLC.


  • Microsoft and SAP Enhance Partnership with Teams Integration

    Microsoft and SAP this week described continuing partnership efforts on Microsoft Azure, while also planning a Microsoft Teams integration with SAP's enterprise resource planning product and other solutions.

  • Blue Squares Graphic

    Microsoft Previews Azure IoT Edge for Linux on Windows

    Microsoft announced a preview of Azure IoT Edge for Linux on Windows, which lets organizations tap Linux virtual machine processes that also work with Windows- and Azure-based processes and services.

  • How To Automate Tasks in Azure SQL Database

    Knowing how to automate tasks in the cloud will make you a more productive DBA. Here are the key concepts to understand about cloud scripting and a rundown of the best tools for automating code in Azure.

  • Microsoft Open License To End Next Year for Government and Education Groups

    Microsoft's "Open License program" will end on Jan. 1, 2022, and not just for commercial customers, but also for government, education and nonprofit organizations.

comments powered by Disqus