Using Policy-Based Management to check backup history for an Availability Group database

by db2   Last Updated August 14, 2019 13:06 PM

I like using Policy-Based Management to do some simple "everything's okay" sanity checks and email me if something goes out of spec. Typically, I validate the time since the last full backup, both to verify backups are running on schedule, and also to make sure that newly created databases are being included in backups. This works perfectly fine on a standalone server.

However, we recently deployed a two-node Always On Availability Group. It's configured to run backups on the current primary node. As you're probably aware, backup history is stored in msdb on the server that performs the backup. This causes a problem with the @LastBackupDate property, which only checks the local backup history for the server the policy is being evaluated on. After a failover, the backup policies almost immediately go out of compliance, as the backups have been running from the other server for however long it was primary (most likely for longer than the span that the policy is checking).

Is there any reasonably simple way to make these policy checks Availability-Group-aware? Or am I going to have to look for some other backup monitoring solution?

Related Questions