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.
Choose “Create a Utility Control Point (UCP)”, and you will see the first screen in that wizard.
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.
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.
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.
If every test passes (or you just have warnings and no errors), click Next, and you will see “Summary of UCP Creation”.
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.
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.
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.
