Expected behavior during failover
I am wondering how CloudBerry behaves when a SQL node is failed over. Our current deployment has been to install CloudBerry on both sides of an AOAG so it will back up whichever side is currently active. Does this setup function correctly?
If e.g. a full backup was taken on node 1, then the cluster is failed over to node 2, does node 2 also take a full backup?
Or, let's say they both have full backups in storage, and the cluster then fails over. Does the new primary take another full backup, so that it doesn't backup transaction logs that don't match up with its last full backup or differential?
In this case the software will simply try to work according to the schedule, so full/diff/tl backups will only happen when they are supposed to happen. Since you're using 2 machines to perform backups they will run backups independently of each other, which is actually a pretty safe strategy if you using 2 backup prefixes, so you can recover the needed data from either backup destination.
My recommendation would be to have backup plans on each AG instance but instruct CloudBerry Backup to only execute them if the database is the preferred replica in the AG. Backing up both the preferred replica and a secondary replica in an AG has licensing implications in SQL Server (in addition to running multiple backups from more than once instance which has overhead to the AG instances). SQL Server only allows backups on the preferred replica - any backups on secondary replicas require another SQL Server license.
This is how I'd probably look to support AGs with CloudBerry:
1- Create a stored procedure in the master database so it's available for execution from any database. The procedure checks one of the databases in the AG to see if it's the preferred replicate. If it is, the script returns a 0. If not, the script raises an error.
2- Create a CMD file that executes the SQL Server store procedure in step 1 using SQLCMD
3- Execute the CMD file as a Pre-Backup Action in CloudBerry and instruct the backup plan to exit the backup plan if the pre-backup action fails
Example stored procedure you can create in management studio in master.
CREATE PROC dbo.CheckPreferredHA
IF sys.fn_hadr_backup_is_preferred_replica(N'MyDB') <> 1
RAISERROR ('No', 18, 1);
Create a CMD file and save somewhere where CloudBerry can access it during backup. You may need to adjust the server name and authentication type, but you need to use the "-b" parameter.
sqlcmd -b -S SERVER\NAMED_INSTANCE -E -Q "EXEC dbo.CheckPreferredHA;"
From the CloudBerry Backup Wizard on the Pre-Post Actions tab, check the Pre-Backup Action and enter the fully qualified file name (any folder would work as long as the CloudBerry service can access):
Check the Exit Backup Plan if Pre-Backup Action Failed option.
Do this on a test instance and verify it works, but what you should see is that when the database is not the preferred replica, the stored procedure raises an error and the "-b" parameter in SQLCMD causes the script to return an ERRORLEVEL and this causes the backup to terminate.
Let me know if you have any questions.
Sign in or register to add a comment.
Add a Comment
Welcome to MSP360 (CloudBerry) Forum!
MSP360 Managed Backup
Microsoft SQL Server
Microsoft SQL Server
MSP360 Remote Desktop
Error occurred during backup; Arithmetic operation resulted in an overflow
Error occurred during backup: You are not authorized to perform selected operation.
ERROR: Error occurred during backup: The provided security credentials are not valid.
Files and folders were skipped during backup, error 1603
Terms of Service
Useful Hints and Tips
Created with PlushForums
© 2021 MSP360 (CloudBerry) Forum