Comprehensive data protection for all workloads
sukybains
Veeam ProPartner
Posts: 6
Liked: never
Joined: Apr 22, 2015 5:57 pm
Full Name: Suky Bains
Contact:

Re: Backing up Always On MSSQL with Veeam

Post by 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
Expert
Posts: 222
Liked: 59 times
Joined: Apr 10, 2014 4:13 pm
Contact:

Re: Backing up Always On MSSQL with Veeam

Post by lightsout »

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

sukybains
Veeam ProPartner
Posts: 6
Liked: never
Joined: Apr 22, 2015 5:57 pm
Full Name: Suky Bains
Contact:

Re: Backing up Always On MSSQL with Veeam

Post by sukybains »

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

foggy
Veeam Software
Posts: 19187
Liked: 1717 times
Joined: Jul 11, 2011 10:22 am
Full Name: Alexander Fogelson
Contact:

Re: Backing up Always On MSSQL with Veeam

Post by foggy » 1 person likes this post

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
Influencer
Posts: 24
Liked: 4 times
Joined: Feb 03, 2014 8:01 am
Full Name: Artur Schneider
Contact:

Re: Backing up Always On MSSQL with Veeam

Post by JaYaKaAzZ »

Thank you Alexander

DeaconZ
Influencer
Posts: 14
Liked: 3 times
Joined: Jul 09, 2010 7:39 pm
Contact:

Re: Backing up Always On MSSQL with Veeam

Post by 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
Veeam Software
Posts: 19187
Liked: 1717 times
Joined: Jul 11, 2011 10:22 am
Full Name: Alexander Fogelson
Contact:

Re: Backing up Always On MSSQL with Veeam

Post by foggy »

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

DeaconZ
Influencer
Posts: 14
Liked: 3 times
Joined: Jul 09, 2010 7:39 pm
Contact:

Re: Backing up Always On MSSQL with Veeam

Post by 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
Veeam Software
Posts: 19187
Liked: 1717 times
Joined: Jul 11, 2011 10:22 am
Full Name: Alexander Fogelson
Contact:

Re: Backing up Always On MSSQL with Veeam

Post by foggy »

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

DeaconZ
Influencer
Posts: 14
Liked: 3 times
Joined: Jul 09, 2010 7:39 pm
Contact:

Re: Backing up Always On MSSQL with Veeam

Post by 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
Veeam Software
Posts: 19187
Liked: 1717 times
Joined: Jul 11, 2011 10:22 am
Full Name: Alexander Fogelson
Contact:

Re: Backing up Always On MSSQL with Veeam

Post by foggy »

DeaconZ wrote: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
Novice
Posts: 3
Liked: never
Joined: Nov 09, 2015 11:44 am
Full Name: Luis Fernando Laguna
Contact:

Re: Backing up Always On MSSQL with Veeam

Post by 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
Product Manager
Posts: 17745
Liked: 1612 times
Joined: Oct 26, 2012 3:28 pm
Full Name: Vladimir Eremin
Contact:

Re: Backing up Always On MSSQL with Veeam

Post by veremin »

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

johannesk
Enthusiast
Posts: 60
Liked: 10 times
Joined: Jan 19, 2016 1:28 pm
Full Name: Jóhannes Karl Karlsson
Contact:

Re: Backing up Always On MSSQL with Veeam

Post by johannesk » 1 person likes this post

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
Novice
Posts: 7
Liked: 1 time
Joined: Jun 17, 2016 1:19 pm
Full Name: Javier Gonzalez
Contact:

[MERGED] Veeam and Always On Clusters

Post by 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
Veeam Software
Posts: 19187
Liked: 1717 times
Joined: Jul 11, 2011 10:22 am
Full Name: Alexander Fogelson
Contact:

Re: Backing up Always On MSSQL with Veeam

Post by foggy »

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

Havgon
Novice
Posts: 7
Liked: 1 time
Joined: Jun 17, 2016 1:19 pm
Full Name: Javier Gonzalez
Contact:

Re: Backing up Always On MSSQL with Veeam

Post by Havgon »

Thank you

johannesk
Enthusiast
Posts: 60
Liked: 10 times
Joined: Jan 19, 2016 1:28 pm
Full Name: Jóhannes Karl Karlsson
Contact:

[MERGED] Backing up node SQL AlwaysON - one backupjob multip

Post by 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
Product Manager
Posts: 17745
Liked: 1612 times
Joined: Oct 26, 2012 3:28 pm
Full Name: Vladimir Eremin
Contact:

Re: Backing up Always On MSSQL with Veeam

Post by veremin »

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

johannesk
Enthusiast
Posts: 60
Liked: 10 times
Joined: Jan 19, 2016 1:28 pm
Full Name: Jóhannes Karl Karlsson
Contact:

Re: Backing up Always On MSSQL with Veeam

Post by johannesk » 1 person likes this post

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 Software
Posts: 19187
Liked: 1717 times
Joined: Jul 11, 2011 10:22 am
Full Name: Alexander Fogelson
Contact:

Re: Backing up Always On MSSQL with Veeam

Post by foggy » 1 person likes this post

johannesk wrote:"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
Expert
Posts: 147
Liked: 8 times
Joined: May 22, 2015 7:16 am
Full Name: Paul
Contact:

[MERGED] Microsoft SQL AlwaysOn Availability Groups

Post by 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
Expert
Posts: 1943
Liked: 248 times
Joined: Dec 01, 2016 3:49 pm
Full Name: Dmitry Grinev
Location: St.Petersburg
Contact:

Re: Microsoft SQL AlwaysOn Availability Groups

Post by DGrinev »

Hi Paul,

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

stewsie
Expert
Posts: 147
Liked: 8 times
Joined: May 22, 2015 7:16 am
Full Name: Paul
Contact:

Re: Windows Failover Clusters with SQL AlwaysOn Availability Groups

Post by 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
Expert
Posts: 1943
Liked: 248 times
Joined: Dec 01, 2016 3:49 pm
Full Name: Dmitry Grinev
Location: St.Petersburg
Contact:

Re: Microsoft SQL AlwaysOn Availability Groups

Post by 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
Expert
Posts: 147
Liked: 8 times
Joined: May 22, 2015 7:16 am
Full Name: Paul
Contact:

Re: Microsoft SQL AlwaysOn Availability Groups

Post by 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
Veeam Software
Posts: 19187
Liked: 1717 times
Joined: Jul 11, 2011 10:22 am
Full Name: Alexander Fogelson
Contact:

Re: Backing up Always On MSSQL with Veeam

Post by foggy »

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

stewsie
Expert
Posts: 147
Liked: 8 times
Joined: May 22, 2015 7:16 am
Full Name: Paul
Contact:

Re: Backing up Always On MSSQL with Veeam

Post by 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
Product Manager
Posts: 17745
Liked: 1612 times
Joined: Oct 26, 2012 3:28 pm
Full Name: Vladimir Eremin
Contact:

Re: Backing up Always On MSSQL with Veeam

Post by veremin »

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

stewsie
Expert
Posts: 147
Liked: 8 times
Joined: May 22, 2015 7:16 am
Full Name: Paul
Contact:

Re: Backing up Always On MSSQL with Veeam

Post by stewsie »

I will investigate this. Thank you

Post Reply

Who is online

Users browsing this forum: Google [Bot] and 28 guests