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

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:

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:

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_RepGOUPDATE ServerList_SSISSET Holds_DBA_Rep = 1WHERE [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.)

Figure 1: Copying the captured data into the archive tables

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:

Figure 2: Running the reports from the consolidated monitoring tool’s GUI
 

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.

Figure 3: Integrating all the reports into a cohesive unit

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.