Content area
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. The second process needs Io collect the actual data and store it in the collection table. adding in the server name, database name, and collection dale. [...] you need to insert a row into lhe DBA_Reports table and enter the report's name. description, filename, and report type in the appropriate columns.
Run three types of reports from one GUI
Over lime I've accumulated several performance monitoring tools. Two of my favorites were Microsoft's SQL Server 2005 Performance Dashboard Reports and Rodney Lundrum's DBA repository, which was introduced in SQL Server Magazine. (See the Resources box.) These solutions basically gave me a way to view live performance dala as well as create snapshots of il. However. I wanted to expand on these solutions. 1 needed a tool that would also
* Create reports thai show historical trends
* Easily connect to any SQL Server instance and display its dashboard repon
* 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 creale a consolidated monitoring tool. This tool uses a SQL Server Integration Services (SSlS) package to collect performance data from SQL Server instances, then stores thai dala 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 too).
3. Populate the ServerLisi_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 1 39799.zip file, which contains the scripts, SSIS package, SSRS package, and other documentation for (he consolidated monitoring tool. To download it. go to www .sqlmag.com. enter 139799 in the Search box, and click the 1 39799.zip hotlink.
Step I : Install the Dashboard Reports
The first step is to install the dashboard reports. You can download the installation file (SQLScrver2005_ PcrformanceDashboard.msi) from www.microsoft .com/dow'nloads/details.aspx?familyid=ld3a4aud7e()c-4730-8204-e4 1 92 1 8c I efc. You must install the dashboard reports on all lhe 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 Dash Board 2005. syl or Fix Dash Board 2(M)8..scj scrip!. The origina) code in the MS_PerfDashboard.usp_Main_Get Sessionlnfo procedure contains a bug. Specifically, the code in Listing I needs to be replaced with the code in Listing 2. On SQL Server 2005 instances, you need to run Fix DashBoanl2lHi5.mil to fix the bug. On SQL Server 2008 R2 and SQL Server 2008 instances, you need io run Fix Dadi Board 2008. sql. Running Fix Dash BoardBound 2028.sql will also install all the needed procedures and functions in the msdb database that don't get installed when you run lhc SQ LScrver2005_Per for miincc Dashboard .msi lile. (For more information about using the consolidated monitoring tool with SQL Server 2008, see the web-exclusive sidcbar "SQL Server 2008 Considerations When Using the Consolidated Monitoring Tool," www.sqlmag.com. InstaiuDcic ID 140688.1
* Replace the performance_dashboard_main .rdl file in the C:\Program Files\Microsoft SQL Server\90\Tools\PcrformanceDash board !older with the per forma ncc_dashboard_jna in .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. Sonic of the new tables store data collected from several new performance counters. Others serve as an archive for collected data. I aiso added an administrative table Io help keep track of the reports in the SSRS package- Table 1 shows all the tables in (he 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_ Refi DB.sql script. TIi is scripts creates noi 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 "(February 2008. InstantDoc ID 97840), you just need to update the DBA_Rep database. Running the Modify f)BA_ Rep D B. St]I 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 Resources box), you need to first update the database using tlie code in the "Use SSRS and SSIS to Creale a DBA Repository" article, (hen run Modify DBA_Rrp DH.sql.
Step 3: Populate the ServerList_SSIS Table
The DBA_Rep database includes the table ServerLisl_SSIS. This table needs to contain the names of your SQL Server instances. To populate this table, you can execute the Pitpitiate Server Nurni's.sifl script. Note thai this script uses the \p_cmdshcll stored procedure to execute the Sqlcmd utility, so you need Io have .\p_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 Lised to designate the location of the DBA_Rep database. Sn the table, find the server on which the DBA_Rep database resides and change the Holds_DBA_Rcp column's value from O to 1 . For example, if the database is on ServerO()4, the Holds_DBA_Rep column would look like that in Table 2. Alternatively, you can run code such us
Step 4: Run the SSIS Package
The consolidated monitoring tool usen the DBA repository solution's SSIS package, DBA_Rep SSIS. However. I made two sets of changes Io 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 ?? capture the new data. The second sci 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 139799.zip 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_Servcr_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 fo upgrade the package, keeping all of the default sellings. You might gel some warnings concerning insert commit size, the connection manager being changed from SQLNCLI. I to SQLNCLIK), 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 lhe "Introducing Business Intelligence Development Studio" web page msdn.microsoft.com/en-us/ libra ry/ms!73767.aspxj.
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 (heal) values lo the instance containing the DBA_Rep database.
Here again I would like to reiterate (hat the dashboard reports must be installed on all the SQL Server instances that you want to monitor (i.e.. all (he instances listed in the Server List_SSi S table). Otherwise, you might run into errors loading the SSIS package due to missing objects.
One benefit of this SSIS package is thai ali of the collection processes arc 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 creale a single GUI from which you can access all the reports.
To use this SSRS package, copy the DBA Rep Dashboard Reports solution folder Io the same location as the SSIS solution folder. In BIDS, open lhe solution file named DBA Rep Dashboard Repon&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 Jf so lhal its connection string points to the SQL Server instance thai 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 lhat they would handle large databases. (Large databases caused some of the original dashboard reports to constantly fail with un arithmetic overflow.) Note that you can't access the Live Dashboard reports for SQL Server 2(K)O 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 al 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,/ip 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." "Database Name," and "Collection_Date" columns in it. You also need to create an archive version of the table.
In the SSlS 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 Io collect the actual data and store it in the collection table. adding in the server name, database name, and collection dale. The third process needs to copy the dala in lhe collection lable Io 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 lhe DBA_Reports table and enter the report's name. description, filename, and report type in the appropriate columns. Don't include lhe .rdl extension in the report's filename. For the report lypc, you need to enter either L for latest data or A for archived data.
Build a Tool of Your Own
The consolidaled 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 lo the consolidated monitoring tool, such as adding enhanced reports from SQL Server 2008. I hope that I've inspired you Io start working on your own expanded solulion.
InstantDoc ID 139799
Jeff Carrington
([email protected]) is a senior DBA with comScore, a market research company located in Reston, Virginia. He has been a DBA since 1986 and has worked with SQL server since 1999
Copyright Penton Business Media, Inc. and Penton Media, Inc. Dec 2011