Availability Database Backup Status

Microsoft.SQLServer.Windows.Monitor.AvailabilityDatabaseHealth.BackupStatus (UnitMonitor)

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. Note that this monitor is disabled by default. Use overrides to enable it when necessary.

Knowledge Base article:

Summary

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. Note that this monitor is disabled by default. Use overrides to enable it when necessary.

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.

Note that due to the Availability Group database backup specifics, the alerting for this unit monitor is disabled by default and it is not recommended to enable it. The actual health state of the availability database backup shows the "Availability Database Backup Status (rollup)", which has the 'Best state of any member' health rollup policy option.

See the Availability Database rollup monitors alerts article for more details.

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.

Causes

Database has not been backed up for too long.

Resolutions

You should perform a backup of one of the availability database 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.

External

Best Practice recommendations for SQL Server Database Backups

Overridable Parameters

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

The number of times a measured value should breach the 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 is specified by using a 24-hour format. Can be omitted.

 

Timeout (seconds)

Specifies the time the workflow is allowed to run before being closed and marked as failed.

300

Timeout for query execution (seconds)

The workflow will fail and register an event, if the query execution takes longer than the specified period.

60

Timeout for database connection (seconds)

The workflow will fail and register an event, if it cannot access the database during the specified period.

15

Element properties:

TargetMicrosoft.SQLServer.Windows.AvailabilityDatabaseHealth
Parent MonitorSystem.Health.AvailabilityState
CategoryAvailabilityHealth
EnabledFalse
Alert GenerateFalse
Alert Auto ResolveTrue
Monitor TypeMicrosoft.SQLServer.Windows.MonitorType.AvailabilityDatabaseHealth.BackupStatus
RemotableTrue
AccessibilityPublic
RunAsDefault

Source Code:

<UnitMonitor ID="Microsoft.SQLServer.Windows.Monitor.AvailabilityDatabaseHealth.BackupStatus" Accessibility="Public" Enabled="false" Target="SqlDiscW!Microsoft.SQLServer.Windows.AvailabilityDatabaseHealth" ParentMonitorID="Health!System.Health.AvailabilityState" Remotable="true" Priority="Normal" TypeID="Microsoft.SQLServer.Windows.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>
<MonitoringType>$Target/Host/Host/Property[Type="SqlDiscW!Microsoft.SQLServer.Windows.DBEngine"]/MonitoringType$</MonitoringType>
<Threshold>7</Threshold>
<ConsiderAgBackupPreference>false</ConsiderAgBackupPreference>
<NumSamples>2</NumSamples>
<SqlExecTimeoutSeconds>60</SqlExecTimeoutSeconds>
<SqlTimeoutSeconds>15</SqlTimeoutSeconds>
<TimeoutSeconds>300</TimeoutSeconds>
<IntervalSeconds>3600</IntervalSeconds>
<SyncTime/>
</Configuration>
</UnitMonitor>