Receiving many DBSS - Days Since Last Backup alarms on AG secondary node.
説明
An environment has many SQL Server availability groups where the secondary replica is not available for monitoring.
These availability groups can failover at any time between 2 servers. In some cases, there is a two node cluster where the availability groups are split between the two nodes with some availability groups active on each node. Since the databases are not available on the secondary, many Days Since last backup are fired.
原因
Knowledgebase article 4311845 detail the situations where the DBSS - Days Since Last Backup alarm will not fire.
対策
RESOLUTION 1 In this environment the secondary node was always in READ_ONLY by default, therefore backups wouldn't be done on the secondary replica and backups were performed on the primary replica based on the selected backup preference option of "Prefer Secondary" in the AG.
Select "Primary" as the option for backup preference in the AG then the alarm will filter out the secondary by "preferred_replica_for_backup=0"
RESOLUTION 2 Enable the COPY_ONLY option and remove the "updateability check" step in the backup job. Once the COPY_ONLY is checked, the full backup can be executed. So both primary and secondary can back-up then the alarm will not trigger.