SQL Server
One of the new features of SQL Server 2008 R2 is the SQL Server Utility which gives a unified view of your “managed servers” so that you can monitor multiple SQL Servers’ health from a central server called a Utility Control Point (UCP). I have been testing using the R2 August CTP… First of all, I installed a brand new named instance of SQL Server 2008 R2 (10.50.1092) which went smoothly as you would expect from a clean install. I then backed up the AdventureWorks2008 database on a SQL Server 2008 (10.00.1600) instance and restored it to the R2 instance and again, all worked well, including the Filestream data included in the Production.Document table. I then decided to upgrade an instance from SQL 2008 to R2 using the Upgrade option in the Installation Center. Unfortunately, the Upgrade Advisor option was disabled for this CTP. Maybe next time. My first upgrade failed based on a Full Text Indexing option called “Import” which is the default for an upgrade. The error was “illegal characters in path”. Sounds like a CTP bug. I’ll have to report that one. Next time through, I changed that option to “Reset” which will reset the Full Text Indexes without rebuilding them. That did the trick. There is also a third option of “Rebuild” which will rebuild the Full Text Indexes with the new structure for R2. So with two instances of R2, I decided to test out the new SQL Server Utility (SSU). I followed the Books Online article “Overview of SQL Server Utility” which highlighted Step 1 as “Create a utility control point”. I followed the instructions of setting up the UCP which is the “central reasoning point” of the SSU. The UCP hosts the Utility Management Data Warehouse (UMDW). Just what I need, more acronyms…the UMDW is the database where the performance information of multiple servers will be uploaded to for centralized monitoring. By choosing the View menu option “Utility Explorer” then clicking the Create UCP button, the appropriate wizard is launched. Technically, the name of the new database created is sysutility_mdw. Step 2 is “Enroll instances of SQL Server into the SQL Server Utility” and I chose to enroll both of my R2 instances including my UCP itself. Once an instance is enrolled, it becomes a “managed instance” and you can monitor all the managed instances using the SSU graphic displays. With this CTP all managed instances need to be at 2008 R2 level in order to host the “Utility Collection Set” which is uploaded to the central UMDW database. I am hoping future CTPs support other releases of SQL Server for the managed instances. The 2008 feature of a “Data Collection Set” is now refered to as a “non-utility Collection Set” and can operate on a managed instance but the feature has to be disabled before enrolling the instance on the UCP. After enrolling, you can then re-enable the non-utility collection set and that performance data will also upload to the central UMDW database as well as the local MDW database. So the features can co-exist which is important. I tested this out and it worked fine. After setup, the performance data is uploaded from the managed instances every 15 minutes (not configurable in this CTP) and then the SSU can be used to graphically monitor the overall health of those SQL Servers using the Utility Explorer. There is a chart for “Managed Instance Health” which shows how many instances are over or under utilized. You can also drill down to view servers in either category. There are also graphs based on Storage Utilization. You can configure exactly what over or under utilized means by setting Global Policies using the Utility Administration page. For instance, the default threshold for over-utilization is 70%. I will be exploring setting up these policies in future blogs. Obviously, this is a first look at the SQL Server Utility and if I can get it to work, then so can you. I am expecting more functionality in future CTPs but so far so good. Lots of potential. I am most interested in the overhead generated by this new feature. I will have to monitor this new monitoring feature. In the process, maybe I will discover which came first, the chicken or the egg? Watch this space. cheers Brian
