Introducing the SQL Server Consolidated Monitoring Tool

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 "Download the Code" 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.
• 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.

SUM(CONVERT(bigint, datediff(ms, login_time, GETDATE())))
  - SUM(CONVERT(bigint, s.total_elapsed_time))
  AS idle_connection_time,

SUM(CONVERT(bigint, CAST( DATEDIFF( minute, login_time, GETDATE())
  AS BIGINT)*60000 + DATEDIFF(millisecond, DATEADD(minute,
  DATEDIFF (minute, login_time, GETDATE()), login_time), GETDATE())))
  - SUM(CONVERT(bigint, s.total_elapsed_time)) AS idle_connection_time,

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.

Step 3: Populate the ServerList_SSIS Table

The DBA_Rep database includes the ServerList_SSIS table. This table needs to contain the names of your SQL Server instances. To populate this table, you can execute the Populate Server Names.sql script. Note that this script uses the xp_cmdshell stored procedure to execute the Sqlcmd utility, so you need to have xp_cmdshell enabled on your instance. Alternatively, you can manually enter the names. Be sure to enter each name in a separate row.

After the ServerList_SSIS table is populated, you need to update its Holds_DBA_Rep column, which is used to designate the location of the DBA_Rep database. In the table, find the server on which the DBA_Rep database resides and change the Holds_DBA_Rep column's value from 0 to 1. For example, if the database is on Server004, the Holds_DBA_Rep column would look like that in Table 2. Alternatively, you can run code such as

USE DBA_Rep
GO
UPDATE ServerList_SSIS
SET Holds_DBA_Rep = 1
WHERE [Server] = 'Server004'

Step 4: Run the SSIS Package

The consolidated monitoring tool uses the DBA repository solution's SSIS package, DBA_Rep SSIS. However, I made two sets of changes to that SSIS package. The first set consists of the changes associated with adding new collection objects to facilitate the capture and storage of data in the new collection tables. These changes mainly included adding new Execute SQL Tasks to truncate the old data and new Data Flow tasks to capture the new data. The second set consists of the changes associated with adding new objects to facilitate the archiving of collected data. Several Data Flow tasks were inserted to copy the captured data to the archive tables and add a timestamp, as Figure 1 shows. (The SSIS Design.jpg file in zip file at the top of the page shows the SSIS package's design in its entirety.)

To use the DBA_Rep SSIS package, copy the DBA_Rep SSIS solution folder to the machine on which you performed the previous steps. In Business Intelligence Development Studio (BIDS), open the DBA_Server_Load.sln file.

If you're using the version of BIDS in SQL Server 2008, you'll be prompted to upgrade the package after the file opens. Use the conversion wizard to upgrade the package, keeping all of the default settings. You might get some warnings concerning insert commit size, the connection manager being changed from SQLNCLI.1 to SQLNCLI10, and the package format changing from version 2 to 3. These warnings are expected and can be ignored. If you're using the version of BIDS in Visual Studio 2010, you'll also be prompted to upgrade the package. If you do so, the upgrade will "succeed," but you'll soon find out that the project couldn't be loaded because Visual Studio 2010 doesn't support SSIS solutions. For a workaround to this problem, see the note on the "Introducing Business Intelligence Development Studio" web page.

Before you run the SSIS package, you might need to modify the Connection Manager settings. The servers and two package variables (SRV_Conn and SRV_Conn_9) have been set to (local). So, if the DBA_Rep database is on the local default instance, you can just leave everything as is. Otherwise, you need to change the (local) values to the instance containing the DBA_Rep database.

Here again I would like to reiterate that the dashboard reports must be installed on all the SQL Server instances that you want to monitor (i.e., all the instances listed in the ServerList_SSIS table). Otherwise, you might run into errors loading the SSIS package due to missing objects.

One benefit of this SSIS package is that all of the collection processes are contained in one location. This makes it much easier to maintain a scheduled update of your performance counters, as recommended by all performance tuning documents. You can either set up a job to run the SSIS package or manually run it.

Step 5: Run the SSRS Package

The consolidated monitoring tool uses the DBA repository solution's SSRS package, DBA Rep Dashboard Reports. Modifying the SSRS package required the most work because I had to modify some existing reports, create new reports, and create a single GUI from which you can access all the reports.

To use this SSRS package, copy the DBA Rep Dashboard Reports solution folder to the same location as the SSIS solution folder. In BIDS, open the solution file named DBA Rep Dashboard Reports.sln. If you're using SQL Server 2008, the package will need to be converted first. Then, in Solution Explorer, open the shared data source named DBA_Rep.rds. Modify it so that its connection string points to the SQL Server instance that contains the DBA_Rep database.

You can run or deploy the SSRS package from BIDS. Figure 2 shows the main screen in the GUI. As you can see, you can run three types of reports:

• Live Dashboard reports. You can access the live dashboard report for any SQL Server instance by clicking the instance's name in the main screen's server name column. The Live Dashboard reports are a modified version of Microsoft's dashboard reports. Besides modifying the reports so that you can access any SQL Server instance from the same screen, I modified some of them so that they would handle large databases. (Large databases caused some of the original dashboard reports to constantly fail with an arithmetic overflow.) Note that you can't access the Live Dashboard reports for SQL Server 2000 instances because dashboard reports were introduced in SQL Server 2005.
•  Latest Collected Data reports. The Latest Collected Data (report type L) reports pull the latest data collected from performance counters. This group of reports includes reports from the original DBA repository solution as well as some newly created reports. In the reports, you can include data from all SQL Server instances or a select few.
• All Collected Data reports. The last group of reports (report type A) pulls data from the archive tables in the DBA_Rep database. With the All Collected Data reports, you can include data from only one SQL Server instance at a time. However, you can choose which collection dates to include. In addition, a few reports offer an option to display the top number of objects sorted by the chosen category. They also include bar graphs, which can help you see any trends that might be occurring.

Table 3 provides more information about the Latest Collected Data and All Collected Data reports, including a description of each one. Figure 3 shows how the Live Dashboard, Latest Collected Data, and All Collected Data reports are integrated into a cohesive unit. This is only part of the layout. Report Layout.vsd in the 139799.zip file shows the layout in its entirety. There are also .jpg and .pdf versions of this file in case you don't have Microsoft Visio.

Step 6: Customize the Tool

In the event you want to expand this tool by adding you own reports, you need to know a few things. When adding a new collection table to the DBA_Rep database, you need to include the "Server," "DatabaseName," and "Collection_Date" columns in it. You also need to create an archive version of the table.

In the SSIS package, you need to add three processes for the new data. The first process needs to truncate the table that contains the latest collected data. The second process needs to collect the actual data and store it in the collection table, adding in the server name, database name, and collection date. The third process needs to copy the data in the collection table to the archive table. In the SSRS package, you need to place the new report's .rdl file in the same location as the other .rdl files.

Finally, you need to insert a row into the DBA_Reports table and enter the report's name, description, filename, and report type in the appropriate columns. Don't include the .rdl extension in the report's filename. For the report type, you need to enter either L for latest data or A for archived data.

Build a Tool of Your Own

The consolidated monitoring tool builds on the DBA Repository concept of having one location where you can access information from all your SQL Server instances. I expanded the reporting capabilities so that you can report on more than one instance and include more than one reporting period. There's still a lot of functionality that could be added to the consolidated monitoring tool, such as adding enhanced reports from SQL Server 2008. I hope that I've inspired you to start working on your own expanded solution.