VMware Cloud Community
jdoz_bw
Contributor
Contributor

VCB Snapshot crashed SQL server on GUEST

This is interesting. Today at 3:05 PM EST from my VCB ProxyHost I ran a vcbMounter.exe -t fullvm to copy a VM over the SAN. At the same time the SQL database on the VM guest crashed with the following events from the event log:

MSSQLSERVER 17053: LogWriter: Operating system error 1784 (The supplied user buffer is not valid for the requested operation encountered)

MSSQLSERVER 900: The log for database 'BESMgmt' is not available

MSSQLSERVER 9001: The log for database 'tempdb' is not available

What is happening here? I thought a Snapshot quiesces a VM's disk on the SAN and is not supposed to have an effect on the VM guest itself... Any thoughts or ideas are appreciated.

Reply
0 Kudos
23 Replies
dconvery
Champion
Champion

If you are running ESX 3.5U2 Go to add/remove programs select VMware Tools and make sure the VSS driver is added. It is NOT installed during an upgrade and MAY not install during a new install. If you are using anything previous to 3.5U2, you need to re-run the VMware Tools installer and select "Complete" instead of typical. This will install the drivers to quiesce I/O.

Dave

Dave Convery, VCDX-DCV #20 ** http://www.tech-tap.com ** http://twitter.com/dconvery ** "Careful. We don't want to learn from this." -Bill Watterson, "Calvin and Hobbes"
Reply
0 Kudos
rahul_bck
Contributor
Contributor

Iam a little curious here to know, if enabling VSS really helped, or if you are seeing this issue with SQL again?

Reply
0 Kudos
jdoz_bw
Contributor
Contributor

Not running U1 or U2 yet. However, the complete VMWare Tools is installed on this guest, including the Sync driver.

I thought by default when taking a snapshot the VM I/O is quiesced? That is the impression that I get from the documentation. Further reading the VCB docs, it says to run the pre-freeze and post-thaw scripts to quiesce and un-quiesce NTFS filesystems. It looks like the "vcbMounter.exe" command actually calls these scripts.

This makes me really nervous... I need to find a test SQL server to test on... Stay tuned.

Reply
0 Kudos
dconvery
Champion
Champion

Ditto.... I usually recommend to use SQL to do a maintenence job to back it up to a share, then back up the share. The VSS driver is fairly new and I have not had a chance to really look at it.

Dave Convery, VCDX-DCV #20 ** http://www.tech-tap.com ** http://twitter.com/dconvery ** "Careful. We don't want to learn from this." -Bill Watterson, "Calvin and Hobbes"
Reply
0 Kudos
ChrisDearden
Expert
Expert

the pre freeze and post thaw scripts would be used to stop and start the SQL services.

I'd get update 2 in there and use the VSS snapshots unless you want to stop your SQL services during the backups.

If this post has been useful , please consider awarding points. @chrisdearden http://jfvi.co.uk http://vsoup.net
Reply
0 Kudos
dconvery
Champion
Champion

You will need to create a pre-free and post-thaw script. This is just a simple batch file. It can contain "net stop mssql" and "net start mssql". This will stop the service during the quiesce operation. The service is not stopped during the entire backup process. I still recommend using a maint job to back up to a flat file. Your chances of recover are much better.

Dave Convery, VCDX-DCV #20 ** http://www.tech-tap.com ** http://twitter.com/dconvery ** "Careful. We don't want to learn from this." -Bill Watterson, "Calvin and Hobbes"
Reply
0 Kudos
jdoz_bw
Contributor
Contributor

I would prefer to not stop SQL, we do hotbackups which get backed up. I am trying to do full VM image backups for Disaster Recovery. I will get Update2 installed and do some more testing.

Thanks

Reply
0 Kudos
depping
Leadership
Leadership

I would prefer not to use snapshots on a database server or domain controller. to often one of these go haywire when the snapshot is removed or taken.

Duncan

My virtualisation blog:

If you find this information useful, please award points for "correct" or "helpful".

Reply
0 Kudos
jdoz_bw
Contributor
Contributor

I agree. I guess I assumed that Snapshot's were seamless and that the quiescing of I/O happened automagically behind the scenes...

Thanks

John

Reply
0 Kudos
depping
Leadership
Leadership

well the problem in this case is that the applications aren't snapshot aware...

Duncan

My virtualisation blog:

If you find this information useful, please award points for "correct" or "helpful".

Reply
0 Kudos
jdoz_bw
Contributor
Contributor

I kindof assumed a snapshot works like a VMotion where the IO and writes still go on and when the snapshot is done, the changes are merged together. Smiley Sad

I need to go find a KB from VMWare on all of this.

Reply
0 Kudos
dconvery
Champion
Champion

I am not sure if you are going to find a KB about how it works completely. It takes a snapshot of the disk. Before the snapshot, it quiesces I/O. This is where you are having issues. Can you take a normal snapshot? I suspect you cannot. Something must be up with the configuration. OK...here comes my shameless plug:

If you are going to VMworld, check out my breakout session: BC2497 VMware Consolidated Backup - Report from the Trenches

Dave

Dave Convery, VCDX-DCV #20 ** http://www.tech-tap.com ** http://twitter.com/dconvery ** "Careful. We don't want to learn from this." -Bill Watterson, "Calvin and Hobbes"
Reply
0 Kudos
Phil_White
Enthusiast
Enthusiast

My workaround for this is to simply set the VM to shut off two minutes prior to the backup and then I use VCB to backup the VM whilst powered off to avoid the issues with doing a hot backup of a database/DC.

Unfortunately this is not a possiblity for every one as some systems require absolutely zero downtime.

I have a whoozy of a question for ya....

Is it possible to just do a VCB backup of the C drive on a VM with multiple drives? For instance, my boss destroyed our C drive on our Exchange server so we had to rebuild it from scratch(minus the important files) last week. The VM has a C, D, and E drive. Is there not a way to just do a VMbackup of the C drive in case a mistake like this happens again? We already back up the D and E drive so it's not a concern to me.

Reply
0 Kudos
jdoz_bw
Contributor
Contributor

Yes, do a Full VM copy over the SAN or do a VM Mount and just backup, from your proxyhost, C:\mnt\vm_name\letters\C

You may have issues backing up the system state (registry) by backing up the flat files.

Reply
0 Kudos
dconvery
Champion
Champion

Fore a VMDK backup - you can use vcbExport to do it, but the recovery may fail because of lack of other disks.

Dave Convery, VCDX-DCV #20 ** http://www.tech-tap.com ** http://twitter.com/dconvery ** "Careful. We don't want to learn from this." -Bill Watterson, "Calvin and Hobbes"
Reply
0 Kudos
proden20
Hot Shot
Hot Shot

VCB crashes Oracle too. I don't back up SQL or Oracle with VCB anymore. Maybe its an issue with the # of transactions vs. how long it takes to recommit the snapshot. Whatever it is, I stay away Smiley Happy

Reply
0 Kudos
dconvery
Champion
Champion

Does it crash if you manually create a snapshot, or just when VCB calls one?

Dave Convery, VCDX-DCV #20 ** http://www.tech-tap.com ** http://twitter.com/dconvery ** "Careful. We don't want to learn from this." -Bill Watterson, "Calvin and Hobbes"
Reply
0 Kudos
jdoz_bw
Contributor
Contributor

VCB keeps the snapshot open for at least 10 minutes while the VM copies over the SAN. In the case of file level backup it keeps the snapshot open until you tell it to destroy itself. So I would think that it is just snapshots in general. VCB calls the snapshot technology just the same as one would click on a machine and do a Snapshot->Create snapshot

Reply
0 Kudos
BenConrad
Expert
Expert

There are several ways to snap/backup guests in ESX so this can get confusing:

1) Normal snapshot - the guest is not aware a snapshot was taken, the snapshot is "crash consistent", that is, if you restore from this snapshot the guest recovers in a similar manner as a power failure. This is not a great way to backup a busy database server or file server

2) 'Quiesced' snapshot apps (like VCB). VCB will use the vmware tools sync driver to pause I/O, this can and will cause issues with SQL/Exchange/Domain Controllers as the data doesn't get written to disk unbuffered in the manner the application expects

3) VSS aware vmware snapshots, I think you need 3.5 U2 tools for this to work. Your backup app actually starts/stops the VSS functionality in the guest so that the guest will quiesce the OS/apps and then it's safe for the backup to run.

Ben

Reply
0 Kudos