VMware

This Question is Possibly Answered

1 "correct" answer available (10 pts) 2 "helpful" answers available (6 pts)
4 Replies Last post: Dec 4, 2007 8:11 AM by Argyle  

Using Snapshot manager for SQL 2005 server VM's posted: Nov 28, 2007 9:56 PM

Click to view mallah's profile Novice 16 posts since
Jun 28, 2006

I am running Microsoft SQL 2005 Ent Ed on ESX 3.0.1 as virtual machine. My question is if there are any issues with using Snapshot feature in ESX to take snapshot of the running SQL server. Do I have to shutdown SQL 2005 before I take snapshot ?

Thanks

Re: Using Snapshot manager for SQL 2005 server VM's

1. Nov 28, 2007 10:16 PM in response to: mallah
Click to view Dave.Mishchenko's profile Guru 8,943 posts since
Nov 15, 2005
You don't necessarily have to shut down the VM, but with just a simple snapshot the backup you take is a bit like pulling the power plug on the server. You'll get a good disk image, but you'll lose transactions in memory and just like with a hard power down there's always a chance of a database corruption. Shutting down the VM first would of course avoid the problem but that may not be an option for you if the DB server has to be up 24 x 7. If you want to avoid any change of corruption, then you might want to rely on another method for SQL backup (native file backups, agent based, etc). That way you'll have an extra safety net. Are you planning on using any backup software for this. Apps like vRanger will quiesce the file system and use VSS to quiesce SQL Server so that all writes get flushed out of memory and you should then have a crash consistant database. See this thread for a discussion on that. http://communities.vmware.com/message/741887

Re: Using Snapshot manager for SQL 2005 server VM's

2. Nov 28, 2007 11:05 PM in response to: mallah
Click to view jhanekom's profile Master 1,084 posts since
May 13, 2005
It's mentioned in the post referenced by Dave, but I'd like to stress that you might want to disable the LGTO_Sync driver for this VM if you're not stopping the SQL service during a snapshot. This is important, even if you're not backing up the disk the SQL database is on.

Not doing so has a tendency to cause SQL to take the database offline if there is a certain level of load at the time of the backup. The same holds true for other database-based systems such as Oracle and Exchange.

Re: Using Snapshot manager for SQL 2005 server VM's

4. Dec 4, 2007 8:13 AM in response to: mallah
Click to view Argyle's profile Hot Shot 86 posts since
Dec 29, 2006
If you are going to stop the SQL services anyway I would just shut down the entire VM instead and then backup the offline VM (assuming it is a dedicated SQL Server).

Note that if you have any applications like older VB or Java applications they often have constant connection to the SQL Server (the connection is not disconencted and connected on each request). If you stop/start the SQL service for such applications they will stop to function. They will think they still have a connection to the SQL Server but there is nothing in the other end and thus you have to implement restart procedures for the applications as well.

VMware Developer

SDKs, APIs, Videos, Learn and much more in the Developer community.

Learn More

Developer Sample Code

Increase your developer productivity with VMware API sample code.

Learn More

VMworld Sessions & Labs

Online access to the latest VMworld Sessions & Labs and online services.

Learn more

Purchase PSO Credits Online

Purchase credits to redeem training and consulting services online.

Buy Now

Community Hardware Software

View reported configurations or report your own.

Learn More

VMware vSphere

Come witness the next giant leap in virtualization.

Register Today

Communities