Introduction to MSSQL Management Data Warehouse (MDW) presentation

Last night I presented an intro to SQL Server 2008’s MDW to the fwPASS Professional Association. The subject is primarily of interest to DBA’s rather than Developers since it is best leveraged by monitoring and managing an environment of multiple SQL Servers.

Introduction to SQL Server 2008 Management Data Warehouse (MDW)

View more presentations from Dean Willson.

SQL Server Management Data Warehouse

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.

1. 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.
2. 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

Fort Wayne SQL Server Presentation - Performance Monitoring and Baselining

Click here to download my FWPass presentation from November 25, 2008. The meeting was held upstairs at the Pint and Slice in downtown Fort Wayne, Indiana. The meeting format was a little different because someone apparently walked off with their VGA cable for the overhead projector. The backup plan was to use the big flat panel display, but my laptop didn't have S-Video output. In leiu of everyone peering over my shoulder at the laptop display, we just discussed the material. The atmosphere and food were great. It was a small informal group of six or seven, but it was still fun.