Application aware image processing and vmware snap logic

[adriand]

Hello,

Doing an MS SQL 2008 VM backup on vSphere 5.5 U1. Veeam version is 7.0.0.833.
VM was busy, so VSS could not quiesce SQL, hence backup failed. Nothing special here.
However Veeam took a VMware snapshot of the VM even if VSS could not quiesce the VM. One would think that VSS must first quiesce the VM and when SQL is "idle" to take the VMware snap.
On top of that since the VM is so busy, obviously VMware snapshot removal would be a problem = the VM can be stunned (= frozen) for quite a while. In our case this translated into SQL mirroring triggering a failover.

Why would Veeam backup take a VMware snap in this case?



Thanks,
Adrian

[Vitaliy S.]

Hi Adrian,

According to the screenshot your Guest OS has been freezed successfully, but this was the release guest operation that failed, thus you had a snapshot created for this VM.

Thanks!

[adriand]

Thanks Vitaliy.

It turns out that thing are a little bit more complicated. When mapping the exact time (SQL time is one hour off compared to Veeam) we saw that actually the log truncation breaks the mirror not the vmware snapshot removal (so mirror broke during the successful retried backup not depicted failure below); it happened twice so far. I opened a case with support to investigate.

Thanks,
Adrian

[veremin]

It turns out that thing are a little bit more complicated. When mapping the exact time (SQL time is one hour off compared to Veeam) we saw that actually the log truncation breaks the mirror not the vmware snapshot removal (so mirror broke during the successful retried backup not depicted failure below); it happened twice so far.

Can you elaborate on it a little bit? You mean that log truncation, not snapshot removal, resulted in "failed to freeze" error? Also, can you post your ticket number, so that, we can follow the case? Thanks.

[adriand]

Hello Eremin,

No, the initial post was a confusion because of diff time on SQL server (= email alert that mirroring was broken) and Veeam time.

So basically ocasionally during the time when log truncation is done while backup the principal, the sql mirror breaks and we get email alerts like (and our dev ping us back that customer's web servers go down temp):

Code: Select all

*** WARNING !!! *** Mirroring state changed.
Timestamp: 5/29/2014 9:41:14 PM
Machine Name: xxx
SQL server: xxx
Database: aspnetdb;
Mirroring state: SYNCHRONIZING;
Mirroring role: MIRROR;
Witness state: UNKNOWN;

Case no. 00576136. I uploaded both backup logs and email alert we get from SQL.

Ps. the initial post, that freeze error happened I think because the customer asked us for a certain backup time and we discovered that actually at that time he was doing some heavy maint jobs hence the VM was very busy. so we moved the backup to a diff time when the VM is not that busy.

Thanks,
Adrian

[Vitaliy S.]

Just wanted to add that there is an existing topic about SQL Server mirroring and backup job behavior: SQL backups causing SQL mirroring to stop for a few seconds

[adriand]

thanks guys. Our dev sent some SQL logs which I uploaded to support. Looks like some log backups failed.
And that the customer actually has some plans which may backup the logs too, which means we may overlap with log truncation. the joy of web hosting...
will see what support manages to find out.

the vmware snapshot removal appears to be a less issues these days in newer vsphere versions (due to "smaller" consolidation commits to say so); even if it takes a few hours we did not notice to take SQL out on any VMs we have I think.
on this scenario SQL mirroring timeout is 30 secs for failover.

Thanks,
Adrian

[Vitaliy S.]

And that the customer actually has some plans which may backup the logs too, which means we may overlap with log truncation. the joy of web hosting...

Yes, having two backup solution truncating the logs might mess up the backup jobs.

[adriand]

Yes, the "double" log truncation seemed to interfered with the mirroring state when the two log backups overlapped (on sql side was done every 30 mins). we disabled it on veeam backup job and also re-scheduled teh backup job to not overlap with any of the sql maint jobs.
So far things have been stable after doing that.

Thanks,
Adrian

[Vitaliy S.]

Adrian, thanks for the update. Keep in mind that, you need to disable AAIP option completely, unchecking "truncate logs" option in the backup job settings will still interfere with native backup made by SQL management studio.

[adriand]

Vitaliy, we have not had any issues with backup since. Veamm principal backup starts a little after that "native" log backups, so when Veeam calls VSS in theory there is nothing going on there. In practice, I guess we shall see

[Vitaliy S.]

Depends on what is your backup plan that you have with native tools. Run some recoveries to confirm that you have all the logs properly backed up with your 3rd party solution.

[m.novelli]

I guys, I'm approaching a new customer that is running SQL Server with mirroring enabled, ref. http://msdn.microsoft.com/en-us/library ... .105).aspx

The OS is Windows 2008 R2 fully patched and the SQL Server version is 2008 R2 with SP3

Is there any special care to backup the source SQL Server to not break the mirror? Should I disable the transaction log truncation in Veeam Backup settings?

Any advice would be great!

Thanks, Marco

[veremin]

Hi, Marco,

Your post has been merged into existing discussion. So, please, take a look at the answers provided above.

Thanks.

[m.novelli]

Thanks guys! I'll try

[a.fantini]

Hi everyone.

i'm looking forward to a best practices or step by step document >> backup a mirrored sql server with whitness Win 2008 r2
this database is the prodution Xendesktop Database.

so i have :

sql1 principal db
sql2 mirrored db
sql3 whitness

how i can configure a new backup with veeam 8?

i can't find a steb by step backup whitepaper.

can you help me?