While investigating a failed job that occurred over night I stumbled on to a novel situation that I thought was pretty interesting.
A server that was part of an AOA group was performing nightly backups. The first step is to determine whether the server was the primary or secondary. At the time the job began the server was the primary so it moved ahead to begin backing up the databases. While this was happening the server was bumped down to secondary and the backup jobs began failing as SQL won't allow full backups (without COPYONLY). This then lead to an alert from our monitoring solution.
Is it possible to continually check if the server is primary while performing backups?
Best Answer
We use ola hallengren backup scripts/jobs and that does check the cluster settings for each database.
For each database it will check, can I backup this database?, using the cluster settings and current cluster status. So if your AOAG is set to only allow backups from the Primary it wont backup a database that is currently a secondary.
Works great for us, the only issue we run into is the currently running DB backup will usually fail during a failover event because all connections are briefly severed.