Microsoft SQL Server 2008 R2 Utility Control Point – Multi Server Management

  • Article

Microsoft SQL Server 2008 R2 Utility Control Point – Multi Server Management

In today’s world most challenging question for SQL Server DBA is how to monitor our database environment

Ø Hundreds of databases

Ø Most databases have a single Database File and one Transaction Log file

Ø CPU and Storage is a major pain area in our landscape

Ø In some cases, the hardware is over utilized and in some cases it is under utilized

Ø Sudden changes are surprises and flooded with alerts

 

clip_image001

 

Utility Control point surfaces our need as database administrator which is a new feature in SQL Server 2008 R2. These are the actions that can be performed using UCP.

Ø Central Point of reasoning for Multi-Server Management – Daily Health Check

Ø Meaningful Dashboard to provide insight into utilization of CPU and Storage – Proactive Maintenance

Ø Drill-down reports to identify disk utilization up to file level

Ø Analysis of CPU utilization over a given period (Day, Week, Month and Year)

Ø Capacity Planning

 

clip_image002

This is the overview of the UCP dashboard , which tells me in our environment 7 instances are over utilized out of 19 instances

Ø Managed Instances indicates the number of instances are getting managed through UCP  maximum is 25

Ø It also describes well utilized,over utilized,under utilized and no data available.

Ø we can still drill down to the instance level.

clip_image004

Managed Instances - CPU Utilization

For each Instance we can have the data for the below points

Ø Instance CPU

Ø Computer CPU

Ø File Space

Ø Volume Space

Ø Policy Type

2. CPU utilization graph describes the utilization of CPU for Instance and Server. Also we can have daily, weekly, monthly and year wise data and identify the trends

Red Arrow indicates that the server is over utilized based on the policy defined.

Please see the below screen shots from which we can identify the trend for the SQL instance test1

clip_image005

 

clip_image007

clip_image009 clip_image011

Managed Instances - Storage Utilization Per Database

1.For each Instance we can have the data for the below points

Ø Storage Utilization per Database

Ø Storage utilization per drive

2.Storage utilization graph describes the utilization of Space for Instance and Server.

3. this also includes if you have any mounted volumes on the server

The figure shows the utilization per database including the database file

clip_image012

Ø Storage utilization details for all the drives is listed in percentage.

Ø We can get the data per day, week, month and year.

clip_image013

UCP-Utilization Policies

Ø Global policies are defined for disk and CPU utilization.

Ø As per global policy > 70% is over utilized.

Ø We can override the global policies by defining the utilization limits.

clip_image014

Property Details for Managed Instance

UCP also provide the property details of the managed instances.

Ø Processor Name

Ø No of Processors

Ø Is clustered

Ø OS Version

Ø SQL Version

Ø SQL server Edition

Ø Collation

Ø Case Sensitive

clip_image015

Ok now who are all can administer the UCP dashboard , well we do have permissions settings which DBA can control. All sysadmin accounts will act as administrators

 

clip_image017

 

 

in UCP and we can grant read permissions to specific logins who can look at the  dash board and monitor the server

Well we are done , now we know that we can use UCP to monitor all the servers in our environment from one single dashboard. Also I can create multiple UCP instances and use it for Dev , test and production environments.