SQL Server 2008 R2: Multiserver Management Using Utility Explorer

Exclusive offer: get 50% off this eBook here
Microsoft SQL Server 2008 R2 Administration Cookbook

Microsoft SQL Server 2008 R2 Administration Cookbook — Save 50%

Over 70 practical recipes for administering a high-performance SQL Server 2008 R2 system with this book and eBook

$32.99    $16.50
by Satya Shyam K Jayanty | June 2011 | Cookbooks Enterprise Articles Microsoft

SQL Server 2008 R2 improves the ability to manage multiple servers centrally with Utility Control Point (UCP). In order to manage multiple instances efficiently, there are certain settings available within the Utility Explorer tool. In this recipe by Satya Shyam K Jayanty, author of Microsoft SQL Server 2008 R2 Administration Cookbook, we will focus on how to manage multiple instances using Utility Explorer by setting global policies for data-tier applications (DAC), and managed instances.

 

Microsoft SQL Server 2008 R2 Administration Cookbook

Microsoft SQL Server 2008 R2 Administration Cookbook

Over 70 practical recipes for administering a high-performance SQL Server 2008 R2 system with this book and eBook

        Read more about this book      

(For more resources on Microsoft SQL Server, see here.)

The UCP collects configuration and performance information that includes database file space utilization, CPU utilization, and storage volume utilization from each enrolled instance. Using Utility Explorer helps you to troubleshoot the resource health issues identified by SQL Server UCP. The issues might include mitigating over-utilized CPU on a single instance or multiple instances.

UCP also helps in reporting troubleshooting information using SQL Server Utility on issues that might include resolving a failed operation to enroll an instance of SQL Server with a UCP, troubleshooting failed data collection resulting in gray icons in the managed instance list view on a UCP, mitigating performance bottlenecks, or resolving resource health issues.

The reader will benefit by referring the previous articles on Best Practices for SQL Server 2008 R2 Administration and Managing the Core Database Engine before proceeding ahead.

Getting ready

The UCP and all managed instances of SQL Server must satisfy the following prerequisites:

  • UCP SQL Server instance version must be SQL Server 2008 SP2[10.00.4000.00] or higher
  • The managed instances must be a database engine only and the edition must be Datacenter or Enterprise on a production environment
  • UCP managed account must operate within a single Windows domain or domains with two-way trust relationships
  • The SQL Server service accounts for UCP and managed instances must have read permission to Users in Active Directory

To set up the SQL Server Utility you need to:

  • Create a UCP from the SQL Server Utility
  • Enroll data-tier applications
  • Enroll instances of SQL Server with the UCP
  • Define Global and Instance level policies, and manage and monitor the instances.

Since the UCP itself becomes a managed instance automatically, once the UCP wizard is completed, the Utility Explorer content will display a graphical view of various parameters, as follows:

How to do it...

To define the global and instance level policies to monitor the multiple instances, use the Utility Explorer from SSMS tool and complete the following steps:

  1. Click on Utility Explorer; populate the server that is registered as utility control point.
  2. On the right-hand screen, click on the Utility Administration pane.
  3. The evaluable time period and tolerance for percent violations are configurable using Policy tab settings.
  4. The default upper threshold utilization is 70 percent for CPU, data file space, and storage volume utilization values. To change the policies use the slider-controls (up or down) to the right of each policy description.
  5. For this recipe, we have modified the upper thresholds for CPU utilization as 50 percent and data file space utilization as 80 percent. We have also reduced the upper limit for the storage volume utilization parameter.
  6. The default lower threshold utilization is 0 percent for CPU, data file space, and storage volume utilization values. To change the policies, use the slider-controls (up only) to the right of each policy description.

    For this recipe, we have modified (increased) the lower threshold for CPU utilization to 5 percent.

  7. Once the threshold parameters are changed, click Apply to take into effect. For the default system settings, either click on the Restore Defaults button or the Discard button, as shown in the following screenshot:

Now, let us test whether the defined global policies are working or not.

  1. From the Query Editor, open a new connection against SQL instances, which is registered as Managed Instance on UCP, and execute the following time-intensive TSQL statements:

    create table test (
    x int not null,
    y char(896) not null default (''),
    z char(120) not null default('')
    )
    go
    insert test (x)
    select r
    from
    (
    selectrow_number() over (order by (select 1)) r
    from master..spt_values a, master..spt_values b
    ) p
    where r <= 4000000
    go
    create clustered index ix_x on test (x, y)
    with fillfactor=51
    go

  2. The script will simulate a data load process that will lead into a slow performance on managed SQL instance. After a few minutes, right-click on the Managed Instances option on Utility Explorer, which will produce the following screenshot of managed instances:

  3. In addition to the snapshot of utilization information, click on the Managed Instances option on Utility Explorer to obtain information on over-utilized database files on an individual instance (see the next screenshot):

We should now have completed the strategic steps to manage multiple instances using the Utility Explorer tool.

How it works...

The unified view of instances from Utility Explorer is the starting point of application and multi-server management that helps the DBAs to manage the multiple instances efficiently.

Within the UCP, each managed instance of SQL Server is instrumented with a data collection set that queries configuration and performance data and stores it back in UMDW on the UCP every 15 minutes. By default, the data-tier applications automatically become managed by the SQL Server utility. Both of these entities are managed and monitored based on the global policy definitions or individual policy definitions.

Troubleshooting resource health issues identified by an SQL Server UCP might include mitigating over-utilized CPU on a computer on an instance of SQL Server, or mitigating over-utilized CPU for a data-tier application. Other issues might include resolving over-utilized file space for database files or resolving over-utilization of allocated disk space on a storage volume. The managed instances health parameter collects the following system resource information:

  • CPU utilization for the instance of SQL Server
  • Database files utilization
  • Storage volume space utilization
  • CPU utilization for the computer

Status for each parameter is divided into four categories:

  • Well-utilized: Number of managed instances of an SQL Server, which are not violating resource utilization policies.
  • Under-utilized: Number of managed resources, which are violating resource underutilization policies.
  • Over-utilized: Number of managed resources, which are violating resource overutilization policies.
  • No Data Available: Data is not available for managed instances of SQL Server as either the instance of SQL Server has just been enrolled and the first data collection operation is not completed, or because there is a problem with the managed instance of SQL Server collecting and uploading data to the UCP.

The data collection process begins immediately, but it can take up to 30 minutes for data to appear in the dashboard and viewpoints in the Utility Explorer content pane. However, the data collection set for each managed instance of an SQL Server will send relevant configuration and performance data to the UCP every 15 minutes.

Summary

This article on SQL Server 2008 R2 covered Multiserver Management Using Utility Explorer.


Further resources on this subject:


Microsoft SQL Server 2008 R2 Administration Cookbook Over 70 practical recipes for administering a high-performance SQL Server 2008 R2 system with this book and eBook
Published: May 2011
eBook Price: $32.99
Book Price: $54.99
See more
Select your format and quantity:

About the Author :


Satya Shyam K Jayanty

Satya Shyam K Jayanty is an Independent consultant working as a Principal Architect and Director for D Bi A Solutions Limited based in the UK. He has a Master's degree in Economics and Commerce from Osmania University, Hyderabad (India) and an MBA – Systems from the University of the West of England. Satya started his career in the IT industry in 1992, and he has been working with SQL Server (from version 4.2) for more than 15 years and has been an accomplished Microsoft SQL Server MVP for the last six years. Satya is a frequent speaker and SME volunteer at Microsoft Tech-Ed, SQL PASS, SQL Bits, Scottish Area SQL Server user group. He maintains a blog at http://www.sqlserver-qa.net and http://www.sql-server-performance.com.

Books From Packt


Microsoft SQL Server 2008 High Availability
Microsoft SQL Server 2008 High Availability

Microsoft SQL Server 2008 R2 Master Data Services: RAW
Microsoft SQL Server 2008 R2 Master Data Services: RAW

Learning SQL Server 2008 Reporting Services
Learning SQL Server 2008 Reporting Services

Expert Cube Development with Microsoft SQL Server 2008 Analysis Services
Expert Cube Development with Microsoft SQL Server 2008 Analysis Services

Microsoft Data Protection Manager 2010
Microsoft Data Protection Manager 2010

.NET Compact Framework 3.5 Data Driven Applications
.NET Compact Framework 3.5 Data Driven Applications

Software Testing using Visual Studio 2010
Software Testing using Visual Studio 2010

MDX with Microsoft SQL Server 2008 R2 Analysis Services Cookbook: RAW
MDX with Microsoft SQL Server 2008 R2 Analysis Services Cookbook: RAW


No votes yet

Post new comment

CAPTCHA
This question is for testing whether you are a human visitor and to prevent automated spam submissions.
F
u
7
D
2
G
Enter the code without spaces and pay attention to upper/lower case.
Code Download and Errata
Packt Anytime, Anywhere
Register Books
Print Upgrades
eBook Downloads
Video Support
Contact Us
Awards Voting Nominations Previous Winners
Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
Resources
Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software