With multiple concurrent projects and many everyday tasks, how can the DBA effectively monitor all of the servers in his or her care without spending money on expensive tools or lots of time developing a custom solution? Luckily, Microsoft released SQLH2 last year, an easy to use tool that will do some of the work for free.

What will SQLH2 do for you?

The SQLH2 utility records snapshots of one or more servers into a repository database. It does not provide real time monitoring, but it does provide a history of changes made to your servers. From hotfixes applied to the operating system to database growth to SQL Server settings, there is a wealth of information recorded. Even more data is collected from SQL 2005, things like triggers, assemblies and backups. Thirteen ready-made SQL Reporting Services reports are available to view the information.

Figure 1: A section of the "SQL Server Database Details" report

Another nice thing about it is that you do not install anything on the target servers. The data is pulled to the collector. The documentation states that the performance impact on the server collecting the data is very minimal,

The real beauty of this tool is a separate add-in called the SQLH2 Performance Collector. With the Performance Collector add-in you can easily record any performance counter into the SQLH2 repository database. Sure, you now can set up Perfmon logs as CSV files and import them into a database without spending any money, but it is a lot of work. This easy to set up tool will do the work for you!

What it doesn't do

SQLH2 does not have any real time monitoring capabilities. It doesn't send alerts. It will not work on clustered servers. This tool only works on SQL 2000 and SQL 2005, not SQL 7 or any other previous releases.

Download

Installing SQLH2 is a snap, and it can be installed on one server or even on a workstation. A repository database will be created on a SQL Server, and you'll need Reporting Services to view the reports. The server or workstation where the tool is installed requires bcp.exe, one of the SQL Client tools.

If the SQL Servers you intend to monitor have different start up accounts, you will have to create one domain account to run SQLH2. Give the account admin rights on the servers and each SQL instance. If you don't like this idea, you may have to install SQLH2 on each individual server.

Download three files, one for the base collector, one for the performance collector, and one for the reports from Microsoft's website. I'm not going to give the links here, just search for SQLH2 on the Microsoft site.

Installation Part 1: H2Setup.msi

Begin by installing the base collector from H2Setup.msi. By default, the tool runs the configuration wizard right after installation. I recommend that you read the "SQLH2 V2 Deployment Guide" before configuring. You will find this document in the SQLH2 folder after installation. This document contains important information about configuring the tool as well as a list of the monitored items. If you are installing on a Windows 2003 Server, the wizard will complete successfully. The scheduling step of the wizard is not compatible with Windows 2000, you will have to manually schedule the jobs using either the Scheduled Tasks applet found in Control Panel or as a SQL Server Agent job. Another important thing to keep in mind during installation is that no spaces can be included in the installation folder, "c:\sqlh2\" by default.

Whether you used the configuration wizard or not at installation, you can modify the settings by using the SQLH2 Configuration Utility now found in the start menu.

Figure 2: The SQLH2 Configuration Utility's available menus

Install the repository database where the collected data will be stored by clicking Repository in the menu. You can manually create the database first if you wish, but it must not have any objects in it when you click Install to create the objects

Figure 3: Configure the repository database.

Click Config File-->Edit to modify the servers about which you will collect information

Figure 4: Add target servers.

It is very important that the account running the SQLH2 job have administrative privileges on each server and sysadmin rights in each SQL Server instance.

By default, SQLH2 collects data at 3:00 am on Mondays and Thursdays. More about scheduling discussed later.

Installation Part 2: H2PerfSetup.msi

Now we get to the cool stuff, the Performance Collector. Download and run the H2PerfSetup.msi file. Use the same account to run this as you did for the SQLH2 utility. You can install the Performance Collector on the same or another server. See the "SQLH2 V2 Perf Collector Deployment Guide" for more information on how to install it on a separate server. The Performance Collector runs as a service and does not have a user interface for configuration. You will find, instead, a file for configuration in the install folder called "H2PerfConfigFile.xml" found by default at "c:\SQLH2\C:\SQLH2\PerfCollector\".

The file will be initially set up to record 27 commonly used performance counters on the local server. You can copy and paste the <Target name="."> section, replacing the period, meaning local server, with another server name. You can also add any additional counters you are interested in collecting. You can collect both base and performance data on a particular server or one and not the other.

There are four other settings that you can modify: "VerboseLogging" which I set to false so that it doesn't record so much information in Event Viewer, "CollectionIntervalSeconds" how often the data is collected, "RetryIntervalSeconds" the initial amount of time (the time is doubled after each attempt) it will wait to try again after failing to connect to a server, and "MaxFileSizeMB" which is the size of the data file before it is closed and another one created.

The data is collected in a sub folder called "Data" in the PerCollector folder. As the current file reaches the "MaxFileSizeMB" or the service is stopped and restarted, a new file will be created. When the base collector runs, it records the performance data into the repository from any closed files and deletes the processed files.

After modifying the configuration file, you must stop and start the SQLH2 Performance Collector service. The service will initially be stopped after installation

Go back and run the SQLH2 Configuration Utility and fill in the server name where Performance Collector service is running in the "PerfProviders" section. Make sure that you do not list all of the servers you are collecting information about in this section. If you are using any separate servers as collectors, you must set up a share on the collection servers' performance data folder so that the base collector will be able to collect the data. The share must be named "\\<servername>\SQLH2PerfCollectorData$\" and the account running the base collector must have read and modify permission.

Installation Part 3: ReportV2.cab

The third piece of the puzzle is the reports. Download and extract "ReportV2.cab". The reports were written to run in SQL Reporting Services, so I hope you are already using Reporting Services in your environment. Installing Reporting Services is beyond the scope of this article. Create a new folder, create a data source pointing to the Repository database and upload each report. I prefer to create the data source in the same folder as the reports, but the reports were expecting the data source to be at the root. I just had to repoint each report to use the data source in the SQLH2 folder. There is also a document called "SQLH2 V2 Reports" found in the cab file along with the reports. It contains information about installing the reports and notes about some of the reports. Some of the reports will only display data about SQL 2005 servers.

Figure 5: Reporting Services

You can also create your own reports. Download the data dictionary for the repository from the SQLH2 team.

Scheduling the base collector

Microsoft recommends that the base collector be scheduled to run twice each week. Create a scheduled task in Control Panel or SQL. Here is the syntax:

SQLH2.exe /C<filename> /D<dateformat>

The D switch is required if you have localized servers. Be sure to read Appendix B in the documentation for more information

The C switch is useful if you would like to collect the performance data more frequently than the base information. Follow these instructions to collect base information twice weekly and performance counters daily.

• Create a copy of "H2Config.xml" and name it "H2ConfigPerf.xml".
• In "H2Config.xml" list the target servers where base information will be collected and remove the performance collectors.
• In the "H2ConfigPerf.xml", list only the performance collectors and not the targets.
• Create a batch file called SQLPerfCollection.bat in the SQLH2 folder with the following:

  NET STOP SQLH2PerfCollector

  NET START SQLH2PerfCollector

  C:\SQLH2\SQLH2.exe /CC:\SQLH2\H2ConfigPerf.xml

• Schedule the jobs according to the following information in Scheduled Tasks. Make sure that the account running the jobs has permission on the target servers and the performance collection shares.

Conclusion

As you can see SQLH2 is easy to set up and use. And the best part is that it is free! I hope that you will take a look at using SQLH2 or have learned something new about it from this article. I would like to thank Dan from the St. Louis SQL Server Users Group for researching how to create a separate schedule for monitoring performance and asking Microsoft for the data dictionary.