Getting set up for multi-server management with SQL Server 2008 R2

One of SQL Server 2008 R2's most talked-about new features allows DBAs to monitor the performance of multiple instances with a single glance.

One of SQL Server 2008 R2's most anticipated features is centered around its new multi-server management capabilities. As of the current Community Technical Preview (CTP), this feature is referenced as the utility control point (UCP), so that is how it will be referred to in this article.

The UCP allows you to gather a large number of metrics about multiple SQL Server instances and display this data in an easy-to-use dashboard report. This report provides a quick overview of your entire enterprise from a single screen within SQL Server Management Studio. The utility control point does this by creating a database on the server that will function as your UCP. The monitored instances then upload their data to the UCP.

More on SQL Server 2008 R2

See where multi-server management ranked in our list of R2's 10 most significant changes.

When designing your UCP servers for your enterprise environment, there are some licensing limits to keep in mind. For example, SQL Server 2008 R2 Enterprise Edition allows you to enroll up to 25 monitored instances. To enroll more servers, you'll need either another Enterprise Edition UCP or a SQL Server 2008 R2 Datacenter Edition instance (there is no limit to the number of servers that can be enrolled on that UCP).

Setting up the utility control point

Before you can begin capturing data in the UCP, you have to set it up, which requires you to open the Utility Explorer. This is done in SQL Server Management Studio by clicking on the View drop-down menu and selecting Utility Explorer. This opens another tab in the pane where the object explorer is. You can then create a new UCP by clicking the Create Utility Control Point button on the right, as shown in Figure 1. This will launch the Create UCP wizard.

Figure 1. Creating a new UCP

The first page of the wizard is your standard introduction page. The second page lets you select the server instance, which will serve as the UCP. You can also name the UCP to distinguish it from other utility control points in your enterprise.

The third page of the wizard allows you to select a proxy account or use the SQL Agent account for data collection. If your SQL Agent service is running under a domain account, you can choose to use the service account. If it is not running under a domain account, you will want to enter credentials to use as a proxy account so that the SQL Agent job that handles the data collection can access and authenticate against the remote SQL Server instances. The fourth page of the wizard validates that all the rules have passed their validation check. The fifth page shows a basic summary of the information collected, while the sixth page of the wizard creates the objects that will serve as the UCP.

Using the utility control point

After you create the UCP, there won't be any data available since data collection will have just started. To add additional instances, right-click on Managed Instances from under the UCP you just created. This will prompt you with a wizard that allows you to add additional SQL Servers to be monitored.

After the utility control point has collected some data, you'll see a screen similar to Figure 2 that displays how many of your instances are overutilized, underutilized and, most important, well-utilized. You can also see which specific components are over or underused, including the CPU, memory and disk capacity.

Figure 2. The utility control point dashboard (click to enlarge)

If you use DACPACs to deploy applications to your databases, then you will see the utilization of the data-tier application in all its parts.

Note: I'll cover data-tier applications for SQL Server 2008 R2 in more detail in an upcoming article.

Modifying thresholds

Microsoft has set some respectable defaults when it comes to utility control points. When you click on the Utility Administration option under the selected UCP in the Utility Explorer, you can change the settings. By default, the thresholds are 70% for all overutilization settings and 0% for underutilization settings. The 70% overutilization setting probably works for most people, but you might want to adjust the 0% underutilization setting depending on the normal server load for a given system.

From this same section, you can adjust the amount of evaluations per period and how many of them must be in violation before the CPU alarm is triggered.

For the high-utilization alarm, this is done by adjusting a slider from short to long, where short is one hour and long is one week. You can then set the percentage of alarms that must be in violation before the alarm is triggered.

For the low-utilization alarm, the sliders are very similar, except that the time frame is from one day to one month with the same percentage-based scale.

Note: What I've just written is probably pretty confusing; heck, I barely understand it and I wrote it! This is why I've included Figure 3 below. Microsoft has done a reasonably good job making the screen readable and understandable.

Figure 3. Setting thresholds for server utilization (click to enlarge)

If needed, you can specify instance-specific policies by editing the managed instance and clicking on the Policy Details tab of the instance properties. This allows you to apply specific settings for instances that don't fall within the global policies of the other servers that are managed by the UCP.

The final steps

Now that you have this information, the key is to find a way to use it to make intelligent decisions. From a single glance, you can see which servers are overutilized and which ones have room available on them. This should allow you to make better use of your environment. Simply put, if you know that a database application on Server1 has a high CPU load and that Server2 has a very low CPU load, then that database application might be a good candidate to move from one server to the other.


 Denny Cherry has over a decade of experience managing SQL Server, including MySpace.com's over 175-million-user installation, one of the largest in the world. Denny's areas of expertise include system architecture, performance tuning, replication and troubleshooting. He currently holds several Microsoft certifications related to SQL Server and is a Microsoft MVP.
Check out his blog: SQL Server with Mr. Denny

Dig Deeper on Microsoft SQL Server Consolidation and Virtualization