2 node AG: Backup single node only
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?
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.
Sign in or register to add a comment.
Add a Comment
Welcome to CloudBerry Lab Forum!
MS SQL Server
CloudBerry Managed Backup
CloudBerry Remote Assistant
FAQ - Managed Backup - Initial Setup
FAQ - Managed Backup - Licensing
FAQ - Managed Backup - Storage
FAQ - Managed Backup - Backup Client
Terms of Service
Useful Hints and Tips
Created with PlushForums
© 2019 CloudBerry Lab Forum