March 29, 2019 at 7:09 am
Hi,
I have AOAG set up with one of my databases. I only have a primary and secondary replica. For a while now, I've had my backup preferences for the AG set to "Primary" and I've been taking backups successfully using Ola Hallengren's scripts. I'm actually at a point where I have the resources to be able to take (not offload) backups on the secondary too, just as a matter of redundancy. However, when I change my backup preference to "Any Replica" and run Ola's scripts on the secondary, it's not producing a full backup. Is it true that there can be only one instance of SQL Server that can take a backup of an AG at any given point? I was under the impression that backups could be taken from *both* the primary and the secondary when they're both active.
If I can't do this, then I'll just stick with the Primary. If anyone can shed some light, I'd appreciate it.
Thanks in advance,
Mike
Mike Scalise, PMP
https://www.michaelscalise.com
March 29, 2019 at 9:49 am
You probably know this but you can only take copy-only full backups on the secondary, so if the backup script doesn't specify copy-only that might be why it keeps defaulting to the primary replica.
Theoretically you could set up an additional SQL Agent job on each replica to say IF this is the secondary replica, THEN take a copy-only backup, ELSE do nothing.
Obviously this will affect your licensing position. I know you say you won't be offloading your backups, but it still counts as Production workload.
April 3, 2019 at 5:51 pm
Beatrix,
Thank you so much. I like your suggestion for creating a second job. I think that is what would be necessary to get this to work as I want. We are licensed for that secondary replica, so I'm not worried about the implications there. In any case, thank you again!
Mike
Mike Scalise, PMP
https://www.michaelscalise.com
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply