Availability for the Always-On Enterprise
Post Reply
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: 208
Liked: 54 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: 16932
Liked: 1377 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: 16932
Liked: 1377 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: 16932
Liked: 1377 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: 16932
Liked: 1377 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.

v.Eremin
Veeam Software
Posts: 15209
Liked: 1146 times
Joined: Oct 26, 2012 3:28 pm
Full Name: Vladimir Eremin
Contact:

Re: Backing up Always On MSSQL with Veeam

Post by v.Eremin » 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: 53
Liked: 9 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: 5
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

Post Reply

Who is online

Users browsing this forum: Google [Bot], Majestic-12 [Bot] and 36 guests