Question

morrisos on Fri, 15 Aug 2014 14:23:38


We want to do a Full backup once per week and Diff on days in-bwtween on the primary replica of our AG. 

Does anyone run Full/Diff on the primary in the AG?  If so what happens if there is an automatic failover during the Differential schedule?  Is the backup chain broken?  I know about running the Primary Replica check but am concerned if Primary replica changes mid-backup cycle.

Just curious as to how people set-up Full/Diff on AG group.

Thanks, Morrisos


Sponsored



Replies

Sean Gallardy on Fri, 15 Aug 2014 14:48:40


Hello Morrisos,

Does anyone run Full/Diff on the primary in the AG?

Sure, if you're not under IO strain I see no reason to offload. Diffs can only be done on the primary though.

If so what happens if there is an automatic failover during the Differential schedule?

The failover happens, your diff (if not cleaned up) is not a valid backup (though it should have automatically been cleaned up). That's about it.

Is the backup chain broken?

It is not.

I know about running the Primary Replica check but am concerned if Primary replica changes mid-backup cycle.

If it changed mid backup, your backup would be null and void, not usable. This may impact you in other ways such as not having an actual backup. Otherwise, there is no *harm* done to the database, just to recoverability through backups as you'll potentially be missing one. It won't break the log chain unless there is a hidden race condition bug.

morrisos on Fri, 15 Aug 2014 16:14:00


Thanks Sean.  So we basically need to be alerted to failover and start a new backup cycle to maintain backup continuity?

Sounds like a full every night with our log backups during the day might be safer at the expense of backup time.

Thanks, Morrisos

Sean Gallardy on Fri, 15 Aug 2014 17:17:40


Morrisos,

So we basically need to be alerted to failover and start a new backup cycle to maintain backup continuity?

You should always be altered to a failover! If the backup failed mid-stream, your chain wouldn't be broken so there is nothing that needs to be done from that aspect.

What should be done is on all of the possible replicas that could host the AG as a primary should have the jobs synchronized so that no matter which replica it is one, backups happen. If you have a good routine, missing a single backup won't present much of a problem (arguably). If all nodes have the jobs and everything is as it should be, there should not need to be any manual intervention. Even if there would need to be, these events can be automated by using the message id to trigger an automated response in by SQL Server Agent.

Sounds like a full every night with our log backups during the day might be safer at the expense of backup time.

Remember that all databases in an AG that are synchronized are exact copies of each other down to the bit. If you miss a backup and something very bad happens, you still have copies that are available and good. Should you have a multi-site outage or disaster, you'll need to go from backups and at that point I would assume you have a different SLA involved.

I'm a firm believer that you can never have to many good backups :)

Rama Udaya on Fri, 15 Aug 2014 21:09:54


Adding one more thing here, are you going to use the 3rd party tool for the backup or you are using the SQL native jobs to do the backups for you?

if incase, you are using any 3rd party tool,then I would say to you to test it & see -that will picks up.

because there might be mannual work needs to be done here incase if AAGS get failed over to other .