Comprehensive data protection for all workloads
Post Reply
wandersick
Influencer
Posts: 12
Liked: 2 times
Joined: Nov 09, 2017 6:15 am
Full Name: wandersick
Contact:

AlwaysOn availability group replicas in separate backup jobs

Post by wandersick » 1 person likes this post

When backing up AlwaysOn availability group make sure all cluster nodes are processed by the same backup job for transaction logs processing and restores to work properly. (https://bp.veeam.expert/applications/sql_server.html)
A design question: In case we must have separate backup jobs (actually, separate standalone/all-in-one Veeam backup servers) to back up each cluster node, can the statement below be considered a solution (or workaround) to maintaining transaction consistency and other concerns, i.e. by specifying all replica nodes to be "copy only" except the primary?
When configuring a backup job to process Distributed Availability Groups transaction logs, select either primary or the secondary distributed availability group. Otherwise, the log chain of the distributed group databases might become inconsistent. When configuring a backup job to back up transaction logs for other Distributed Availability Groups, use the Perform copy only mode. (https://helpcenter.veeam.com/docs/backu ... tml?ver=95)
Or, is there some caveat still and other consideration or configuration is required? Please advise. (We are using SQL Server 2016 Enterprise Edition and Veeam 9.5 Update 3 with typical Availability Group but is cross-subnet -- we do not use Distributed Availability Group)

Thanks!
MStrong
Lurker
Posts: 2
Liked: 1 time
Joined: May 22, 2019 2:42 pm
Full Name: Matthew Strong
Contact:

Re: AlwaysOn availability group replicas in separate backup jobs

Post by MStrong » 1 person likes this post

We also have a SQL AoAG, however it is same subnet and we handle the backups as follows:
SQL backs up all databases internally - to a volume local to the SQL server VM - at least once per day. This ensures backup consistency, including transaction log backups because it is all handled by SQL. Then Veeam snapshots the entire SQL VM however often you want. Veeam does not perform any application consistency functions on the SQL server as that is what induces the potential for inconsistency when you are backing up an availability group. Your RPO is managed by SQL internally and you can keep as many restore points as you have disk space to maintain. (Have SQL compress the backup files to maximize storage efficiency.)

The way we have the backup jobs configured within SQL Management --> Maintenance Plans is as follows (and exactly the same on all AoAG nodes): Execute a tSQL script to determine if the current server is the Availability Group Primary; If yes (the current server IS the availability group primary), proceed with a copy-only backup (so as to not mess with the availability replica journal LSN); if No (the current server is NOT the group primary) then quit reporting success.
This way only the primary server of the SQL availability group is performing the backups, backup journaling consistency is maintained and you can snapshot all SQL nodes at whatever periodic interval you chose.
Now all of this only applies to Availability group databases. You would backup master, model, msdb independently on each SQL server as you would traditionally.

I don't see how multiple subnets will have an impact on this at all, as long as all hypervisors are licensed through the same Veeam instance, you can keep a snapshot of each SQL node with multiple restore points, restore one or more as needed and if you really need to, crack the backup open and extract SQL backup/transaction log files to perform a point in time restore. The only real difference is being able to have copies of the SQL backups stored off of the SQL server and if you are snapshotting the SQL VMs periodically over to a Veeam repository, you are accomplishing that by the nature of the fact that the backup files are within the VM image.
Post Reply

Who is online

Users browsing this forum: BackItUp2020, Bing [Bot] and 227 guests