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.6
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.
exclude_copy_only_backups	YES	Specifies whether copy-only backups should be ignored when evaluating the last backup time for each database. When set YES, the job will not consider copy-only backups as valid backups when checking backup status. The database is still included in the check, but only regular backups are used for evaluation.

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.