Expected behavior during failover

MSP360 Backup Backup SQL Server
1

Hello!

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?

Thank you,
-AC

2

[reply=“Andrew Carter;d426”] 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.

3

[reply=“Andrew Carter;d426”] 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
AS
IF sys.fn_hadr_backup_is_preferred_replica(N’MyDB’) <> 1
RAISERROR (‘No’, 18, 1);
ELSE
RETURN 0;

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.

“TestHA.CMD”

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):

C:\USERS\USERNAME\TESTHA.CMD

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.