Comprehensive data protection for all workloads
Post Reply
mdornfeld
Expert
Posts: 125
Liked: 3 times
Joined: Mar 23, 2009 4:44 pm
Full Name: Matt
Contact:

Veeam VSS freeze affecting SQL

Post by mdornfeld »

We have a very busy MS SQL server and have been doing backups during the day using Veeam VSS on Veeam 4.0. During the time that the backup starts and stops, our develop guys are saying that we're dropping connections. This server does have 3 "hard drives", and I am using Safe Snapshot Removal the default 100 MB setting. The job does complete successfully with no warning messages.

Here is the Windows log from the SQL Server for the backup startup:
08:01:04 AM - The Volume Shadow Copy service entered the running state.
08:01:04 AM - The VeeamVssSupport service was successfully sent a start control.
08:01:04 AM - The VeeamVssSupport service entered the running state.
08:01:08 AM - The Microsoft Software Shadow Copy Provider service was successfully sent a start control.
08:01:08 AM - The Microsoft Software Shadow Copy Provider service entered the running state.
08:01:35 AM - The VeeamVssSupport service was successfully sent a stop control.
08:01:35 AM - The VeeamVssSupport service entered the stopped state.
08:04:34 AM - The Volume Shadow Copy service entered the stopped state.
08:07:34 AM - The Microsoft Software Shadow Copy Provider service entered the stopped state.
There are no log entries entered for backup completion.

Here is what virtualcenter is reporting is happening during the backup event
11/18/2009 8:01:01 AM - User logged event: Source: Veeam Backup Action: Job "wksql01" Operation: Started Status: Success ID: 08e74771-ac6a-41bf-ba4f-5a7212a1fe0e Description:
11/18/2009 8:01:20 AM - Create virtual machine snapshot
11/18/2009 8:02:43 AM - Release disk lease
11/18/2009 9:29:57 AM - Release disk lease
11/18/2009 9:56:16 AM - Release disk lease
11/18/2009 9:57:17 AM - Create virtual machine snapshot
11/18/2009 9:57:22 AM -Remove snapshot
11/18/2009 9:58:50 AM - Remove snapshot
11/18/2009 9:59:34 AM - Reconfigure virtual machine
11/18/2009 9:59:35 AM - Reconfigured virtual machine
11/18/2009 9:59:38 AM User logged event: Source: Veeam Backup Action: Job "wksql01" Operation: Stopped Status: Success ID: 08e74771-ac6a-41bf-ba4f-5a7212a1fe0e Description:

I'm trying to verify (or understand) a few things:

During Startup:
1. Why am I dropping connections during startup of the backup. Is this due to VSS or the Snapshot creation, or both?
2. Based on my log above, how long am I technically dropping connections during the startup - 31 seconds?
3. Why don't those connections just queue somewhere using VSS? Is that part of VSS or how the app is written?

During Backup Completion
-Example error message: Exception Message: A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)

4. Why am I dropping connection during backup completion. My assumption is that because of the snapshots being merged.
5. Based on my logs above, can I tell how long I'm dropping connections upon backup completion.

Recommendations?
6. Is there anything I can do to minimize or eliminate these kinds of SQL drops while still using Veeam as the backup method?
7. What is the communities/Veeam's recommended method to achieve say hourly or bi-hourly backups of an SQL server? Is it Veeam, trans log backups, log shipping, active/passive?

Thank you all for your help
Gostev
Chief Product Officer
Posts: 31457
Liked: 6648 times
Joined: Jan 01, 2006 1:01 am
Location: Baar, Switzerland
Contact:

Re: Veeam VSS freeze affecting SQL

Post by Gostev »

Matt, our code simply asks Microsoft VSS to freeze the application, and the rest of the freeze process is handled by Microsoft SQL VSS writer logic. I am not sure why it is dropping connections, but I would expect slight delays in service for highly loaded SQL. My understanding is that during VSS freeze SQL attempts to serve existing transactions while not accepting new ones. When all current transactions are closed, freeze happens and snapshot is created. I would not expect VSS freeze to drop connections, but may be there is a bug with SQL VSS writer?

On backup completion, connections should not be dropped either. With ESX4, VMware ensures that VM freeze on last snapshot commit is a few seconds (I did extensive stress testing on this back in time). But please check if SQL VM has some other snapshots, as this is known to create long delays and VM freezes on snapshot commit.

I will think more on all this and also talk to devs tomorrow.
Post Reply

Who is online

Users browsing this forum: BartP, Google [Bot], Gostev, Kristina.Zalesakova, Semrush [Bot] and 147 guests