AlwaysOn transaction log backup alert
Job details
| Name: | AlwaysOn transaction log 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: | 2.92 |
| Default schedule: | 5,35 6-18 * * |
| 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 | 360 | Alarm threshold (in minutes). |
| backup_warning_threshold | 60 | Warning threshold (in minutes). |
| missing_backup_status | 2 | Return status value (ALARM – 2, WARNING – 1, or OK – 0) when missing transaction log 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 only in AlwaysOn Groups (YES/NO). |
| 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 “AlwaysOn transaction log backup job” is designed to monitor and check the transaction log backup dates of databases in AlwaysOn groups, including both primary and secondary databases, ensuring that they are timely and up-to-date.
- Why: This job is crucial to ensure data consistency and recovery capabilities are intact for databases in MS SQL Server AlwaysOn Availability Groups. Inadequate transaction log backups can lead to significant data loss or inability to restore databases, especially during a failover event.
- Manual checking: You can manually check transaction log backup status with SQL queries in SQL Server Management Studio (SSMS) by examining the msdb backup history or checking AlwaysOn group states.
Technical Details
- Job Name: AlwaysOn transaction log backup alert
- Version: 2.92
- Company: dbwatch.no
- Category: Cluster and Replication
- Instance Compatibility: Only applicable to SQL Server instances where AlwaysOn has been activated.
Key Elements
- “Alerts on transaction log backup status”, “General backup size and compression statistics”, and “Host-based distributions for backups.”
Configuration
- Exclusion Settings: Allows specifiying which AlwaysOn groups or databases should be ignored during monitoring.
- Case Sensitivity: Adjusts for case sensitivity in database names during evaluations.
- Database Inclusivity: Option to extend checks beyond AlwaysOn groups to all replicated databases.
Functionality
- Backup Status Monitoring: Checks how recent transaction log backups were taken and flags databases with backups not meeting specified warning and alarm thresholds.
- Host Diversity Checks: Evaluates transaction logs taken across multiple hosts within a day to enhance resilience and recovery capabilities.
Alert Generation
- Generates alerts and escalates notifications based on the age of the last transaction log backup:
- Alarm: Triggered if the latest backup exceeds the defined alarm threshold for backup age.
Output and Reporting
- Backup Overview for AlwaysOn Groups and Local Databases:
- Reports include detailed backup statistics, specifics about compression ratios, and identifies cross-host backup activities.
Tables in Job Output
- “AlwaysOn Groups”: Shows AlwaysOn group information, including backup status and exceptions.
- “Alarm and Warning Information”: Lists databases with current warnings or alarms related to their transaction log backup status.
- “Transaction Log Backup Information”: Separates reporting between databases within AlwaysOn groups and local databases to provide clear visibility into backup health in different setups.
Execution and Scheduling
- Default Schedule: Scheduled to run at 5 and 35 minutes past the hour, every hour between 6 AM and 6 PM.
- Execution Timeout: Set at 120 seconds, ensuring prompt completion to avoid undue impact on server performance.
Data Privacy and Security
- The job handles sensitives database information securely, adhering to standard data protection and privacy policies required for database management and monitoring systems.