Backing up Always On MSSQL with Veeam

Availability for the Always-On Enterprise

Re: Backing up Always On MSSQL with Veeam

Veeam Logoby nholman » Sat Mar 14, 2015 9:11 am

hi everyone

with Always on am i right in thinking that wehn i add my members or nodes into the job (of which i have 2) on the application aware processing section does one node need to be set to process the transaction logs and the other node be set to copy only transasction logs?

Im having a problem where the passive node ALWAYS fails to freeze VSS if both are set to do process the logs - is this by design? the lady doesn't seem to have the answer and has directed me to permissions..this isn't permissions
nholman
Enthusiast
 
Posts: 57
Liked: 5 times
Joined: Wed Nov 28, 2012 10:23 am
Full Name: Nick Holman

Re: Backing up Always On MSSQL with Veeam

Veeam Logoby nholman » Sat Mar 14, 2015 10:21 am

hello again

another issue im seeing with backing up availability groups

it seems that when backing up my 2 nodes , when the primary has backed up the availability group fails over and the secondary becomes the primary! this is not good and throws my job settings out of sync

has anyone else seen this and know what the cause might be?
nholman
Enthusiast
 
Posts: 57
Liked: 5 times
Joined: Wed Nov 28, 2012 10:23 am
Full Name: Nick Holman

Re: Backing up Always On MSSQL with Veeam

Veeam Logoby lightsout » Sat Mar 14, 2015 4:06 pm

To answer your questions:

1. I believe only the primary can truncate the logs. I only backup one of my hosts, and I make sure it is the primary. Veeam support advised me not to backup both SQL nodes with the same job.

2. Yes, the backups can cause clustering failovers. It is consistent with the behaviour with Exchange.

http://www.veeam.com/kb1744

Those commands don't work on Windows 2012, so you need to use PowerShell. So running this on one cluster node fixed it for me.

Code: Select all
(get-cluster).samesubnetthreshold
(get-cluster).samesubnetthreshold=10
(get-cluster).samesubnetthreshold

(get-cluster).crosssubnetthreshold
(get-cluster).crosssubnetthreshold=10
(get-cluster).crosssubnetthreshold

(get-cluster).crosssubnetdelay
(get-cluster).crosssubnetdelay=4000
(get-cluster).crosssubnetdelay

(get-cluster).samesubnetdelay
(get-cluster).samesubnetdelay=2000
(get-cluster).samesubnetdelay


This display the current value, changes it, and then displays it again. This is constant with the Veeam KB I mentioned for Exchange, and is essentially doubling all of these timeouts so the snapshot "stun" doesn't cause a fail-over.
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 nholman » Sat Mar 14, 2015 5:06 pm

thats great thanks - ill look into this in more detail now.
im glad its not just me thats been struggling with SQL always on. seems a great a feature but getting the thing backed up is tedious to say the least.
nholman
Enthusiast
 
Posts: 57
Liked: 5 times
Joined: Wed Nov 28, 2012 10:23 am
Full Name: Nick Holman

Re: Backing up Always On MSSQL with Veeam

Veeam Logoby alanbolte » Sat Mar 14, 2015 6:19 pm

Nick:

sjlarsen wrote:
Gostev wrote:v8 has added full support for SQL Always On (both at backup and restore). Please, open a support case.

Thanks! Opened a case and got it solved.
Turns out that prior to v8 backup of SQL servers were run under the "SYSTEM" account, whereas in v8 it uses the credentials specified under "Guest processing". I gave that account SYSADMIN on the SQL servers in question, and the backup was successful.


You shouldn't need to manually set the passive nodes to copy-only; we will do that automatically if guest processing is working correctly. At present our Always On group detection can fail silently if you haven't added the guest processing credentials as SYSADMIN. This will be fixed.
alanbolte
Expert
 
Posts: 635
Liked: 172 times
Joined: Mon Jun 18, 2012 8:58 pm
Full Name: Alan Bolte

Re: Backing up Always On MSSQL with Veeam

Veeam Logoby nholman » Sat Mar 14, 2015 9:05 pm

hi Alan

that's a different message to what the support lady at veeam has said.

when i leave both set to process transactions one always falis as sure as day and night. Veeams resolution was set one to copy only to stop the vss freeze error on one node

Running version 8 - it doesn't do what you state.

forgot to to say the account running veeam has sysadmin rights the member servers.
nholman
Enthusiast
 
Posts: 57
Liked: 5 times
Joined: Wed Nov 28, 2012 10:23 am
Full Name: Nick Holman

Re: Backing up Always On MSSQL with Veeam

Veeam Logoby nholman » Sat Mar 14, 2015 9:11 pm

alan
case number is Case # 00826087
nholman
Enthusiast
 
Posts: 57
Liked: 5 times
Joined: Wed Nov 28, 2012 10:23 am
Full Name: Nick Holman

Re: Backing up Always On MSSQL with Veeam

Veeam Logoby alanbolte » Sun Mar 15, 2015 4:17 am

Nick,

I won't get into details because we don't provide direct technical support through the forums, but it's not clear to me from the case notes whether you gave the account sysadmin rights in the SQL instances before or after the case started. It is clear that at the beginning of the case the login to SQL was failing. If you have made any changes, provide Support with additional logging to verify that the login is no longer failing. If the current Support engineer is unable to help identify why the login fails, you can request that they escalate the case.
alanbolte
Expert
 
Posts: 635
Liked: 172 times
Joined: Mon Jun 18, 2012 8:58 pm
Full Name: Alan Bolte

Re: Backing up Always On MSSQL with Veeam

Veeam Logoby Gostev » Mon Mar 16, 2015 1:30 pm

I've confirmed with R&D that globally, there are no known issues backing up active or passive nodes, so this will need to be investigated through support. Thanks!
Gostev
Veeam Software
 
Posts: 21442
Liked: 2362 times
Joined: Sun Jan 01, 2006 1:01 am
Location: Baar, Switzerland

Re: Backing up Always On MSSQL with Veeam

Veeam Logoby JaYaKaAzZ » Fri May 08, 2015 8:07 am

So 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 ??
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 foggy » Fri May 08, 2015 1:02 pm

Correct.
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 » Wed May 13, 2015 8:00 am

Alexander u were right.

I added my 2 Always ON Nodes to Backup Job and did same Setting ob both -> Transaction Log Backup every 15 Minutes

On the first Server he is making the log backup ( in my case its just not working case is opened) and on the other Server he says : "Skipping databases to be backed up from another SQL Server node: ....."

So for everyone with same Question, backup from Primary or secondary ? Just put both in to the Job make same Settings and Veeam will do the rest....

unfortunately my Transaction log backup is still not working ... but i will find the reason why :)
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 JaYaKaAzZ » Wed May 13, 2015 12:09 pm

Edit:

I have same Issue like nholman....

When Backup starts backing up Primary Server it makes a Failover... When i start the full Backup there are a lot of Failover Errors in the Eventlog....

Strange behaviour.. I made a Call

Call ID #00916797
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 foggy » Wed May 13, 2015 12:52 pm

Artur, have you tried to increase the timeouts, as mentioned above?
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 » Tue May 19, 2015 7:27 pm

@ foggy

"Veeam is not responsible for any problems encountered by making any of the suggested changes in these systems. Please refer to their respective support organizations for more detail on these settings."

These changes are not in accordance with best practice and if you have a customer in which it must be matched to the smallest detail perfectly , then that's not an option for this .

Fact is , the VSS snapshot to be backed up with the SQL nodes , causes a failover .
In Webinar Veeam to SQL Always On , you are advised to add all nodes to the backup and Veeam does the rest. This works so unfortunately not.
The Veeam colleague working on my Call #00916797, advised me to backup the secondary node . But in a failover scenario the customer had to switch the Node in the Job manualy again.
And that is really very impractical.

And to add, that the colleague has advised me to use Copy -only , but this is in contradiction statement to Alan Bolte .

I see here on this issue still many question marks. And it looks like they have not yet clarified internally at Veeam . It would therefore be very helpful, to have a official statement how best to proceed with SQL Always On .

@ anton gostev

you said that globaly there are no known issues, i can say that globaly there is no best practice to backup SQL Always On with Veeam ??? :wink:
JaYaKaAzZ
Influencer
 
Posts: 24
Liked: 4 times
Joined: Mon Feb 03, 2014 8:01 am
Full Name: Artur Schneider

PreviousNext

Return to Veeam Backup & Replication



Who is online

Users browsing this forum: Google Feedfetcher and 98 guests