vCenter server snapshot data consistancy of SQL server database

[Sharantyr3]

Hi,

We are running vCenter 4.1 on 2003 R2 64bits and I was wondering how to take a valid snapshot of the VM.

In fact it is not "vCenter" specific, as it applies to any VM running Microsoft SQL Server, but vCenter is my problem atm.

We use vmware data recovery to backup VMs, and I'm wondering, since vdr take "disk quiesced" snapshots, if the consistancy of the SQL database is respected ?

I tried on our live test platform a "net stop SQL Server (instance)" during a vmotion, displaying graphs, etc. Everything is just like "paused" until I do a "net start".

So my questions are :

- Do I need to take care of data consistancy of my SQL databases when doing a quiesced disk snapshot ?

- Is it ok to "net stop" in a pre-freeze script and "net start" in "post-thaw" script ? (aka is net stop sending a "flush" message to sql server ?)

- How do you guys perform database backup in real time without interrupt of vCenter service ?

Thanks

[idle-jam]

the vmware tools comes with vss driver, so that the snapshot process from vmware would be able to make use of the VSS capability ..

[Sharantyr3]

"would be"

This is not the kind of answer I'm looking for.

In case of disaster, I don't want to rely on "it is usually said that..." and "most people believes that...".

I know about VSS, and you forgot to mention that VSS has pre-requisities to work. What I'm looking for is "What do YOU do" and "How do you trust these backups" ?

[a_p_]

For small installations with SQL Express - depending on the databases - I usually only run the image backup (VDR, vRanger, Veeam, ...). For "full" SQL servers, I always run a agent based SQL backup, either through the SQL agent or any backup software capable to handle SQL server. Not only for data consistency, but also to take care of the transaction logs.

André

[Sharantyr3]

Hi,

Yes I think it's safer to make maintenance plans and export the sql dump, but if vdr snapshots were consistent I'd stick with it for simplicity and dedup.

Btw looking at the event log of windows guest when I make a quiesced snapshot, I see that SQL Server send "I am frozen" messages, so I think it is ok ?

[AndreTheGiant]

Also SQL Express is VSS compliant... so VMware snapshot could be consistent.

But, IMHO I prefer add some backup&maintance script and make a dump

Andre