Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Health and History Tool


By:   |   Read Comments   |   Related Tips: More > DBA Best Practices

Attend these FREE MSSQLTips webcasts >> click to register


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

With just about any other Microsoft product

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

 
SQLH2Repository Database Objects


 

SQLH2 Configuration Utility


 

SQLH2_Collector Windows Task Scheduler Job


 

Next Steps



Last Update:


signup button

next tip button



About the author
MSSQLTips author Jeremy Kadlec Since 2002, Jeremy Kadlec has delivered value to the global SQL Server community as an Edgewood Solutions SQL Server Consultant, MSSQLTips.com co-founder and Baltimore SSUG co-leader.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools