SQL Server 2008 introduced the MDW – a Management Data Warehouse. The MDW is a data warehouse for storing performance data about your SQL servers. It is best configured using a centrally located database – in the environment I work in we use our CMS (Central Management Server) as our MDW. The initial setup is performed on your MDW server. You only have 1 choice for creating a MDW database so do it on your CMS. If you go to create your MDW database on the server you want to monitor it will only let you choose your local server. Instead on the monitored server you choose the Data Collector option.
If you are monitoring for deadlocks on your server when you setup the Data Collector, the default schedule for the upload jobs is to upload both jobs at the same 15 min interval. This causes deadlocks. It is easily fixed by staggering the schedules to 1 min apart.
I would recommend a separate MDW database (on your same CMS) for each instance you monitor. The reason for this is that there is a purge job created as part of the MDW setup. If you have 50 servers reporting to the same database then it is going to be extremely difficult to complete the purge in a timely manner each day and your database will grow out of control.
Therefore, using the instmdw.sql script located in:
C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Install
Run this script each time for each MDW database you create. Just make small customisations for the names of data files and the DB name. You can name the database anything you like as you reference this DB in the Data Collector setup. I use MDW_ with a suffix of the servername in our environment.
(to be continued)