Over time I've accumulated several performance monitoring tools. Two of my favorites were Microsoft's SQL Server 2005 Performance Dashboard Reports and
Rodney Landrum's DBA repository, which was introduced in SQL Server Magazine. (See the Learning Path box at the end of this article.) These solutions basically gave me a
way to view live performance data as well as create snapshots of it. However, I wanted to expand on these solutions. I needed a tool that would also
- Create reports that show historical trends
- Easily connect to any SQL Server instance and display its dashboard report
-
Run all the dashboard reports and the DBA repository reports from one interface
To achieve these goals, I combined and modified the DBA repository solution and dashboard reports to create a consolidated monitoring tool. This tool
uses a SQL Server Integration Services (SSIS) package to collect performance data from SQL Server instances, then stores that data in a database. A SQL
Server Reporting Services (SSRS) package consolidates the dashboard reports, DBA repository reports, and several new reports and provides a single
interface from which to run them.
To use the consolidated monitoring tool, follow these steps:
-
1. Install the dashboard reports.
-
2. Create the database for the consolidated monitoring tool.
-
3. Populate the ServerList_SSIS table.
-
4. Run the SSIS package to populate the database.
-
5. Run the SSRS package.
-
6. Customize the tool with your own reports. (This step is optional.)
I'll cover each of these steps in detail. To follow along, you can download the 139799.zip file, which contains the scripts, SSIS package, SSRS
package, and other documentation for the consolidated monitoring tool. To download it, click
the 139799.zip hotlink at the top of this page.
Step 1: Install the Dashboard Reports
The first step is to install the dashboard reports. Download the installation file (SQLServer2005_PerformanceDashboard.msi). You must install the dashboard reports on
all the SQL Server instances you want to monitor. Otherwise, you might run into validation errors when you try to load the SSIS package or execution
errors when you try to run the package.
After you perform the installation, you need to do the following:
- Run either the Fix DashBoard 2005.sql or Fix DashBoard 2008.sql script. The original code in the
MS_PerfDashboard.usp_Main_GetSessionInfo procedure contains a bug. Specifically, the code in Listing 1 needs to be replaced with the code in
Listing 2. On SQL Server 2005 instances, you need to run Fix DashBoard 2005.sql to fix the bug. On SQL Server 2008 R2 and SQL
Server 2008 instances, you need to run Fix DashBoard 2008.sql. Running Fix DashBoard 2008.sql will also install all the needed
procedures and functions in the msdb database that don't get installed when you run the SQLServer2005_PerformanceDashboard.msi file. (For more
information about using the consolidated monitoring tool with SQL Server 2008, see the sidebar "SQL Server 2008 Considerations When Using the
Consolidated Monitoring Tool.")
- Replace the performance_dashboard_main.rdl file in the C:\Program Files\Microsoft SQL Server\90\Tools\PerformanceDashboard folder with the
performance_dashboard_main.rdl file in 139799.zip.
Step 2: Create the Database
The consolidated monitoring tool uses the DBA repository solution's database, DBA_Rep. However, I modified the definitions of nine of its tables and
added several new tables. Some of the new tables store data collected from several new performance counters. Others serve as an archive for collected
data. I also added an administrative table to help keep track of the reports in the SSRS package. Table 1 shows all the tables in the database and
designates their type (e.g., administrative table, archive table).
How you create the database and tables depends on whether you installed the original DBA repository solution. If you haven't installed the original DBA
repository solution, you need to create the DBA_Rep database by running the Create DBA_Rep DB.sql script. This scripts creates not only the
database but also all the necessary tables and other objects. This script uses a basic CREATE DATABASE statement, so the database will be patterned
after your model database.
If you installed the DBA repository solution discussed in Landrum's article "Use SSRS and SSIS to Create a DBA Repository," you just need to update the DBA_Rep database. Running the Modify DBA_Rep DB.sql script will modify the existing tables and create
the new tables. If you installed the DBA repository solution from Landrum's earlier articles (see the Learning Path box), you need to first update the
database using the code in the "Use SSRS and SSIS to Create a DBA Repository" article, then run Modify DBA_Rep DB.sql.