Short summary of how I used to do this
- Create small db on every instance to hold data.
- write scripts to gather metrics, separate table for each metric. Use a separate job to schedule each set of metrics as needed (some daily, some hourly, etc.)
- Put a report table in the db that just holds char fields, an instance name, and an ordering field
- Write a proc that "builds" a report, putting lines of data in the report table, ordering them as needed, and including the instance name. This gives you a report daily, on each server.
- Write a script (SMO/Powershell/LinkedServers/etc) to roll up all reports from all instances to one central server.
Have that central server email you the report, ordering by instance, and then ordering column.
It's flexible, you can add requirements as needed.
You can also do the final export in SSRS.
Follow me on Twitter: @way0utwestForum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com