By: Jeremy Kadlec | 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
