This monitor checks availability of a full database backup and its age as reported by Microsoft SQL Server; it does not apply any logic regarding the replicas preferred for the backup.
This monitor checks the existence and age of a database backup as reported by Microsoft SQL Server. This is done by running a query against the master database of the SQL instance and returning the age of the backup.
By default, the monitor does not track the 'Availability Group Backup Preferences'. If this overdrive is enabled, the monitor will track the backup location configured in the backup preferences of the availability group and will verify whether the backup on the selected replica is in compliance with the backup frequency setting.
Availability Group Backup Preferences can be as follows:
Prefer Secondary
Specifies that backups should occur on a secondary replica except when the primary replica is the only replica online. In that case, the backup should occur on the primary replica. This is the default option.
Secondary only
Specifies that backups should never be performed on the primary replica. If the primary replica is the only replica online, the backup should not occur.
Primary
Specifies that the backups should always occur on the primary replica. This option is useful if you need backup features, such as creating differential backups, that are not supported when backup is run on a secondary replica.
Any Replica
Specifies that you prefer that backup jobs ignore the role of the availability replicas when choosing the replica to perform backups. Note backup jobs might evaluate other factors such as backup priority of each availability replica in combination with its operational state and connected state.
See the Backup Preferences article for more details.
Database has not been backed up for too long.
You should perform a backup of one of the availability database's replicas to resolve the issue.
To eliminate this issue in future it is recommended to schedule an automated backup according to the needs of the application and business requirements.
Best Practice recommendations for SQL Server Database Backups
Name | Description | Default Value |
Backup Period (days) | The target backup frequency in days. Should be set according to your Recovery Point Objective (RPO). | 7 |
Track 'Availability Group Backup Preferences' | Instructs the monitor to track the backup location configured in the Availability Group backup preferences. If enabled, the monitor verifies whether the backup on the selected replica is in compliance with the backup frequency setting. | No |
Number of samples | Indicates how many times a measured value should breach a threshold before the state is changed. | 2 |
Enabled | Enables or disables the workflow. | No |
Generates Alerts | Defines whether the workflow generates an Alert. | No |
Interval (seconds) | The recurring interval of time in seconds in which to run the workflow. | 3600 |
Synchronization Time | The synchronization time specified by using a 24-hour format. May be omitted. |
|
Timeout (seconds) | Specifies the time the workflow is allowed to run before being closed and marked as failed. | 300 |
Target | Microsoft.SQLServer.Linux.AvailabilityDatabaseHealth |
Parent Monitor | System.Health.AvailabilityState |
Category | AvailabilityHealth |
Enabled | False |
Alert Generate | False |
Alert Auto Resolve | True |
Monitor Type | Microsoft.SQLServer.Linux.MonitorType.AvailabilityDatabaseHealth.BackupStatus |
Remotable | True |
Accessibility | Public |
RunAs | Default |
<UnitMonitor ID="Microsoft.SQLServer.Linux.Monitor.AvailabilityDatabaseHealth.BackupStatus" Accessibility="Public" Enabled="false" Target="SqlDiscL!Microsoft.SQLServer.Linux.AvailabilityDatabaseHealth" ParentMonitorID="Health!System.Health.AvailabilityState" Remotable="true" Priority="Normal" TypeID="Microsoft.SQLServer.Linux.MonitorType.AvailabilityDatabaseHealth.BackupStatus" ConfirmDelivery="false">
<Category>AvailabilityHealth</Category>
<OperationalStates>
<OperationalState ID="Good" MonitorTypeStateID="Good" HealthState="Success"/>
<OperationalState ID="Bad" MonitorTypeStateID="Bad" HealthState="Error"/>
</OperationalStates>
<Configuration>
<MachineName>$Target/Host/Host/Property[Type="SqlCoreLib!Microsoft.SQLServer.Core.DBEngine"]/MachineName$</MachineName>
<NetbiosComputerName>$Target/Host/Host/Property[Type="SqlCoreLib!Microsoft.SQLServer.Core.DBEngine"]/NetbiosComputerName$</NetbiosComputerName>
<InstanceName>$Target/Host/Host/Property[Type="SqlCoreLib!Microsoft.SQLServer.Core.DBEngine"]/InstanceName$</InstanceName>
<DatabaseName>$Target/Property[Type="SqlCoreLib!Microsoft.SQLServer.Core.AvailabilityDatabaseHealth"]/DatabaseName$</DatabaseName>
<ConnectionString>$Target/Host/Host/Property[Type="SqlCoreLib!Microsoft.SQLServer.Core.DBEngine"]/ConnectionString$</ConnectionString>
<InstanceVersion>$Target/Host/Host/Property[Type="SqlCoreLib!Microsoft.SQLServer.Core.DBEngine"]/Version$</InstanceVersion>
<InstanceEdition>$Target/Host/Host/Property[Type="SqlCoreLib!Microsoft.SQLServer.Core.DBEngine"]/Edition$</InstanceEdition>
<Threshold>7</Threshold>
<ConsiderAgBackupPreference>false</ConsiderAgBackupPreference>
<NumSamples>2</NumSamples>
<SqlTimeoutSeconds>15</SqlTimeoutSeconds>
<TimeoutSeconds>300</TimeoutSeconds>
<IntervalSeconds>3600</IntervalSeconds>
<SyncTime/>
</Configuration>
</UnitMonitor>