Insufficient data from Andrew Fryer

The place where I page to when my brain is full up of stuff about the Microsoft platform

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… 


image


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..


image


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


 


image


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..


image


Now the UCP wizard will runs some tests..


image


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..


image 


I hit next & the install begins..


image


 


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


image


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..


image


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..


image


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..


image


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..


image


 


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.