2 node AG: Backup single node only

MSP360 Backup Backup SQL Server
1

Currently, I am backing up SQL backup to disk and having Cloudberry back that up to AWS. This is our last ditch backups that we like to have on hand in a worst case scenario. These are not our only backups.

I am testing SQL 2017 Availability Groups on Windows 2016 Data Center with two nodes (possibly three). I would like to know if there is a way to have Cloudberry installed on all the nodes in the Availability Group but only have it backup a single node when it is the primary. The purpose of this is to save space (and money) on our offsite backup (AWS), prevent backup jobs from bumping into each other, and allow any node to be taken down without disrupting the Cloudberry/AWS backups.

To summarize what I want:

  1. Install Cloudberry on all the nodes
  2. Have Cloudberry determine if it is on the Primary (or possibly a single secondary)
  3. Only conduct Cloudberry backup if primary or secondary

Is this possible?

2

[reply=“Rich Anderson;d335”] Just brainstorming here… I think the only way you could force SQL Server AG backups only on the preferred replica would be to use a Pre-Backup Action that ran a SQLCMD script to check if a known database in the AG was the preferred replica (using the sys.fn_hadr_backup_is_preferred_replica function). If not, raise an error (RAISERROR) which might trigger an error that would terminate the backup. The backup plan would have to be confined to databases in the AG. This would need to be tested. If i have time tomorrow, I’ll give it a go.