VMWare Data Recovery and SQL Server.

[SteveO86]

I've heard from some people that it is not recommended to backup an SQL using VMWare Data Recovery due to just how fast SQL works/updates.

Is this true, or is it really nothing to worry about?

[rogard]

In the same way that it better to backup exchange with a tool that is capable of "seeing mailboxes" the same is true with VDR.

Its great for files, but if you have an active enviroment it might be better to have an SQL aware tool that completements VDR

[SteveO86]

Makes sense, thank you for the clarification.

[admin]

I do want to chime in - hopefull, this is of value.

The VSS support that VMware provides today for both Windows 2003 and 2008 (the various permuatations) provide application consistent quiesced snapshots. As part of providing application consistent quiesced snapshots, VSS will tell the application to ensure that there are no transaction in transit before "stunning" the application so that the snapshot can be taken.

As you know, SQL stores data is the database and transaction logs - with the transaction log being the first place where the data is written. When the stun is done and the snapshot taken, both the database and the transaction logs will be in application consistent state. Hypothetically, if you loose the VM and need to restore the VM from this backup/snapshot, the SQL data will be application consistent.

What the VMware VSS implementation does not do is truncate the transaction logs.

Depending on the expectation owner of the application, some are fine with manually truncating the logs inside the app/SQL (or running a script). Others application owners expect the backup operation to automatically perform this as part of the backup operation.

[Frank_Poelert]

The SQL aware backup tool can be the SQL Server itself!

This is the way I have set up the SQL backups:

I use the SQL agent to backup the databases to disk, full backup once a week and transaction logs every day (every hour if you like).

This allows for point in time recovery of a corrupt database.

The backup file are deleted when they are more than a week old.

I use VDR to backup the VM.

If I need older backup files to restore the database, I start FLR, select the desired restore point and pick up the backup files I need. After that it is quite easy to restore the database to the old state.

Cheers,

Frank

[dsoltesz]

We have set our exchange/sql servers to perform circular logging. This way we do not need to truncate them. This allows us to restore from our last backup, but not run logs to the current moment.

If you think about it, logs were more created for the physical environment, where a data drive would fail and you still have the logs on another disk. Now that we use VMDKs on a SAN, the likelyhood that just the data drive and not the log drive would be lost is minimal.

This also speeds up backups, as the log files are not as big.

Here is how to do it in Exchange 2000/2003 - http://support.microsoft.com/kb/314605