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 » May 20, 2015 7:33 pm

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: 218
Liked: 58 times
Joined: Apr 10, 2014 4:13 pm
Contact:

Re: Backing up Always On MSSQL with Veeam

Post by lightsout » May 20, 2015 9:27 pm

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 » May 20, 2015 10:17 pm

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

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

Re: Backing up Always On MSSQL with Veeam

Post by foggy » May 25, 2015 12:16 pm 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 » May 28, 2015 10:02 am

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 » Sep 16, 2015 4:02 pm

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

Re: Backing up Always On MSSQL with Veeam

Post by foggy » Sep 16, 2015 4:07 pm

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 » Sep 16, 2015 5:06 pm

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

Re: Backing up Always On MSSQL with Veeam

Post by foggy » Sep 16, 2015 5:15 pm

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 » Sep 24, 2015 12:48 pm

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

Re: Backing up Always On MSSQL with Veeam

Post by foggy » Sep 24, 2015 2:53 pm

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 » Apr 20, 2016 6:40 pm

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: 17077
Liked: 1475 times
Joined: Oct 26, 2012 3:28 pm
Full Name: Vladimir Eremin
Contact:

Re: Backing up Always On MSSQL with Veeam

Post by veremin » Apr 21, 2016 10:04 am

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

johannesk
Enthusiast
Posts: 59
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 » May 30, 2016 10:16 am 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 » May 22, 2017 2:36 pm

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

Re: Backing up Always On MSSQL with Veeam

Post by foggy » May 22, 2017 2:56 pm

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 » May 23, 2017 1:30 pm

Thank you

johannesk
Enthusiast
Posts: 59
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 » Nov 30, 2017 9:32 am

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: 17077
Liked: 1475 times
Joined: Oct 26, 2012 3:28 pm
Full Name: Vladimir Eremin
Contact:

Re: Backing up Always On MSSQL with Veeam

Post by veremin » Nov 30, 2017 10:57 am

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

johannesk
Enthusiast
Posts: 59
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 » Nov 30, 2017 11:13 am 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: 18458
Liked: 1589 times
Joined: Jul 11, 2011 10:22 am
Full Name: Alexander Fogelson
Contact:

Re: Backing up Always On MSSQL with Veeam

Post by foggy » Dec 11, 2017 4:17 pm 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: 133
Liked: 8 times
Joined: May 22, 2015 7:16 am
Full Name: Paul
Contact:

[MERGED] Microsoft SQL AlwaysOn Availability Groups

Post by stewsie » Mar 07, 2019 10:57 am

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
Veeam Software
Posts: 1819
Liked: 230 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 » Mar 07, 2019 11:06 am

Hi Paul,

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

stewsie
Expert
Posts: 133
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 » Mar 07, 2019 2:59 pm

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
Veeam Software
Posts: 1819
Liked: 230 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 » Mar 07, 2019 3:48 pm

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: 133
Liked: 8 times
Joined: May 22, 2015 7:16 am
Full Name: Paul
Contact:

Re: Microsoft SQL AlwaysOn Availability Groups

Post by stewsie » Apr 11, 2019 2:06 pm

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

Re: Backing up Always On MSSQL with Veeam

Post by foggy » Apr 12, 2019 3:15 pm

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

stewsie
Expert
Posts: 133
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 » Apr 15, 2019 6:48 am

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: 17077
Liked: 1475 times
Joined: Oct 26, 2012 3:28 pm
Full Name: Vladimir Eremin
Contact:

Re: Backing up Always On MSSQL with Veeam

Post by veremin » Apr 15, 2019 6:05 pm

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

stewsie
Expert
Posts: 133
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 » Apr 17, 2019 8:56 am

I will investigate this. Thank you

Post Reply

Who is online

Users browsing this forum: anthonyspiteri79, Google [Bot] and 21 guests