Hosting the Performance Dashboard Reports in SSRS
I blogged a while back about modifying the SQL Server 2005 Performance Dashboard reports to run on SQL Server 2008. I’ve since been working with several customers who use these reports for performance troubleshooting, but who would like to host them on their Reporting Services platform so they can be viewed online instead of within SQL Server Management Studio. So over the past few days I’ve been killing time on flights and trains doing just that. So here goes…
It’s actually very simple to get the reports up and running on Reporting Services. All you have to do is install the Performance Dashboard reports, create a new Report Server project in BIDS (or Visual Studio) and import the .rdl files from the directory they were installed to (right-click the solution name in Solution Explorer –> Add –> Existing Item).
They use a shared data source, so you need to update this to point to the SQL Server instance you want to monitor (making sure that you have enabled the instance for use with the Performance Dashboard reports first), deploy the reports and data source to your Report Server and you’re ready to roll.
However, this means that you can only look at one server. To monitor multiple servers, you would would need to repeat the process and host a separate copy of the reports for each one… not very scalable !
The solution I’ve come up with (and which you can download in the attached .zip file) requires you to publish the reports only once and use a parameter to dynamically determine which server we want to connect to in our data source. This parameter is set in the Performance_Dashboard_Main.rdl file when you first launch the report and is used as an expression in the report data source to dynamically build the connection string.
We need to modify the existing data source to be an embedded data source as using expressions in connection strings is not supported with shared data sources.
This parameter is then passed through to subsequent linked reports to build the dynamic data source connection for those reports as well.
I’ve created 2 versions of the Performance_Dashboard_Main report (Performance_Dashboard_Main.rdl and Performance_Dashboard_Main_CMS.rdl) which I’ve included in the attached solution file. The first one uses a free-text field to enter the server name and the second one uses the new Central Management Server (CMS) functionality in SQL Server 2008 to dynamically populate a drop-down list of servers you have already registered on your CMS… very cool !
These reports have been designed and tested to work on SSRS 2008, however you can use the same technique to host them on SSRS 2005 or 2008. The data source for the reports can also either point to SQL Server 2005 or 2008 instances (as long as you’ve followed the instructions for modifying the reports for SQL Server 2008 first). As I’ve mentioned before, these reports are not a replacement for the fantastic new Management Data Warehouse functionality of SQL Server 2008, but can provide another valuable tool to help DBA’s analyse performance issues.
Download the reports, have a play and let me know what you think !