Application consistent SQL backups - Best solution?

[pigbloke]

We have 3 vSphere ESXi servers currently running approximately 40 VMs. They are currently being backed up using VDR which is doing a good job for us in all but 1 respect - it's not providing application consistent SQL backups on our 3 SQL servers.

To get around the problem our SD/DBA guys are running SQL backups to an additional vmdk attached to the SQL servers and VDR is backing up this vmdk only on the SQL servers. They are running a full SQL backup every night and transaction log backups every 15 minutes and say they can restore to any point in time using the log backups.

Although we're confident we can restore from any db corruption or DR situation, it would involve 2 separate teams and 2 separate restore procedures. Ideally we'd like to implement a single backup and recovery solution that will back up all our VMs, including transactionally consistent SQL backups so that we could relieve the SD/DBA guys of all backup and restore responsibilities.

We would need restore points on our SQL servers every 15 minutes to replicate the point in time restores we can achieve at the moment but without having to restore all the transaction logs.

I have just downloaded Veeam and am about to start a trial but I was wondering if people had experienced the same problem and addressed it with a VM image level backup solution such as Veeam or vRanger. Is it possible to achieve our requirements?

[Stu_McHugh]

I'm not sure if this help but I'm doing a similar thing to you. I'm trialing vDR at the moment to backup VM however I am using traditional backup methods (BAckupExec) to backup the SQL db's and Exchange IS.

Stuart

-

---

Please award points to any useful answer.

[pigbloke]

Stuart, thanks for your input.

As I mentioned, at present we have backups of SQL albeit using native SQL backups. Where we want to get to is 1 backup product backing up everything mainly to simplify restores and DR.

I need to prove to our DBAs that whatever we implement can do the same point in time database restores as the SQL backups and that the data will be transactionally consistent. From what I have read, Veeam should provide this but I need to show this to be true in our environment.

I've read that the problems we are having with VDR are due to VMware tools only using the file system VSS writer so although it will quiesce the file system and backup the volume shadow copy, it won't inform SQL it's doing it so you can't get application consistent restores, only crash consistent. The same article says that Veeam uses it's own VSS which tells VSS aware apps it's backing them up, so restores will be app consistent.

I have started an evaluation of Veeam this morning so hopefully I should have some clear data on this in a couple of weeks, but if anyone has any experience of this or useful links I would be most grateful.

Thanks

Nick

[Gostev]

Nick, your understanding is 100% correct.

There is great blog post about VSS and VMware (nevermind the title), this might be helpful as well:

[pigbloke]

Thanks Gostev

That's one of the articles I got my info from - great article and provides a clear explanation of the workings involved.

I'm currently working with our DBAs to get a couple of test DB servers running, 1 W2K3 with SQL 2000 and 1 W2K8 with SQL 2008. We'll then run some test backups and restores using Veeam to see if we can get get the results we require.

Are there any gotchas I should be aware of or is it really as simple as just scheduling our backups then performing test restores when required?

Many thanks

Nick

[Gostev]

Hi Nick, no gotchas - it just works! In all cases, if you need any help or have some further questions specific to Veeam, feel free to post on Veeam community forums at

[pigbloke]

Thanks Gostev, you've confirmed what I thought (and hoped) Veeam could do for us. All down to testing now I guess.

I'll leave this post open for a few days in case anyone wants further discussion on this topic.

Cheers

Nick