AlwaysOn database backup alert
Job details
| Name: |
AlwaysOn database backup alert |
| Platform: |
Sqlserver |
| Category: |
Cluster and Replication |
| Description: |
Checks the status of each member node of the current WSFC cluster based on Dynamic Management Views sys.dm_hadr_cluster_members. |
| Long description: |
|
| Version: |
3.5 |
| Default schedule: |
5 6 * * |
| Requires engine install: |
No |
| Compatibility tag: |
instance[databasetype=‘sqlserver’]/.[alwayson_active=‘YES’]/.[maj_version > ‘2008′ & engine_edition = ‘Microsoft SQL Server’ & eng_inst_priv = 0] |
Parameters
| Name |
Default value |
Description |
| backup_alarm_threshold |
48 |
Alarm threshold (in hours). |
| backup_warning_threshold |
24 |
Warning threshold (in hours). |
| missing_backup_status |
2 |
Return status value (ALARM – 2, WARNING – 1, or OK – 0) when missing backup of a database. |
| exclude_groups |
|
The list of AlwaysON Groups to be ignored (separated by comma). |
| exclude_databases |
master, msdb, model |
The list of databases to be ignored (separated by comma). |
| check_all_databases |
NO |
Includes databases not in AlwaysOn Groups (YES/NO). |
| check_differential_instead |
NO |
If set to YES the procedure checks differential backup instead of full databases backup and |
| differential_and_full_warning_threshold |
168 |
Warning threshold (in hours) for last full backup although differential backup |
| differential_and_full_alarm_threshold |
336 |
Alarm threshold (in hours) for last full backup although differential backup |
| use_uppercase |
NO |
Change all database names to uppercase when checking backup statistics. Should not be used for instances that are case sensitive. |
Job Summary
- Purpose: The purpose of this job is to monitor and alert on the backup status of databases involved in SQL Server AlwaysOn Availability Groups.
- Why: This job is crucial for ensuring that all databases, especially those critical for AlwaysOn setups, have recent backups. Monitoring helps in preventing data loss and maintaining high availability by alerting on missed backup windows or configurations that could jeopardize the integrity or availability of the data.
- Manual checking: Manually checking the backup status can involve querying system tables and views related to backup history and AlwaysOn configuration in SQL Server. Commands such as the following can be used:
SELECT d.name AS 'DatabaseName', MAX(b.backup_finish_date) AS 'LastBackupDate' FROM sys.databases d LEFT JOIN msdb.dbo.backupset b ON d.name = b.database_name WHERE b.type = 'D' GROUP BY d.name ORDER BY LastBackupDate DESC;
Key Alerts and Metrics Tracked
- Checks the backup dates (full/differential) for all local databases and those in the AlwaysOn groups.
- Only ONLINE databases are checked.
- Alerts are raised based on the defined thresholds for warning and alarm related to the age of the last backups.
Tables and Reports
- AlwaysOn Group Information
- Backup Warning and Alarm Information
- Detailed Backup Information for AlwaysOn and Local Databases
Alerts Configuration
- Alerts are configurable for different thresholds, and include both “warning” and “alarm” levels depending on the time elapsed since the last backup.
- Differential backups can also be monitored as a fallback mechanism if full backups are not updated within the thresholds.
- Excludes specified AlwaysOn groups and databases from checks to focus monitoring on relevant targets only.
Extended Functionality and Details
- Ability to exclude specific AlwaysOn groups and databases.
- Option to include databases not in the AlwaysOn groups.
- Can be set to monitor either case-sensitive or case-insensitive database names based on the SQL Server instance configuration.
- Distinguishing between primary and secondary nodes in an AlwaysOn setup to appropriately monitor and report on backup status.
Database Backup Monitoring Engine
- Scheduled tasks within dbWatch to frequently check backup status.
- Utilizes the SQL engine to fetch and analyze backup data.
- Reports generated in both detailed and summary form, showing backup statistics and alerts based on current status compared to configured thresholds.
Deployment and Compatibility
- Designed specifically for SQL Server instances that are configured with AlwaysOn active setup.
- Compatible with SQL Server versions above 2008.
- Easily installable with required parameters pre-defined which can be customized post-installation.