Transactional DB backups

[vmproteau]

We're transitioning to a snapshot based backup (Symantec Netbackup's implementation). Up until now we have loaded the backup agent on the VMs and backed it up like physical servers. For our DB servers, (primarily SQL) we don't use SQL specific backup agents and recommend a scheduled dump file which is regularly backed up.

I suspect we will want to continue this dump file practice but, I have read some about pre-freeze and post-thaw scripts that can make a snapshot based backup transactionally consistent.

(1) Anyone use these with respect to SQL or Oracle? What's been your experience?

(2) Have you verified the DB in a DR scenerio?

(3) What about the effect on the server/DB/application during the pre-freeze post-thaw interactions. I assume there is a connectivity or performance hit? I'm trying to provide guidance and get ahead of the inevitable questions before we attempt this.

[idle-jam]

the pre and post freeze script is only applicable for backup on the image level of the virtual machine, or the whole vmdk. that would make the backup of the database consistent. if you are backing up using agent level or by dump you do not have the worry as it would be identical on how the physical way of doing ..

[vmroyale]

Hello.

What version(s) of NetBackup and vSphere/vCenter are you using?

Good Luck!

[vmproteau]

Versions:

• vSphere vCenter 4.1 U1
  
• vSphere ESXi 4.1 U1
  
• Netbackup 7.0.1
  

I plan on continuing to recommend dump files but, I am specifically looking for the effects and/or success using these pre/post scripts. I'd like to transition away from the backup agent VM backup methodology we are using in favor of a snapshot/image VM backup.

If using these scripts actually produced a tranactionally consistent image of the DB and didn't have any determental effects on the server or DB function as backups were running, it would be time saving in DR scenerios.

[vmroyale]

My understanding is that vSphere 4.1 will deliver application-consistent quiescing when using Windows 2003 or Windows 2008 with certain caveats.  Basically, Windows 2003 uses a software snapshot provider and Windows 2008 uses a hardware snapshot provider.  This information and the caveats are both discussed in great detail in the "Designing Backup Solutions for VMware vSphere" technical note.

I personally use NetBackup 7 to backup many SQL Servers using the FlashBackup-Windows policy type, but I also keep a local .BAK file on the filesystem as an added precaution.  I have not tried to use the pre/post scripts, as my understanding here is that these would require SQL Server downtime and subsequent increased monitoring.  To me this seems like more work than it is worth.  If SQL has to be stopped, then wouldn't it be better to just create a .BAK prior to the backup running while SQL Server instead stays up?

As with all things backup, your mileage may vary and it is always a very good idea to test periodically, but you already know this.

[vmproteau]

Thanks for the link and I completely agree. I assumed it would require some SQL downtime and I don't want any part of that for the same reasons you mentioned. I'll always recommend a .BAK dump file despite any alternative means of backup. I'll have to read up on "FlashBackup-Windows policy type" or run it by my backup team. I'm not familiar with the policy.

[vmroyale]

Basically, FlashBackup-Windows policies allow you to take image-level backups of virtual machines (not just Windows guests, as the name somewhat misleads).  Another option to look into with this setup is the CBT functionality.  Depending on the volume of change in your servers, you may be able to use CBT to achieve more recovery points in much less time than a full backup would take.

[vmproteau]

I am not our backup engineer but, my assumption was that Netbackup used VMWare APIs to create and remove snapshots when doing VM inage backups. I guess I'm not sure how the FlashBackup Windows policy differs from or adds to the process...or maybe I'm talking about the same thing.

CBT functionality looks promising too. I may let our backup team get their head around virtual backups in general before throwing that at them :smileylaugh:

Very cool. Thanks again for the information.

[vmroyale]

I am not our backup engineer but, my assumption was that Netbackup used VMWare APIs to create and remove snapshots when doing VM inage backups. I guess I'm not sure how the FlashBackup Windows policy differs from or adds to the process...or maybe I'm talking about the same thing.

Yes, its the same thing.

Good Luck!