Procedure:
Perform Remaining Setup Steps within SQL Server Management Studio
Once the installation
package has been run, we are ready to run the Setup file that can be
found in the same directory into which we placed the reports. We can then open
the primary dashboard report with the new Custom Reports option within SQL
Server Management Studio. We will take these final setup steps in the
sections that follow.
Run
the Provided Setup Script against the Desired Server Instance
We can easily run the
necessary SQL Setup script included with the Performance
Dashboard Reports from our immediate position within the SQL
Server Management Studio.
1.
Select File
-> Open from the main menu within SQL Server Management Studio.
2.
Select File...
from the cascading menu that appears, as shown in Illustration 16.
Illustration 16: Select
Open -> File ...
3.
Navigate to
the Performance Dashboard subdirectory that we created earlier (within
the Custom Reports directory), which we populated with the Performance Dashboard Reports as a part of running the
installation package.
4.
Click the setup.sql
script file, as depicted in
Illustration 17.
Illustration 17: Select
Setup.sql ...
5.
Click Open to place the
script into a newly opening query tab of the Management Studio.
The new tab, containing
(and named after) the setup script, appears as partially shown in
Illustration 18.
Illustration 18: Setup
Script Open in Management Studio Tab (Partial View)
6.
Click the Execute
(!) button in the Management
Studio toolbar,
as depicted in Illustration 19.
Illustration 19: Execute
the Query ...
The query executes, and
the Command(s) completed successfully message appears in the Message
pane, underneath the Query pane, as shown in Illustration 20.
Illustration 20: Successful
Completion of Query is Indicated ...
The script
has set up a new schema, called MS_PerfDashboard, in the MSDB
database, together with a set of functions and procedures referenced
by the Performance
Dashboard Reports. It is important to remember
that the Performance
Dashboard Reports
rely solely upon information
made available in the SQL Server 2005 dynamic management views (DMVs),
and that no databases / tables are created (as is the case with standard Reporting
Services reports), or used to store trending or other data for use by the
reports.
Open and
Examine the Primary Dashboard Report with the Custom Reports Functionality
All that
remains to complete setup is to open our primary dashboard report using the new
Custom Reports functionality that is introduced, as we have seen, within
Service Pack 2.
1.
Right-click the appropriate
server instance within the Object Browser.
2.
Select Reports ... from
the context menu that appears.
3.
Select Custom Reports ... from
the cascading menu that appears next, as depicted in Illustration 21.
Illustration 21: Select Reports
-> Custom Reports ...
4.
Navigate to the Performance
Dashboard subdirectory we created earlier, and select the performance_dashboard_main.rdl
file contained therein, within the Open File dialog, as shown in Illustration 22.
Illustration 22: Select
the Primary Dashboard Report File ...
5.
Click Open to open the
file within Custom Reports.
As we shall
discuss in Part II of this article,
the first time we choose the Custom Report option, we are prompted to
choose the report to run, as we see here. Once we have browsed to the
share housing the primary dashboard report file, and loaded it into the viewer,
the dashboard can be accessed prospectively simply by using the recent file
list, instead of manually browsing to the share.
6.
Click Run on the Run
Custom Report dialog that appears next, as depicted in Illustration 23.
Illustration 23: Click
Run to Execute the Report ...
The
message Retrieving Data briefly appears, and then the dashboard opens,
presenting status information for this point in time (for purposes of this
exercise, I am performing a full process of the Adventure Works Analysis
Services database, as well as a simultaneous full backup of a copy of the Adventure
Works database), as shown in Illustration 24.
Illustration 24: The
Performance Dashboard in Action ...
At this
point, we can drill through to various supporting reports, via the hyperlinks
that appear on the dashboard. We will explore the set of supporting reports in
the subsequent article in this series. In the meantime, if you wish to test
drillthrough and other features, this is a great time to become familiar with
all the parts and pieces.
7.
Perform
drillthrough to underlying support reports, as desired, by clicking the
respective hyperlinks in the Performance Dashboard.
8.
Select File
-> Exit, when ready, to leave SQL Server Management Studio, as depicted in Illustration 25.
Illustration 25: Select
the Primary Dashboard Report File ...
Having
loaded the primary dashboard report as a Custom Report within Management
Studio, we can easily open and refresh it (and, of course, access all
supporting drillthrough reports from it) anytime we enter Management Studio.
In our next article we will examine the ease with which gaining such access is
possible, as well as review the purposes and uses of the supporting report files
that ship with the Performance Dashboard Reports, together with
other topics related to this useful new administrative tool.
Conclusion ...
In this article, we introduced the Performance Dashboard for MSSQL Server, a new, useful administrative report pack
offered for download by Microsoft just after the release of MSSQL Server 2005 Service Pack 2. After
discussing some of the details of the contents within, and valuable uses for,
this new reporting option, we prepared for installation by downloading and
running the installation
file (.msi) available from Microsoft. As an additional part of
preparation, we ascertained that system requirements were met for installing
the Performance Dashboard Reports in our local environments.
We next performed
remaining setup steps within SQL Server Management Studio, including
taking the steps to load the primary dashboard report within the new Management Studio
Custom Reports functionality. We examined the face of the dashboard within
SQL Server Management Studio. Finally, we looked ahead to our examination of the individual,
supporting drillthrough reports in Part II of this article.
»
See All Articles by Columnist William E. Pearson, III
Discuss this article in the MSSQL Server 2000 Reporting Services Forum.