Getting Started with Multi-Server Management

In SQL Server 2008 R2 multi server management is expanded by the use of a Utility Control Point.  This article shows you how to set it up from a clean install of SQL Server.

Open SQL Server Management Studio (SSMS) and go to the menu -> view –> utility explorer… 

From here you can sit back and watch what it does.  For those that hate watching videos I am going to create a UCP & show you the screen grabs starting with the wizard flash screen..

First I need to connect to the instance I want to create the UCP

Then I need to specify an account that is going to be used to go and get the telemetry for this instance which  is gathered using SQL Agent running periodically.  I am going to use the SQL Agent because it’s running with a domain account that I use for all my SQL related services on my demo rig, contoso\sqlservice as you can see in SQL Server configuration manager..

Now the UCP wizard will runs some tests..

Mine has failed because SQL agent needs to be running and set to start automatically so back to the SQL Server Configuration Manager (above) to do that. Now I can rerun the validation and proceed to the confirmation or summary step..

I hit next & the install begins..

and finishes successfully.  If I close this dialog I now get my management dashboard..

which is empty. So what’s behind this tow key things some agent jobs and a management data warehouse based on what there was in SQL Server 2008..

Now that it is installed you can do several things…

Enrol other instances so can manage see the status of all your SQL Server instances in one place. However be aware that it is currently only going to work against other instances of SQL Server 2008 R2.

Select Utility Administration to

Setup the threshold levels to indicate when servers are over uilised which can be down ata global level or specially set for individual instance..

Assign which accounts can access the dashboard from the security tab above

Set how long the the database should keep information from the data warehouse tab..

Once I have configured the global and specific policies for each managed instance I can just keep an eye on the overall dashboard or drill into the details for each instance by selecting it..

note the tabs in the middle of the right pane above whee I can choose to look at CPU or storage utilisation, the polices specific to this instance and its overall properties.