Introducing the SQL Server Utility

Over the years, an increasing number of organizations have turned to SQL Server because it embodies the Microsoft Data Platform vision. This vision helps organizations scale database operations with confidence, improve IT and developer efficiency, and offer self-service business intelligence (BI). However, this wide adoption of SQL Server introduced minor challenges, especially in the area of application and multi-instance management when monitoring resource health and utilization with native tools.

Related: SQL Server 2008 R2 New Features

Microsoft tackled some of these challenges by introducing two multi-instance management capabilities in SQL Server 2008. Policy Based Management and Data Collector dramatically changed how DBAs managed SQL Server instances and databases. Policy Based Management gave organizations the ability to leverage predefined conditions and expressions to create policies for managing one or more databases or instances of SQL Server. Data Collector was used to obtain, save, and analyze data gathered from several sources.

Although Policy Based Management and Data Collector were intuitive, DBAs still required additional tools to proactively manage resource utilization across all of the SQL Server instances and applications within their enterprise. SQL Server 2008 R2 addresses this need for additional multi-instance management tools through the introduction of the SQL Server Utility.

I’ll introduce you to the SQL Server Utility and other native tools in SQL Server 2008 R2 that let you easily monitor the health and utilization of your resources so that you can proactively and more effectively manage your database environment. I’ll then show you how to use the tools.

Introducing the SQL Server Utility

The SQL Server Utility provides a holistic view of the health and utilization of the resources associated with managed instances of SQL Server and registered database applications. Here are the components and terms affiliated with it:

Figure 1 illustrates the workflows associated with the SQL Server Utility. In this figure, a UCP has been deployed within a production environment and is managing many SQL Server instances. A DBA is viewing the dashboard in SSMS to gain insight into resource health and utilization. Finally, a data-tier developer has created a DAC with Visual Studio, and a DBA has deployed the DAC to a managed instance of SQL Server.

Figure 1: Using the SQL Server Utility to manage multiple instances of SQL Server

Deploying a UCP

UCP deployment is paramount in the operation of the SQL Server Utility. SSMS’s Create Utility Control Point Wizard offers a simple way to create and deploy one. If you’re a scripting fan, it’s possible to create a UCP with Windows PowerShell. No matter which method you use, you need to first satisfy several requirements:

Once you’re sure these requirements have been met, open SSMS, go to the Utility Explorer pane, and connect to the SQL Server 2008 R2 database engine instance where you plan on deploying the UCP. (If the Utility Explorer pane isn’t visible, select Utility Explorer on the View menu.) Invoke the Create Utility Control Point Wizard by either clicking the Create Utility Control Point icon on the Utility Explorer toolbar or clicking the Create a Utility Control Point (UCP) link on the Getting Started tab. Review the message on the Introduction page, then click Next to begin the UCP creation process.

On the Specify the Instance of SQL Server page, click the Connect button and specify the SQL Server instance on which to create the new UCP. Specify a name to identify the UCP on the network. In this example, Production Utility is used, as shown in Figure 2. Click Next.

Figure 2: Specifying where to create the UCP

On the Utility Collection Set Account page, specify the Windows domain account that will be used as the SQL Server Agent proxy account for the Utility Information data collection set. Alternatively, you can select the SQL Server Agent service account if the agent account is a Windows domain account. Click Next.

Review the UCP configuration settings on the Summary of UCP Creation page, then click next to commence the creation of the UCP. On the SQL Server Instance Validation page, review the results for each action associated with the generation of the UCP, as shown in Figure 3. If an action fails, address the problem and rerun the Validation test. Once all the actions are successful, click Finish to solidify the creation of the UCP.

Figure 3: Making sure the SQL Server instance serving as the UCP passes the validation tests

Enrolling SQL Server Instances

Now that the UCP has been deployed, the next step is to enroll the SQL Server instances to be managed. This task can be accomplished with SSMS’s Enroll Instance Wizard or with PowerShell. Because the enrollment process is conducted one SQL Server instance at a time with the wizard, it might be beneficial to use PowerShell to save time if you need to enroll many SQL Server instances. Note that the SQL Server instance designated as the UCP is automatically enrolled during the creation process.

Before you can enroll an SQL Server instance, you need to satisfy these requirements:

When you’re confident each requirement is satisfied, expand the UCP you just created in the Utility Explorer tree. Right-click the Managed Instances node, then select Add Managed Instance to invoke the Enroll Instance Wizard. After reviewing the notes on the Introduction page, click Next to begin the enrollment process.

On the Specify the Instance of SQL Server page, click the Connect button and enter the name of the SQL Server instance to enroll in the UCP. Click next.

On the Utility Collection Set Account page, specify the Windows domain account you’re going to use to run the Utility Information data collection set. Alternatively, select the SQL Server Agent Service account if the agent account is a Windows domain account. Click Next.

On the SQL Server Instance Validation page, make sure that each action successfully passes. If an action fails, address the problem and rerun the Validation test. Once all the actions are successful, click Finish.

On the Summary of Instance Enrollment page, review the settings to enroll the SQL Server instance and click Next. Review the status of the enrollment operations on the Enrolling the Instance of SQL Server page, then click Finish to complete the enrollment. Repeat this process to enroll additional SQL Server instances.

Working with DACs

The SQL Server Utility can provide resource health and utilization for not only managed instances of SQL Server but also deployed DACs. It’s possible to view metrics associated with application CPU, application database or log files, application storage volumes, and computer CPU. Before you can view the data affiliated with a DAC, the DAC must be deployed on or registered with a managed instance of SQL Server.

For those of you who are unfamiliar with creating, deploying, and registering DACs, here’s the basic process: First, create a DAC. You can do this by using Visual Studio 2010’s SQL Server Data-tier Application project template to build a DAC or by using SSMS’s Extract Data-tier Application Wizard to extract a DAC definition from an existing database.

After the DAC is created, you can use SSMS’s Deploy Data-tier Application Wizard to deploy the DAC package to a managed instance of SQL Server. To invoke the wizard, right-click the Data-tier Applications node under the Management folder and select Deploy Data-tier Application. Alternatively, you can register a DAC with a managed instance by right-clicking an existing database and selecting the Register Data-tier Application Wizard from the Tasks menu. After the DAC is successfully deployed or registered, you can use it to monitor the health and utilization of resources.

Monitoring Resource Health and Utilization

The Utility Explorer dashboard provides a holistic view of resource health and utilization for both managed instances of SQL Server and DACs. To view the dashboard, simply click the UCP node in Utility Explorer. Summary data and drilldown utilization views are displayed in the Utility Explorer Content pane, as Figure 4 shows.

Figure 4: Checking the health and utilization of resources in Utility Explorer’s dashboard

The data summarized in the dashboard can significantly increase your understanding of resource health and utilization. For example, by viewing the summary data together with any related graphs and drilldown utilization views, you can quickly identify whether the instance CPU, database files, storage volumes, or computer CPU of managed instances or DACs are well utilized, overutilized, or underutilized. This information can help you optimize resource utilization, which translates into a lower total cost of ownership (TCO) associated with the SQL Server infrastructure.

For additional, more detailed information about DACs or managed instances of SQL Server, you can click the Data-tier Applications or Managed Instances node in the tree. For example, if you click the Managed Instances node, the Utility Explorer Content pane lists the managed instances and displays key utilization metrics for each one, as Figure 5 shows. If you highlight a managed instance, you can view detailed data about its CPU and storage utilization, as well as policy and property details. Figure 5 shows the Storage Utilization view.

Figure 5: Checking the Storage Utilization view for a managed instance

Managing the SQL Server Utility

To manage the policy, security, and data warehouse settings for the SQL Server Utility, you need to use the Utility Administration node in Utility Explorer. When you click the node, the Utility Explorer Content pane displays the Policy, Security, and Data Warehouse tabs.

Policy tab. You use the Policy tab to review or change global policy settings for managed instances and DACs. In addition, you use it to specify how often utilization policies should be reported. As Figure 6 shows, it has the following three sections:

Security tab. You use the Security tab to grant logins the Utility Reader role. The Security tab is also useful for viewing Utility Reader attributes on existing logins. The Utility Reader privilege allows a DBA the ability to connect to the SQL Server Utility, view the dashboards and drilldown utilization views, and observe settings associated with the UCP. DBAs can simply enable the Utility Reader option next to a login to have access to the Utility Reader role. If a login doesn’t exist, you can create a new user by adding logins through the Security folder in SSMS’s Object Explorer. By default, users with sysadmin privileges can administer a UCP and all of the associated management tasks.

Data Warehouse tab. You use the Data Warehouse tab to review or make configuration changes to the UMDW database associated with a UCP. For example, you can modify the data retention period for the UMDW configuration. The default setting is set at one year, but you can change it to one month, three months, six months, or two years.

Time Well Spent

As you’ve seen, the SQL Server Utility is easy to configure and use—and it’s time well spent because it can help you monitor and manage resource utilization across all the managed instances and DACs in your SQL Server environment. By taking advantage of the data obtained through the dashboard and drilldown utilization views, you can improve service levels and identify consolidation opportunities, which can reduce the TCO associated with the SQL Server implementation.