SQL Server Health and History Tool
ProblemI 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
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
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
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 |
 |
- 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:
No comments:
Post a Comment