Backing up Always On MSSQL with Veeam

[sukybains]

I am aware of the best practices around the backup of a SQL AlwaysOn cluster in order to prevent failover etc.

However, I would like to understand if there are any best practice recommendations on the recovery procedure for a failed node in an AlwaysOn cluster. Is it the case that SQL will recognize a restored node that is out of sync and hence automatically start a re-sync to the recovered node or are there some post recovery opertions that have to be applied in order to restore the SQL AlwaysOn Cluster back into its production state?

[lightsout]

I've restored to the active node, and the cluster has taken care of itself automatically.

[sukybains]

thanks for a prompt response lightsout !!!!!!

[foggy]

Artur, best practice is to place both nodes into a single job for proper transaction logs handling. That said, failover during snapshot operation is not something Veeam B&R can handle, since it is purely environmental thing (depends on infrastructure and underlying hardware). In order to avoid failover, some fine-tuning may be required.

[JaYaKaAzZ]

Thank you Alexander

[DeaconZ]

Some of the documentation on the backup of SQL AAG's indicates the creation of "child" jobs for log backup only every 15 minutes or so. Is this automatically created? It seems to indicate that.

[foggy]

Yes, those are created automatically, once you enable logs backup in the job settings.

[DeaconZ]

So, one more question, and it may have been covered in here already and i missed it. In my group I have 2 in the primary DC and 1 in the failover DC. Each has its own Veeam job. When the log backups run every 15 minutes, it states the failover SQL job is already backing up the logs. Should I set the failover DC job to only do "Truncate" on the successful backup instead of the log backups? Or set it to no processing?

[foggy]

You should set the 'Do not truncate' setting for these VMs.

[DeaconZ]

Thank you. Another thing I am noticing is that the interval jobs on the primary datacenter SQL servers, it periodically throws a bunch of "Failed to save transaction log backup file for database model to repository: No such host is known"
I verified DNS is working correctly, so not sure of the issue.

EDIT: looks like the above was a firewall rule blocking it.

It also seems to intermittently have trouble recognizing system DB's and excluding them including Master DB.

[foggy]

It also seems to intermittently have trouble recognizing system DB's and excluding them including Master DB.

Please contact technical support regarding this.

[fernando.laguna]

Hi Guys,
I just recieve a question from a customer that have been reading the entire post, and we have a customer that has SQL 2012 Server Always on and the is willing to use VB&R V9.
Infrastructure detail:

SLQ Server 2012 Always on with:
1 Active Server
1 Passive Server
1 Réplica Server.

In a nutshell costumer is asking if there is any procedure or detailed documet how to backup their always on.
they read the suggestion of creating an script to turn passive into active, but they are not willing to follow that path and they also read a thread that specify in Summary, backing up a SQL Always ON environment, is just Add the SQL Servers in a Backupjob as usual and veeam will do the rest ??

Any input is hagly appreciated.

[veremin]

Put both nodes in a backup job. And specify transaction log truncation settings in accordance with your needs and desires. Thanks.

[johannesk]

Hi nholman,

I struggled with log truncation in SQL AlwaysOn backups. I researched with Veeam support and they pointed out this article that describes how the AlwaysOn nodes are evaluated for log backups.

https://helpcenter.veeam.com/backup/vsp ... pport.html

In our case, the point #2 was affecting us in such a way, that log backup did not happen on the primary node, since there were files in the temp directory on the secondary node. Hence, logs were only backed up from the secondary node with Copy only and logs did not get truncated.

If this is affecting you, you should see for the active node this message (not a warning or error).
“….Skipping databases to be backed up from another SQL Server node…”

[Havgon]

Has anyone experienced any issues with backing up these Microsoft Always On Clusters? There have been several instances where fail over, occurs during the time of the snapshot. Not sure, but if this was the case I would see a bunch more of these errors. We have about 30TB's of these clusters and we have them configured to do full back ups with 7 restore points.

Thanks in advanced
Javier

[foggy]

Javier, please review some feedback above, should answer your questions to some extent.

[Havgon]

Thank you

[johannesk]

hi,

I've been backing up SQL server AlwaysON systems with a single backup job. Thus the same job backing up all the nodes in the availability group to the same repository.

I notice that the backup job realizes what node is active and tends to do log backups and log truncation on the active node.

Now I'm wandering, would it introduce any problems if I use one backupjob per node instead of one job with all the nodes. Is there something with Veeam that says if you are backing up AlwaysON that all the nodes need to belong to the same backup job? If so, I don't find it. And if it's not so I'm considering changing the backup strategy and have multiple backupjobs for AlwaysON systems.

regards,
Johannes

[veremin]

Kindly, check the answers above as well as the corresponding section of our online Help Center. Thanks.

[johannesk]

Well this thread is long and has information that might be outdated. At least it's telling mixed advices such as:

"Veeam support advised me not to backup both SQL nodes with the same job."

and

"...best practice is to place both nodes into a single job for proper transaction logs handling. That said, failover during snapshot operation is not something Veeam B&R can handle, since it is purely environmental thing (depends on infrastructure and underlying hardware). In order to avoid failover, some fine-tuning may be required."

I'm posting this question so that we can all have a solid answer from Veeam. Pointing to this thread with mixed advice is only confusing.

[foggy]

"Veeam support advised me not to backup both SQL nodes with the same job."

This most likely was a recommendation that was given prior adding SQL Always On support to Veeam B&R. Current best practice is to put all nodes into a single job.

[stewsie]

Hi

We are looking to implement Microsoft SQL AlwaysOn Availability Groups and as part of this I am interested to see how others are backing up this service?

I have found one Veeam article that mentions using Veeam agents, https://www.veeam.com/kb2463. Is this the only way to backup the availability groups?

At the moment the SQL backups are managed by the DBA team using native SQL Tools

Thanks

[DGrinev]

Hi Paul,

Yes, you can protect AlwaysOn Availability Groups only with the Veeam Backup Agent for Windows. Thanks!

[stewsie]

Thanks for the update

https://helpcenter.veeam.com/docs/backu ... l?ver=95u4

I cannot see any mention of the agent at the above link

[DGrinev]

Hey,

I am sorry, I was thinking we're talking about MS Failover Cluster with SQL AlwaysOn Availability Groups.

You've found the right article in the User Guide, since Veeam B&R supports SQL AlwaysOn starting from a few versions before the 9.5 U4.

[stewsie]

Hi

During our testing we have seen that the SQL databases are backed up on both nodes in the availability groups during the image level backups. Is there a way to exclude the secondary node from backing up the SQL database even in copy only mode? I have seen in the Veeam documentation that this appears to be the process. Can someone please explain why both databases are backed up?

This seems to be duplicating data for no benefit that the DBA can see.

Thanks

[foggy]

You can exclude the secondary node, just don't add it to any other job, to avoid mess.

[stewsie]

I still want to backup the server just not SQL. Can I exclude the database files from the job or will the database still backup when VSS runs?

[veremin]

You can exclude SQL db from processing using this regkey. Is this something you're after? Thanks!

[stewsie]

I will investigate this. Thank you