Database Server uptime

Job details

Parameters

Job Summary

• Purpose: The purpose of this job is to monitor the uptime statistics of a database server, particularly targeting Microsoft SQL Server instances with the 2000 version.
• Why: This job is critical for ensuring high availability and reliability of the SQL Server 2000 instances. The uptime statistics help identify the operational status and any potential downtimes which might affect the business processes relying on the database.
• Manual checking: To manually check the uptime information, the following SQL commands can be issued:

SELECT instance_name, startup_time, last_check FROM dbw_db_uptime;

Job Description and Features

• Name: Database Server uptime
• Description: Collects database server uptime statistics.
• Version: 1.9
• Dependency Information:
  • This job is dependent on several stored procedures, views, and tables that help in gathering and maintaining uptime data, such as dbw_db_uptime, dbw_db_uptime_histr, dbv_db_uptime_histr (view), db_cluster_uptime (procedure), etc.
• Scheduled Frequent Checks: The job is configured to run very frequently (every minute), as specified by the setting “* * * *”, which is the crontab format for “run every minute of every hour of every day of every month”.

Implementation Details

• Database Modifications:
  • Various tables such as dbw_db_uptime and dbw_db_week_uptime are created to store current uptime and weekly uptime statistics respectively.
  • Stored procedures like db_uptime are implemented to handle complex data processing tasks required for calculating and updating uptime data.
• Procedural Logic:
  • Procedures like db_cluster_uptime handle clustering related time periods, while db_reset_week_uptime recalculates weekly uptime figures. They perform tasks such as setting environmental variables, running loops for data calculation, and inserting or updating records in the created tables.

Reporting and Data Presentation

• A comprehensive dbWatch report template is included to format and present the uptime data:
  • Shows live and historical uptime data.
  • Interactive tables and statistics such as “Server Name”, “Instance Name”, “Startup Time”, “Last dbWatch Connection”, and calculated periods of activity.
  • Specific sections for maintenance and downtime analysis are also included.

Dependencies and Cleanup

• Dependencies:
  • The job relies heavily on the consistent function of the database objects like tables and procedures it interacts with.
  • It ensures that on a failure, necessary cleanup actions are performed to maintain data integrity.
• Cleanup Actions:
  • Includes cleanup strategies for tables and procedures upon job failure to prevent stale or corrupted data from affecting subsequent operations.

Upgrade Path

• Upgrade Notes:
  • From version 1.7 to 1.8, several structural and procedural enhancements are made to accommodate new features or improvements.
  • Procedural scripts for handling large datasets, error correction in data capture, and enhancements for maintenance period calculations are incorporated.

This job not only focuses on the critical aspect of monitoring uptime but also ensures that the operational data is cleanly maintained and accurately reported for effective decision-making.