AlwaysOn differential database backup alert
Job details
| Name: | AlwaysOn differential 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.3 |
| Default schedule: | 15 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 | 1 | Return status value (ALARM – 2, WARNING – 1, or OK – 0) when missing differential backup of a database. Only in use if parameter ignore_if_not_implemented is set to NO. |
| 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 | YES | Includes databases not in AlwaysOn Groups (YES/NO). |
| ignore_if_not_implemented | YES | Ignores databases where differential backup is not implemented (parameter value YES). |
| 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: Monitoring the status and details of differential database backups for both local and AlwaysOn group databases operating under SQL Server.
- Why: This job is crucial to ensure that differential backups are conducted in a timely manner. Timely backups are essential for data integrity and recovery strategies, especially in environments utilizing SQL Server AlwaysOn for high availability and disaster recovery.
- Manual checking: Manual checking involves querying the database backup tables to verify the latest backup times, sizes, and other relevant parameters.
Implementation Details
- Differential backup checks are executed for all online databases, including local and those in AlwaysOn clusters.
- The job distinguishes between primary and secondary replicas in the AlwaysOn groups.
- Utilizes various properties and thresholds to raise alerts or warnings if backups have not occurred within the specified intervals.
Configuration
- Includes parameters for alarm thresholds, exclusion lists for databases and groups, and toggles for case sensitivity and inclusion of non-AlwaysOn databases.
- The job can be configured to ignore databases where differential backups are not implemented based on the setting of the “ignore_if_not_implemented” parameter.
Alerting Logic
- An alert or warning is issued based on the recency of the last differential backup.
- The job aggregates data regarding backup size, duration, and the number of databases checked.
- Complex conditions check each database against specified thresholds and exclusion settings to fine-tune alert generation.
Database Coverage
- The job processes databases including and excluding those in AlwaysOn groups based on configuration settings.
- The script ensures that databases not configured for differential backups are handled based on user-specified parameters.
Output and Reporting
- Reports include key metrics like backup start and finish times, sizes, and durations, presented in customized tables with sorting and filtering capabilities.
- The result-set presentations are targeted at providing a detailed view of backup status across the different database groupings, whether local or part of AlwaysOn configurations.
Tables Presented in Reports
| Title | Description |
|---|---|
| AlwaysOn Groups | Shows AlwaysOn group details that have differential backup statistics. |
| Alarm and Warning Information | Lists databases that have backup-related warnings or alarms, indicating potential risks. |
| Differential Backup Information (AlwaysOn databases) | Displays the most recent backup information for databases in AlwaysOn groups. |
| Differential Backup Information (Local databases) | Lists details about the most recent backups for local databases not in AlwaysOn groups. |
- Through these tables, the job aids in monitoring and mitigating risks associated with the non-execution of necessary differential backups, tailored to the intricacies of high availability setups like SQL Server AlwaysOn.