SQL Server Health and History Tool

By: Jeremy Kadlec   |   Updated: 2008-04-17   |   Comments   |   Related: More > DBA Best Practices

Problem

I have heard about the SQL Server Health and History tool, but I have never used it.  How long has this tool been around for?  In your opinion is this a tool worth using?  What sorts of functionality does the tool have to offer?  What types of limitations does the tool have?  Is this tool primarily intended for DBAs, Developers or Network Administrators?  Where can I download a copy of the tool and what do I need to do to install it?

Solution

In terms of history, the SQL Server Health and History tool has been available to the public for the last 2 and a half years as of the writing of this tip.  Patches have also been released for this tool based on feedback from the community. 

In terms of usage, I can see value in this tool from a DBA, Developer and Network Admin perspective.  We will cover some of those items in this tip, but will also do so in some upcoming tips related to performance monitoring.  In addition, I can see benefit from a managerial perspective based on the reporting features, which will be covered in an upcoming tip as well.

In this tip let's focus on the following items:

• Data available from the tool
• General architecture
• Installation instructions

SQL Server Health and History Data

During the installation process a SQL Server database is created to support the data collection and reporting process.  By a quick examination of the database, the following sets of data are captured:

• Hardware configurations
• Windows configurations
• All SQL Server instances on the machine
• SQL Server configurations
• SQL Server performance metrics
• Doctor Watson errors
• Event log entries

Based on this information, I see value in capturing snapshots of information for the SQL Server instance.  When you include capturing the performance metrics, reviewing this data on a more frequent basis can offer value not only to the DBA team, but also to the Development team to determine any performance issues that need to be addressed.  Being able to correlate this information back to the T-SQL code can help to identify code that requires attention.

In terms of limitations, the tool does not seem to be a real time data collection and reporting tool.  So if real time data is one of your requirements you made need to change your requirements or look for another tool.  With some of the data (hardware, Windows and SQL Server configurations) real time requirements do not seem very interesting, but with other pieces of data such as Event log errors, spiked resource utilization, low response time, etc they could offer additional insight if the data was delivered in a real time manner.

Product Architecture

The SQL Server Health and History tool consists of the following components:

• Application files typically stored in the C:\SQLH2\ directory
• SQL Server database to store the historical data which is typically the SQLH2Repository
• Data collection via a Windows Task Scheduler Job typically called SQLH2_Collector
• Data collection logging in the C:\SQLH2\H2log.txt file
• Performance metrics
• Reporting

Installation Instructions

SQL Server Health and History Tool Installation Instructions

Welcome Screen

License Agreement

Configuration Utility - By selecting this checkbox the second portion of the installation will run automatically, which simplifies the installation process.

Installation Directory Warning - Be sure to keep the default installation directory

Installation Folder

Confirm Installation

Installation Progress

Installation Complete

SQLH2 Installation & Configuration Wizard

Repository Database Installation Configuration

Share Data with Microsoft

Installation Progress

Collection Computers

Windows Job Scheduler Configurations - Add the credentials before pressing the 'Next' button

Installation Complete

SQLH2 Repository Database Objects

SQLH2 Configuration Utility

SQLH2 Collector Windows Task Scheduler Job

Next Steps

• This tip has just touched the surface of the Health and History tool.  Stay tuned for two more tips on the SQL Server Health and History tool related to capturing performance metrics and reporting.
• If you are in need of a tool to perform monitoring and analysis, then consider this tool as a potential resource as you survey the marketplace, review evaluations and select a final product.  Also check out the Performance Monitoring and Performance Tuning tools on the MSSQLTips.com resource portion of the site.
• To download the SQL Server Health and History tool visit the Microsoft Download Center.
• For additional free tools from Microsoft visit these resources:
• Free SQL Server tools from Microsoft
• Free SQL Server Troubleshooting Tool from Microsoft
• Free Job Scheduling Tool for SQL Server Express and MSDE
• Web Based Database Administration for SQL Server

About the author

Jeremy Kadlec is a Co-Founder, Editor and Author at MSSQLTips.com with more than 300 contributions. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP. Jeremy brings 20+ years of SQL Server DBA and Developer experience to the community after earning a bachelor's degree from SSU and master's from UMBC.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips