1

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?

3
  • Run them using the agent powershell subsystem and make the first jobs step a check to see if it's primary or not using the typical t-sql functions such as sys.fn_hadr_is_primary_replica. Aug 15, 2019 at 12:46
  • @SeanGallardy I have @ReadOnly=False in the list of target conditions for the policy, and that works fine for ignoring any secondary databases, but the issue arises when I do a failover to a different node. All of the recent backup history is in msdb on the previous primary node, so the policy check on the new primary node thinks the database hasn't been backed up recently (we do weekly fulls, nightly diffs, and hourly logs, so it could be up to a week before the next full backup happens on this node).
    – db2
    Aug 15, 2019 at 13:37
  • Let me be more clear. If it's the primary then you can create your own failover table and check that before running the health check. Then you'd know if it just failed to not alert you for x amount of time. Aug 15, 2019 at 14:57

1 Answer 1

0

You can use @LastBackupDate and ExecuteSQL ('Numberic','Select master.sys.fn_hadr_is preferred_replica(DB_NAME())')=1 in condition.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.