SQL 2005 Performance Dashboard on the Web
One of the most exciting things to come out of SP2 is the availability of the performance dashboard. This allows a one page overview of you SQL 2005 server and raises SQL 2005 trouble shooting from black art involving complex dynamic management views and pouring over perfmon to point and click. It even includes a CHM detailing how to use it in conjunction with a tuning methodology - nice ;-)
The first thing I wanted to do was present this dashbaord on the web rather than through sql server management studio.
Steps to Publish SQL Performance Dashboard to reporting services web site:
a) install dashboard from http://www.microsoft.com/downloads/details.aspx?FamilyId=1D3A4A0D-7E0C-4730-8204-E419218C1EFC&displaylang=en.
b) Start a new "Report Server Project" in BI Studio or Visual Studio 2005.
c) Add a new Shared Data Source with Default Name "DataSource1" which connects to your msdb database. Ideally use a service account for credentials.
d) Drag Report Files (*.rdl) from "C:\Program Files[ (x86)]\Microsoft SQL Server\90\Tools\PerformanceDashboard" to the Reports Section of your project.
e) Right Click on your Project and set the Target URL to http://localhost/reportserver or your production report server
f) Right Click and select deploy.
To tidy it up you can go into the Report Portal http://localhost/Reports and hide every report except the first one "performance_dashboard_main" as these are all child reports called from the main dashboard.