SQL Server 2008 R2 Utility
Posted by decipherinfosys on June 1, 2010
SQL Server 2008 R2 has introduced SQL Server utility as part of it’s multi-server administration enhancements – it is the center of operations for consolidating and monitoring the managed instances of SQL Server. You can launch the utility explorer from the View option in the Menu in SSMS. If you are launching it the first time, you will see the following screen in SSMS detailing several video links and wizards for the Utility Configuration Steps:
First thing to do would be to create a utility control point. You can simply use the wizard and the steps are fairly straightforward. Once it runs the instance validation, you might have to make certain changes like enabling TCP/IP for an instance (can be done using the SQL Server Configuration Manager) or troubleshooting WMI configuration – this article might help for the WMI piece. Once everything goes through fine and you do not have any errors (it will allow you to proceed if there are warnings though it is highly recommended to fix those issues before you proceed), you will now have a UCP (Utility Control Point) and you will be able to see it under the utility explorer. In addition, you will be able to see a database called: sysutility_mdw under your list of databases in object explorer. By default the collection frequency is set to every 15 minutes and the data retention is set to 1 year.
And in the Utility Explorer Content, you will be able to see a graphical representation of the different aspects of the managed instance:
You should note that this feature is available only in Enterprise Edition and the premium editions. Also, another thing to note is that this can be used only for SQL Server 2008 R2 since the version level of the monitored instance of SQL Server needs to be compatible with the version level of the UCP.
One thing to note over here is that in the left pane you have three enteries in the node – Data Tier Applications, Managed Instances and Utility Administration. Out of these, we had covered Data Tier Applications before – you can read more on it over here.
As far as the managed instances are concerned, we will see the information in the pane over there about the CPU utilization, the storage utilization etc. which should raise an immediate question – based on what values is it considered to be performing well or performing less than optimal? The answer to that lies in the third node – Utility Administration – if one clicks over there, one can manage the policy, the security and the data warehouse settings for a SQL Server utility:
Using this, one can set up the different parameter thresholds. One thing to note is that these are global policies. As with any other new features, it will take some time to fine tune these settings as per your environments and to become comfortable with them.