Backing up Always On MSSQL with Veeam

Availability for the Always-On Enterprise

Re: Backing up Always On MSSQL with Veeam

Veeam Logoby m.novelli » Tue Nov 25, 2014 1:18 pm

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
m.novelli
Veeam ProPartner
 
Posts: 299
Liked: 35 times
Joined: Tue Dec 29, 2009 12:48 pm
Location: Asti - Italy
Full Name: Marco Novelli

Re: Backing up Always On MSSQL with Veeam

Veeam Logoby igarcia1 » Tue Nov 25, 2014 1:49 pm

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.
igarcia1
Influencer
 
Posts: 12
Liked: 4 times
Joined: Mon Feb 10, 2014 5:19 pm

Re: Backing up Always On MSSQL with Veeam

Veeam Logoby sjlarsen » Wed Nov 26, 2014 9:28 am

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.
sjlarsen
Lurker
 
Posts: 2
Liked: never
Joined: Fri Nov 21, 2014 8:44 am
Full Name: Simon Juul Larsen

Re: Backing up Always On MSSQL with Veeam

Veeam Logoby mcwill » Wed Nov 26, 2014 9:51 am

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
mcwill
Enthusiast
 
Posts: 64
Liked: 10 times
Joined: Sat Jan 16, 2010 9:47 am
Full Name: Iain McWilliams

Re: Backing up Always On MSSQL with Veeam

Veeam Logoby foggy » Wed Nov 26, 2014 12:14 pm

igarcia1 wrote: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.

igarcia1 wrote: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.
foggy
Veeam Software
 
Posts: 14560
Liked: 1060 times
Joined: Mon Jul 11, 2011 10:22 am
Full Name: Alexander Fogelson

Re: Backing up Always On MSSQL with Veeam

Veeam Logoby igarcia1 » Mon Jan 12, 2015 4:56 pm

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
Influencer
 
Posts: 12
Liked: 4 times
Joined: Mon Feb 10, 2014 5:19 pm

Re: Backing up Always On MSSQL with Veeam

Veeam Logoby igarcia1 » Mon Jan 12, 2015 5:46 pm 1 person likes this post

Found our gateway security services blocking the data transfer across our vpn tunnel so that explains the connection forcibly closing.
igarcia1
Influencer
 
Posts: 12
Liked: 4 times
Joined: Mon Feb 10, 2014 5:19 pm

Re: Backing up Always On MSSQL with Veeam

Veeam Logoby igarcia1 » Mon Jan 12, 2015 10:25 pm 3 people like this post

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.
igarcia1
Influencer
 
Posts: 12
Liked: 4 times
Joined: Mon Feb 10, 2014 5:19 pm

[MERGED] AlwaysOn Availability Group - backup command failed

Veeam Logoby aetius80 » Tue Jan 20, 2015 8:16 pm

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
aetius80
Novice
 
Posts: 4
Liked: never
Joined: Thu May 29, 2014 2:55 pm
Full Name: aetius mailman

Re: AlwaysOn Availability Group - backup command failed to

Veeam Logoby dellock6 » Wed Jan 21, 2015 10:33 am

Hi,
the best advice I can give you is to upgrade to version 8, that introduced specific support for AlwaysOn SQL clusters.
Luca Dell'Oca
EMEA Cloud Architect @ Veeam Software

@dellock6
http://www.virtualtothecore.com
vExpert 2011-2012-2013-2014-2015-2016
Veeam VMCE #1
dellock6
Veeam Software
 
Posts: 4989
Liked: 1308 times
Joined: Sun Jul 26, 2009 3:39 pm
Location: Varese, Italy
Full Name: Luca Dell'Oca

Re: Backing up Always On MSSQL with Veeam

Veeam Logoby foggy » Wed Jan 21, 2015 11:40 am

And here's more details on AlwaysOn support in v8.
foggy
Veeam Software
 
Posts: 14560
Liked: 1060 times
Joined: Mon Jul 11, 2011 10:22 am
Full Name: Alexander Fogelson

Re: Backing up Always On MSSQL with Veeam

Veeam Logoby nholman » Tue Mar 10, 2015 8:55 pm

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.
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 » Tue Mar 10, 2015 8:57 pm 2 people like this post

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-replication-f2/sql-server-2012-sp2-sqlvdi-error-t23928.html

Note, CU4 is out now.
lightsout
Expert
 
Posts: 183
Liked: 47 times
Joined: Thu Apr 10, 2014 4:13 pm

Re: Backing up Always On MSSQL with Veeam

Veeam Logoby nholman » Tue Mar 10, 2015 9:05 pm

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.
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 » Tue Mar 10, 2015 9:17 pm 2 people like this post

Its Microsoft, you can never be sure. :)

With SP2 I had periodic failures of snapshotting my AO, but it went away with CU3 entirely.
lightsout
Expert
 
Posts: 183
Liked: 47 times
Joined: Thu Apr 10, 2014 4:13 pm

PreviousNext

Return to Veeam Backup & Replication



Who is online

Users browsing this forum: Google [Bot] and 16 guests