Monday, November 15, 2010
Removing the SQL Server Management Data Warehouse (or not!)
I have SQL Server Management Data Warehouses implemented on dozens of servers in various customer sites. The data it collects is great, especially considering that it's free and easy to implement (initially). But as with most free things, it has some drawbacks. Most of all, sometimes it's not very reliable. In some environments, I spent more time chasing down issues with the SSIS packages it runs than reviewing the data it collects. Some of my clients have chosen to invest in commercial monitoring tools such as Quest Spotlight, Idera Diagnostic Manager, SQL Sentry, Redgate SQL Monitor or Confio Ignite. Each of these tools has pluses and minuses, but overall they are all more reliable than the Management Data Warehouse.
The net result is that I'm working to decommission Management Data Warehouses in various environments. Unfortunately, I discovered that Microsoft never implemented a removal utility for the Management Data Warehouse. Without manual intervention, the best you can do is disable it. However, it leaves it's jobs behind, which in some cases had a last status of Failed. This clutters up my reporting and it drives me batty.
Initially I cleared the job failures so that they wouldn't show up in any more reports. Now I'm looking at how to remove the jobs and objects completely. Sadly, Microsoft still hasn't published information about this. DBAs are complaining, but apparently it isn't a big priority.
So, that leaves the manual steps of removing the fragments of the Management Data Warehouse from the server. The removal workaround documented on the support site isn't supported, nor was it recommended by Microsoft. Moreover, it references a table that doesn't exist.
I've decided to live with it wait and see what Microsoft puts out in a future service pack before attempting a crude hack that might make it impossible to fully remove the feature when Microsoft finally gets around to this issue.