Sunday
Oct232011

MDW tips and tricks

I love MDW.

It provides at-a-glance wait stats statistics and overall performance data (including disk growth trending and problematic queries) for my SQL 2008 databases. While deploying it in my environment was relatively simple given the ample documentation on the web I did come across a few snags.

The snags were:

  • Avoiding deadlocks in MSDB caused by the default SQL Agent Job schedules
  • Getting it to run successfully across Active Directory domains (where my centralized MDW server was a member of a different domain than the SQL Server I wished to collect information on).

The deadlocking in MSDB was relatively simple to overcome: I altered the schedule of the Collection 3 upload to offset that of the Collection 2 upload. By default the collection_set_2_upload and collection_set_3_upload jobs are set up with a predefined schedule of every 15 minutes. Simply delete the schedule for collection_set_3_upload and create a new schedule of every 20 minutes (or whatever).

The second issue I overcame by creating credential/proxy accounts in SQL 2008. Given that we are in a secure environment I wanted the MDW service account running on the SQL Servers to have the least amount of privilege necessary to access the SQL database. For that reason I allocated only the dc_proxy role for the MSDB database on the SQL server for which it was collecting/uploading statistics.

On the MDW side I granted the mdw_writer role on the MDW database.

The domain service account (ryanboyer.net\Tallarico in the examples below) has modify access to the share to which the collected data is being written.

Here's a breakdown of the commands you'll need to run on your SQL 2008 Server to get started collecting and uploading performance data across Active Directory domains: