Database Server uptime
Job details
| Name: | Database Server uptime | |
| Platform: | Sqlserver | |
| Category: | Availability | |
| Description: | Collects database server uptime statistics. | |
| Long description: | Collects database server uptime statistics. | |
| Version: | 2.5 | |
| Default schedule: | * * * * | |
| Requires engine install: | Yes | |
| Compatibility tag: | .[type=‘instance’ & databasetype=‘sqlserver’]/instance[maj_version > ‘2005′ & hasengine=‘YES’ & (engine_edition = ‘Microsoft SQL Server’ | engine_edition = ‘Azure SQL Managed Instance’)] |
Parameters
| Name | Default value | Description |
|---|---|---|
| week uptime calculation | 24 | Number of hours between each calculation of up-time statistics. |
| register maintenance period | This parameter is used to register a new maintenance period. Parameter value should be in the following format: NAME;PERIOD START;PERIOD END;COMMENT, period start and end in format YYYY-MM-DD HH24:MI:SS. Use “;” as a separator between the fields. Example of parameter setup: Monthly maintenace;2020-05-17 10:30:00;2020-05-17 16:30:00;patching of all database servers | |
| delete maintenance period | This parameter is used to deletes a maintenance period. Use the following format: NAME;PERIOD START;PERIOD END;COMMENT, period start and end in format YYYY-MM-DD HH24:MI:SS. Use “;” as a separator between the fields. Example of parameter setup --> Monthly maintenace;2020-05-17 10:30:00;2020-05-17 16:30:00;patching of all database servers |
Job Summary
- Purpose: This monitoring job is primarily designed to collect and report on database server uptime statistics, enabling a systematic review of server availability and operational periods.
- Why: Maintaining constant tracking of the database server’s uptime is crucial for understanding server reliability, planning maintenance, and improving database availability. Insights derived from uptime statistics help in preemptive maintenance planning and can alert administrators to potential downtimes or disruptions in service.
- Manual checking: You can manually check the uptime statistics in the database by issuing the following SQL command:
select SERVER_NAME, INSTANCE_NAME, STARTUP_TIME, LAST_CHECK from dbw_db_uptime order by STARTUP_TIME desc;Main Task Features
- Monitoring of server uptime and downtime instances.
- Insertion of new records into uptime history tables for reference and audits.
- Regular updates to week and month uptime statistics.
- Register and deletion of maintenance periods through scheduled tasks, reflecting any system downtime due to maintenance.
- Comprehensive reporting via dbWatch Control Center on server availability including current and historical uptime data, down periods, and the impact of maintenance.
Report Structures
- General uptime statistics table, showcasing server name, instance name, the period of startup, and last checked details.
- Weekly and monthly uptime percentages, detailing the exact downtime in seconds alongside visual representations of uptime metrics.
- Maintenance periods, helping to track when the server will undergo scheduled maintenance and the effects on availability.
Dependencies
The main procedure uses a variety of helper functions and procedures to collect, update, and maintain uptime statistics:
| Dependency | Description |
|---|---|
| dbw_db_uptime | Holds the current server instance information |
| db_uptime | Main procedure which collects and updates instance uptime statistics |
| dbw_db_uptime_histr | Contains the historical server instance information for audits |
| f_db_availability | Function that computes the availability based on uptime and down periods |
Automated Implementations
- Creation of tables to store uptime data, maintenance periods, and calculated weekly/monthly server availabilities.
- Procedures that insert, update or reset data based on current server statistics and predefined maintenance schedules.
- Functions to calculate downtime and update corresponding availability metrics based on server activity logs.
System Requirements
This job is suitable for instances running on:
- SQL Server versions beyond 2005.
- Either on Microsoft SQL Server or Azure SQL Managed Instance platforms.
System Utilizations
- Regular database transactions to update and maintain accurate information on server performances.
- Resource usage includes minimal CPU, memory, and storage during the execution of scheduled tasks.
Vulnerability Considerations
- Adequate error handling to manage exceptions during data capture, ensuring high reliability and reducing potential crashes or inconsistencies in server monitoring reports.
Overall, the dbWatch Control Center job serves as a critical function for administrators to maintain the optimal performance and reliability of database servers, ensuring that uptime metrics are consistently monitored and reported for strategic IT and business planning.