Backing up Always On MSSQL with Veeam

Availability for the Always-On Enterprise

Re: Backing up Always On MSSQL with Veeam

Veeam Logoby sukybains » Wed 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?
sukybains
Veeam ProPartner
 
Posts: 6
Liked: never
Joined: Wed Apr 22, 2015 5:57 pm
Full Name: Suky Bains

Re: Backing up Always On MSSQL with Veeam

Veeam Logoby lightsout » Wed May 20, 2015 9:27 pm

I've restored to the active node, and the cluster has taken care of itself automatically.
lightsout
Expert
 
Posts: 186
Liked: 47 times
Joined: Thu Apr 10, 2014 4:13 pm

Re: Backing up Always On MSSQL with Veeam

Veeam Logoby sukybains » Wed May 20, 2015 10:17 pm

thanks for a prompt response lightsout !!!!!!
sukybains
Veeam ProPartner
 
Posts: 6
Liked: never
Joined: Wed Apr 22, 2015 5:57 pm
Full Name: Suky Bains

Re: Backing up Always On MSSQL with Veeam

Veeam Logoby foggy » Mon 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.
foggy
Veeam Software
 
Posts: 14904
Liked: 1096 times
Joined: Mon Jul 11, 2011 10:22 am
Full Name: Alexander Fogelson

Re: Backing up Always On MSSQL with Veeam

Veeam Logoby JaYaKaAzZ » Thu May 28, 2015 10:02 am

Thank you Alexander
JaYaKaAzZ
Influencer
 
Posts: 24
Liked: 4 times
Joined: Mon Feb 03, 2014 8:01 am
Full Name: Artur Schneider

Re: Backing up Always On MSSQL with Veeam

Veeam Logoby DeaconZ » Wed 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.
DeaconZ
Influencer
 
Posts: 14
Liked: 3 times
Joined: Fri Jul 09, 2010 7:39 pm

Re: Backing up Always On MSSQL with Veeam

Veeam Logoby foggy » Wed Sep 16, 2015 4:07 pm

Yes, those are created automatically, once you enable logs backup in the job settings.
foggy
Veeam Software
 
Posts: 14904
Liked: 1096 times
Joined: Mon Jul 11, 2011 10:22 am
Full Name: Alexander Fogelson

Re: Backing up Always On MSSQL with Veeam

Veeam Logoby DeaconZ » Wed 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?
DeaconZ
Influencer
 
Posts: 14
Liked: 3 times
Joined: Fri Jul 09, 2010 7:39 pm

Re: Backing up Always On MSSQL with Veeam

Veeam Logoby foggy » Wed Sep 16, 2015 5:15 pm

You should set the 'Do not truncate' setting for these VMs.
foggy
Veeam Software
 
Posts: 14904
Liked: 1096 times
Joined: Mon Jul 11, 2011 10:22 am
Full Name: Alexander Fogelson

Re: Backing up Always On MSSQL with Veeam

Veeam Logoby DeaconZ » Thu 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.
DeaconZ
Influencer
 
Posts: 14
Liked: 3 times
Joined: Fri Jul 09, 2010 7:39 pm

Re: Backing up Always On MSSQL with Veeam

Veeam Logoby foggy » Thu 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.
foggy
Veeam Software
 
Posts: 14904
Liked: 1096 times
Joined: Mon Jul 11, 2011 10:22 am
Full Name: Alexander Fogelson

Re: Backing up Always On MSSQL with Veeam

Veeam Logoby fernando.laguna » Wed 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.
fernando.laguna
Veeam Software
 
Posts: 3
Liked: never
Joined: Mon Nov 09, 2015 11:44 am
Full Name: Luis Fernando Laguna

Re: Backing up Always On MSSQL with Veeam

Veeam Logoby v.Eremin » Thu 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.
v.Eremin
Veeam Software
 
Posts: 13433
Liked: 987 times
Joined: Fri Oct 26, 2012 3:28 pm
Full Name: Vladimir Eremin

Re: Backing up Always On MSSQL with Veeam

Veeam Logoby johannesk » Mon 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…”
johannesk
Enthusiast
 
Posts: 26
Liked: 3 times
Joined: Tue Jan 19, 2016 1:28 pm
Full Name: Jóhannes Karl Karlsson

[MERGED] Veeam and Always On Clusters

Veeam Logoby Havgon » Mon 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
Havgon
Novice
 
Posts: 5
Liked: 1 time
Joined: Fri Jun 17, 2016 1:19 pm
Full Name: Javier Gonzalez

PreviousNext

Return to Veeam Backup & Replication



Who is online

Users browsing this forum: Google Feedfetcher and 96 guests