The Management Data Warehouse introduced in SQL Server 2008 is a comprehensive performance monitoring and reporting system. It is a handy way to consolidate performance monitoring and data for all your servers on a single system.
A couple of minor gotcha’s to consider when setting up the data collector. The setup is so easy it is tempting to do it on auto-pilot. Don’t.
- This is an obvious one, but I’m pretty sure it happens more than you would expect. Make sure to create a blank database on your monitoring server where you will store the aggregated data collection sets. Don’t accidentally pick one of your production databases or it will add all the tables, views, stored procedures, functions, types, schemas, and other logging objects into that database (there are a lot of them). It will also mark that database as a data collection warehouse which will show up in the database list when you configure data collection on your other servers.
- Make sure to create the temp folder that will be used to cache the performance data until the periodic upload to the Management Data Warehouse. If the folder is not created, the upload jobs will fail.
photo credit: Nick Saltmarsh / CC BY 2.0