I am manning the ask the experts SQL server booth at TechEd this week. One of the many interesting questions I was asked was how the reporting information from the standard reports in SQL server could be integrated into one set of data from multiple servers.
I couldn’t think of anything immediately to help him, so I am posting my thoughts here.
The reports are using Reporting Services from a SQL source, but I couldn’t find out what this was without running profiler – so that’s what I had to do. I setup a new trace to watch for RPC complete events with a column filter on application name set to "Microsoft SQL Server Management Server". I then ran the Activity – All Session report:
and then stopped the trace and looked at the relevant event:
as you can see the SQL looks long but it’s mainly columns, there are only four tables involved:
Of course in some of the other reports there are multiple charts and data regions so the underlying query will be much more complex, but this can be easily broken down and will only hit four or five table for each sub-query.
The next step is how to combine and extract this. Personally I would use Integration Services to run this query for each server with the query as the source and put this out to one table. On the way throw you would tag every row with the server it came in a new column. the final step would ne to fire this out to excel and schedule it to run as required.
Another approach would be to do the who;e thing in one stored procedure which would link to each server in turn and would run on demand.