Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQL Server Utility in SQL Server 2008 R2

One of the new management related features in SQL Server 2008 R2 is SQL Server Utility. SQL Server Utility gives you a way to manage and monitor multiple instances of SQL Server using a variation of Management Data Warehouse. You can use this to monitor things such as CPU utilization and disk space utilization. SQL Server Utility is an Enterprise Edition and above only feature, that requires SQL Server 2008 R2 for both the Utility Control Point (UCP) and all managed instances (at least in the November 2009 CTP of SQL Server 2008 R2). SQL Server 2008 R2 Enterprise Edition has a limit of 25 managed instances in each UCP.

If you want to use SQL Server Utility, the first thing you need to do is create a Utility Control Point. The UCP is where all of the monitoring data from your managed instances will be stored. You need to make sure that you have TCP/IP enabled on the instance where the UCP will live. You also need to make sure that SQL Agent is running and is set to start automatically.

Inside of SQL Server Management Studio (SSMS), you select Utility Explorer from the View menu. You will see the Utility Configuration Steps screen for a series of wizards that allow you to do most SQL Server Utility related tasks.

image

Choose “Create a Utility Control Point (UCP)”, and you will see the first screen in that wizard.

image

Click on Next, and you will see “Specify the Instance of SQL Server”. You need to specify the instance where the UCP will be located, and supply login credentials. You also need to give the UCP a relevant name.

image

Click Next, and you will see “Utility Collection Set Account”. You need to specify a Windows domain account that will be used as the SQL Server Agent proxy account for the utility collection set.

image

Click Next, and you will see  “SQL Server Instance Validation”, and the wizard will run a series of twelve validation tests to make sure the the instance will work as a UCP. The WMI validation test may take some time, so don’t be alarmed.

image

If every test passes (or you just have warnings and no errors), click Next, and you will see “Summary of UCP Creation”.

image

Click Next, and you will see “Utility Control Point Creation”, which will create the utility management data warehouse database, configure the UCP, and enroll the UCP instance as a managed instance.

image

Click Finish, and you will see a summary of the UCP instance’s health (with no data). Wait a few minutes, and you should start to see some more interesting data appear in the various reports.

image

If you look under databases in Object Explorer, you will see a new database called sysutility_mdw. If you go to SQL Agent, and look under Jobs, you will see seven new Agent jobs that are used to collect data from the managed instances into the sysutility_mdw.

Comments

Posted by elmasre_201038 65679 on 24 September 2011

when i make this setup in step no 5 show to me 2 error

1-

TITLE: Result

------------------------------

The SQL Server Agent service must be started.  If the specified instance of SQL Server is a SQL Server failover cluster instance, the SQL Server Agent service must be configured to start manually.  Otherwise, the SQL Server Agent service must be configured to start automatically. Validation completed with a warning. The SQL Server Agent service is not configured to start automatically on the specified instance. For more information, see the Help topic for this operation.

------------------------------

BUTTONS:

OK

------------------------------

2-

TITLE: Result

------------------------------

The SQL Server Agent proxy account must be a valid Windows domain account on the specified instance. Validation failed. The operation failed to create a login on the specified instance of SQL Server.  Possible reasons include invalid username and password, no permission to create a login, or a policy on the specified instance of SQL Server that prevents login creation. To continue, verify that the specified username and password are correct, then verify that Windows permissions and SQL Server PBM policies allow the specified account to run the Windows CREATE LOGIN operation. The error reported for this operation was:

'Elmasry' is not a valid Windows NT name. Give the complete name: <domain\username>.

For more information, see the Help topic for this operation.

------------------------------

BUTTONS:

OK

------------------------------

Posted by Michael.Baker on 3 July 2012

OK, so SQL Enterprise for everything; MS will love that.

Posted by brian.cole on 16 July 2012

Ugh, I just learned that if you have UCP monitoring a cluster, and you fail over the cluster, it loses all history of the volumes and databases from before the failover. So this tool is virtually useless in a production environment.

Posted by rick.gotner on 12 February 2013

Don't bother with this if you're administering in a NetApp environment ... you already have vastly superior monitoring capabilities to what a UCP offers.  I inherited one created by a contractor before I came on board, and am removing the UCP from our network now.

Leave a Comment

Please register or log in to leave a comment.