Backing up Always On MSSQL with Veeam

[m.novelli]

A little OT question: do you know how to properly backup a mirrored SQL Server?

OS is Windows 2008 R2 and SQL Server is 2008 R2 SP3. Should I disable truncation of logs on the master?

Any advice would be appreciated! Thanks

Marco

[igarcia1]

I should have mentioned that there are three availability groups. One hosted locally at each site on the two nodes, and then the third spanning both sites. We would still need to truncate logs at the second site for the databases that only exist there. Or is there an alternative?

I suppose if necessary we could simplify the configuration and span all three availability groups for sake of the backups.

[sjlarsen]

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.

[mcwill]

Ah that explains a problem we had.

Backups made under V7 worked correctly with the new SQL Explorer but those made with V8 were empty when viewed with SQL Explorer. As yourself, adding the guest processing account to the SQL server then solved the problem for future backups.

Interestingly the V8 backups didn't report any errors or warnings so the first we were aware of it was when attempting to restore a database and being presented with a blank screen. Luckily file explorer did work on the backup so we could pull a copy of the database the old fashioned way.

Well worth checking that Veeam is given access to all your SQL servers under V8.

Regards,
Iain

[foggy]

I should have mentioned that there are three availability groups. One hosted locally at each site on the two nodes, and then the third spanning both sites. We would still need to truncate logs at the second site for the databases that only exist there. Or is there an alternative?

No, with such configuration, you need to backup all nodes in a single job. Otherwise, logs will not be collected for databases that exist at the second site only.

I suppose if necessary we could simplify the configuration and span all three availability groups for sake of the backups.

Yes, this would allow to have different jobs for different sites.

[igarcia1]

It seems as though we're stuck on a SQL Availability Group backup problem. I'm still finding the documentation lacking and my questions to support have gone unanswered (ticket 00687887) which leads me to believe they are in the dark as well. This all started with the VSS unfreeze error which is still occurring. Since the previous post I've reconfigured the cluster so that each availability group spans all four nodes. I've also switched to using one job for all four nodes. Now the two remote nodes start backing up before failing with a new error: "existing connection was forcibly closed by the remote host". Although, the VSS Unfreeze error still occurs periodically.

Snapshot deletion causes the primary node to drop from the cluster, forcing a failover and a hiccup in both the vCenter and Veeam databases (both are in the availability group), potentially causing an issue if any other backups are running at the time. To avoid this, the backup preference for each availability group has been set to Secondary Only. I've also experimented with excluding the same two nodes on all availability groups. While Veeam does attempt to backup the secondary nodes first, it will continue to backup the primary node and the excluded nodes despite this setting. Unfortunately, the backup of the cluster only seems to succeed on the primary node.

Ultimately I suspect our configuration is to blame, but I can find nothing stating supported/unsupported configurations. Does Veeam have a problem with three availability groups using different primary nodes in one SQL cluster? Should I expect the backup to only succeed on the primary nodes? Why ignore the availability group backup preference? Should I remove the Veeam database from the availability group?

[igarcia1]

Found our gateway security services blocking the data transfer across our vpn tunnel so that explains the connection forcibly closing.

[igarcia1]

Things started smoothing out after fixing the security services issue. I rebooted the affected servers to reset the VSS error and was able to get 3 of the 4 nodes backed up. I then looked into our cluster adjusted the failover thresholds, which seems to have fixed the issue with snapshot deletion causing a failover. All this, along with using one backup job, seems to have fixed the problem. We're now getting 4/4 successes each time the job runs.

[aetius80]

Hi,
I have a SQL Server 2012 AlwaysOn Availability Group which uses Veeam 7 for backups. I'm using a 2 node Windows 2012 cluster with a file share witness.

- At 19:00 the primary node is backed up by Veeam using application aware image processing to truncate the logs.
- At 22:00 the secondary node is backed up using Veeam without application aware image processing.

On the primary node, I see application event log error "backup command failed to complete BACKUP LOG" <model/master/tempdb/YourDB> "Check the backup application log for detailed information".

On the secondary node, I see application event log error "The recovery LSN (45:6543:1) was identified with the ID 5.This is an informational message only. No user interaction is required"

Other than the backup related messages, the AlwaysON SQL cluster is fine - cluster validation checks pass as expected, no other errors are logged in the event log and failover works as expected.

I want to backup the active DB on my AAG and truncate the logs - ideally using Veeam, but if not, I'm happy to use an alternative.

Is there a best practice, recommended way of doing this?

Thanks

[dellock6]

Hi,
the best advice I can give you is to upgrade to version 8, that introduced specific support for AlwaysOn SQL clusters.

[foggy]

And here's more details on AlwaysOn support in v8.

[nholman]

is anyone else having as much trouble in backing up a 2 node SQL always on cluster?

I put both nodes in the veeam 8 job, first node backups - second always fails with a VSS error. I look at the vss writers on the second node and the SQL writer is in a failed state. I restart to clear the error and retry the backup - same error.

Code: Select all

Unable to release guest. Error: Unfreeze error: [Backup job failed. Cannot create a shadow copy of the volumes containing writer's data. A VSS critical writer has failed. Writer name: [SqlServerWriter]. Class ID: [{a65faa63-5ea8-4ebc-9dbd-a0c4db26912a}]. Instance ID: [{d7a383af-33c9-4b90-94ad-f85f6c22ffb6}]. Writer's state: [VSS_WS_FAILED_AT_PREPARE_SNAPSHOT]. Error code: [0x800423f4].]
Error: Unfreeze error: [Backup job failed. Cannot create a shadow copy of the volumes containing writer's data. A VSS critical writer has failed. Writer name: [SqlServerWriter]. Class ID: [{a65faa63-5ea8-4ebc-9dbd-a0c4db26912a}]. Instance ID: [{d7a383af-33c9-4b90-94ad-f85f6c22ffb6}]. Writer's state: [VSS_WS_FAILED_AT_PREPARE_SNAPSHOT]. Error code: [0x800423f4].]

Support seems to be taken a while to get back to me which means ive obviously stumped them also. the documentation around Veeam 8 and SQL AG is seriously lacking in my opinion.

[lightsout]

What version of SQL 2012 are you running? I had similar issues, until I applied SQL 2012 SP2 CU3.

http://forums.veeam.com/veeam-backup-re ... 23928.html

Note, CU4 is out now.

[nholman]

thanks for the tip. we are on SP1 on both servers.
I will get them updated but wouldn't it fail with the same error on both nodes ?

Just frustrating at the moment.

[lightsout]

Its Microsoft, you can never be sure.

With SP2 I had periodic failures of snapshotting my AO, but it went away with CU3 entirely.

[nholman]

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]

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?

[lightsout]

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.

[nholman]

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.

[alanbolte]

Nick:

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.

[nholman]

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]

alan
case number is Case # 00826087

[alanbolte]

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.

[Gostev]

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!

[JaYaKaAzZ]

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 ??

[foggy]

Correct.

[JaYaKaAzZ]

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]

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

[foggy]

Artur, have you tried to increase the timeouts, as mentioned above?

[JaYaKaAzZ]

@ 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 ???