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:
- Utility control point (UCP). The UCP is configured on a SQL Server instance. It provides the foundation for the SQL Server Utility, using SQL Server Management Studio (SSMS) to monitor SQL Server resource health and utilization. The UCP supports a number of actions, including specifying resource utilization policies that track an organization’s utilization requirements.
- Utility Explorer. Accessed from SSMS, this interface lets you manage and control the SQL Server Utility. You can use Utility Explorer to connect to a utility, create a UCP, enroll the SQL Server instances to be managed, manage utilities, view dashboard and drilldown views affiliated with managed instances of SQL Server, and more.
- Utility management data warehouse (UMDW). This relational database stores data collected by managed instances of SQL Server. The UMDW database (sysutility_mdw) is automatically created on a SQL Server instance when the UCP is created. It utilizes the simple recovery model and requires approximately 2GB of storage for every managed instance of SQL Server per year.
- Utility Information data collection set. When an instance is managed by the UCP, the Utility Information data collection set is installed and automatically started. It collects data and forwards it to the UMDW.
- Data-tier application (DAC). A DAC is a single unit for developing, deploying, and managing data-tier objects. A DAC package includes database application schema, tables, views, stored procedures, and logins. A DAC can be generated with the new SQL Server Data-tier Application project template in Visual Studio 2010 or by reverse engineering an existing database.
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.
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:
- The database engine instance hosting the UCP must be SQL Server 2008 R2 or later. It’s recommended that the collation settings affiliated with this database engine instance be case insensitive.
- For production environments, the instance hosting the UCP must be either the Datacenter or Enterprise Edition of SQL Server 2008 R2 or later.
- The SQL Server Utility must operate within a single Windows domain or across domains with two-way trust relationships.
- The SQL Server Agent service must be started.
- The SQL Server Agent service account can’t be a built-in account such as Network Service. It should be a valid Windows domain account on the specified instance.
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.
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.
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:
- You must have administrator privileges on the SQL Server instance.
- The specified instance must be SQL Server 2008 SP2 or later.
- The SQL Server edition must support instance enrollment.
- The SQL Server instance can’t be enrolled in any other UCP.
- The SQL Server instance can’t already be a UCP.
- The SQL Server instance must have the utility collection set installed.
- The Utility Information data collection set on the SQL Server instance must be stopped.
- The SQL Server Agent service on the specified instance must be started and configured to start automatically.
- The SQL Server Agent proxy account can’t be a built-in account, such as Network Service.
- The SQL Server Agent proxy account must be a valid Windows domain account on the specified instance.
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.
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.
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:
- Global Policies for Data-tier Applications. In this section, you can view or configure the CPU, file-space, and storage-volume utilization policies for all DACs. You can also specify the computer CPU utilization policy for them. When configuring a utilization policy, you set upper and lower limits. For example, you might specify that a DAC’s CPU is to be considered overutilized when it’s greater than 65 percent and underutilized when it’s less than 10 percent. You can individually configure file-space utilization settings for both the data file and log file associated with a DAC.
- Global Policies for Managed Instances. In this section, you can view or configure the instance-CPU, file-space, and storage-volume utilization policies for all managed instances. In addition, you can specify the computer-CPU utilization policy for them. Like in the Global Policies for Data-tier Applications section, you set upper and lower limits to specify what is considered overutilized and underutilized. You can also individually configure the file-space utilization settings for both the data file and log file.
- Volatile Resource Policy Evaluation. This section, which is displayed in Figure 6, provides settings that dictate how often CPU utilization policies should be reported. After you have chosen your settings, a detailed summary will appear below each section. For example, if you set the Evaluate SQL Server Utility policies over this moving time window slider to 1 hour and the Percent of SQL Server Utility policies in violation during the time window before CPU is reported as overutilized slider to 20 percent, the following summary is displayed: Over 1 hour, there will be 4 policy evaluations, and 1 must be in violation before the CPU is marked as overutilized.
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.