Backing up of SQL VM's

[hillda01]

Hi,

I'm looking at backing up my SQL virtual servers by snapshotting them and then shoving them of to another server.... What needs to happen with the SQL database for it to be backed up in a consistent state - I know you have to do some special stuff with Exchange but again im not sure what exaclty needs to happen...

Hope someone can help....

Regards

Dave

[java_cat33]

I would advise to use a SQL backup agent to backup your database and transaction logs. As for the backup of the VM itself there are a variety of methods. VCB, vRanger etc.

What do you currently use to backup Exchange?

[hillda01]

To backup exchange we use ArcServe agent for Exchange which works well...

I would like to be able to backup the vmdk file that hosts the SQL and Exchange database's so when it comes to restoring I can just pull back the vmdk file rather than reaching for a backup tape to restore database files etc....

There must be a way to backup the sql database consistently...

[vmrox]

Yeah your biggest danger is consistency.

You could use SAN-based tools (if viable - or even relevant). For example NetApp have Snap Manager for SQL which automatically quiesces the DB, and backs it up wherevever you wish. They also have snap vault which can transfer it to another datastore (in a consistent state).

If that is not an option, you could schedule a SQL backup job (or even do manual backups) within Server Management Studio to a second HDD (which is a seperate VMDK) - and snapshot that?

I have not tried the second (we use the first), could be worth a try though

[hillda01]

As you say my main concern was data consistency!!

Our VI infrastructure is working on a HP MSA500 which is SCSI based and dont having anything as fancy as SAN backups lol....

I like the idea of yours to create SQL backups from within the management studio to a seperate vmdk and then snapshot that...

We are looking to buy a SAN so will ensure the SAN has the SQL snapshot feature!

Thanks for your help...

[java_cat33]

Vizioncore vRanger quote from their user guide..... page 10

vRanger Pro includes a VSS driver that utilizes Microsoft's Volume Shadow Copy Service to alternately freeze and thaw application writes. VSS enables quiescing-or pausing-of supported databases to provide a transactionally consistent backup image. VSS works with VMware Consolidated Backup (VCB).

I've implemented vRanger for several customers - however I'd still recommend to backup your SQL/Exchange etc with a backup agent, but additionally you can backup the entire virtual machine (vmdk) via vRanger/VCB.

In a restore scenario (depending on the problem) you then have the option of restoring the whole VM from backup (via VCB/vRanger), and then restore the SQL database and transaction logs if needed via the backup performed using the SQL agent.

[hillda01]

To use your NetApp Snap Manager do you have to address the storage from within the virtual machine as raw disk mappaing to a SAN lun?

Or do you still keep the SQL database's within a vmdk file?

Regards

Dave

[vmrox]

Hi hillda01,

No RDM's are used. The SQL DB's are within the VMDK, and the Snap Manager tool is installed within the VM as well. You configure the backup jobs from within windows (including schedules I believe), and the backed-up DB's + tran logs are all sent to a mapped lun via this snap manager tool.

If you didn't have Snap Manager you could have 2 VMDK's in the VM:

C:\ - system drive, SQL install, DB's, tran logs

D:\ - Backups

The backups could be left there or manually copied off to another storage location (even via file copy if you need to).

For an even better solution you configure the D:\ drive not as a VMDK but as a mapped LUN (by installing an iSCSI software initiator within the VM and configuring the LUN mapping from within the SAN management software). That way the backups go directly to the SAN, rather than within a VMDK file.

Finally, best practice is to set up your VM with one (or 2) VMDK's, and map multiple LUN's (as described above) for each type of DB and Tran logs (user, system etc) - these will appear as drives in Windows but are direct LUNs mappings. You can do a default install of SQL, and then using the Snap Manager tool you can 'migrate' the DB's to these LUN's and also verify the backups and restore them as well.

[hillda01]

Hi vmrox nice name btw

"For an even better solution you configure the D:\ drive not as a VMDK but as a mapped LUN (by installing an iSCSI software initiator within the VM and configuring the LUN mapping from within the SAN management software). That way the backups go directly to the SAN, rather than within a VMDK file."

Wouldnt I have to have an iSCSI SAN to make use of the software initiator mapping to a LUN rather than a FC SAN...

Regards

Dave

[khughes]

The way we backup our SQL server is from a file level program which has been in place for years to backup the translog files etc which runs everynight... we also use a .vmdk backup program as well. The only problem that we ran into was from when we were using a really low level SAN and snapshots took a long time to commit and would lag out sometimes causing corruption in the SQL database. Even though we have changed SANs, we made it a policy to only do a full .vmdk backup on the SQL servers on weekends when there is no concern of users using the databases. We use esXpress for the .vmdk backups but TexiWill has a nice document on the heavy hitters of the bulk backup solutions.

From my perspective if you have a file level backup already in place you should just keep using that, and then add on a bulk VM backup which will give you the ability to restore the entire VM in case of a failure. Find something that you are comfertable with and that meets your standards for backing up your servers and go for it.

• Kyle

[Dave_Mishchenko]

Your post has been moved to the VI: Virtual Machine and Guest OS forum

Dave Mishchenko

VMware Communities User Moderator

[vmrox]

Hi hillda01 - thanks

Yes you are correct - an iSCSI SAN is needed, which (I now remember!) you don't have...

I would probably still go with the 2 x VMDK's, manual backups to the second (D:\) drive through SQL Server Mgmt Studio, and then copy those backups to another location (via robocopy or something similar to a file server on the same segment). A bit of admin overhead but it would ensure you have consistent backups to a specific point in time. Just get the business to accept the risk (eg "I can restore the DB's from 10pm each night, going back 4 nights - otherwise you need to invest in a SAN" ha ha ha ha)

If you went with the 2 x VMDK's + manual backups to D:\ as above, but used snapshotting rather than copying the files off to another location..the problem with this is that the redo-log fiiles (the delta files utilised by snapshotting) would be for the whole SQL server (rather than the individual VMDK holding the backups - that is my understanding of snapshots). This can be problematic for highly intensive (IO intensive) servers such as SQL (see below).

I think you might have to fall back on the old manual backup strategy.

khughes - great point about commiting the snapshots, it actually reminded me of a test SQL box we had where we tested exactly this - took a few snapshots (to establish how easily it would be to roll back after a patch upgrade), and although the server performed fine with the snapshots in place if we tried to roll back to a former state or even commit any changes it failed (presumably because of it's high IO utilisation).

I hope this helps

[hillda01]

Hi vmrox,

"If you went with the 2 x VMDK's + manual backups to D:\ as above, but used snapshotting rather than copying the files off to another location..the problem with this is that the redo-log fiiles (the delta files utilised by snapshotting) would be for the whole SQL server (rather than the individual VMDK holding the backups - that is my understanding of snapshots). This can be problematic for highly intensive (IO intensive) servers such as SQL (see below)"

Could I not put the drive that holds the live database's into independant mode so they dont get affected by the snapshots...

Regards

Dave

[vmrox]

Yeah you could - I assume you mean place the C:\ in independent (persistent) mode, and the D:\ (backups) in non-independant mode? That way all ongoing changes to C:\ would be written to the parent VMDK, and all ongoing changes to D:\ would be written to a delta file. (I have never done this but it sounds viable).

As long as you realise there would be no way to roll the system drive (C:\) back to a prior state without some sort of file level backup (or imaging software like LiveState)?

Also you would need to keep an eye on the number of child snapshots you made...I am not sure about limits etc..

Sounds pretty good - let us know how you go!

[vmrox]

Yeah you could - I assume you mean place the System drive (C:\) in independent (persistent) state, and the Backups (D:\) in non-independent? that way all changes made to the system drive would be written directly to the VMDK, and all changes made to the D:\ drive would be written to a delta file (which could subsequently be comitted to it's parent or discarded).

As long as you realise you wouldn't be able to roll back the System drive (C:\) to a prior state without some sort of file level or imaging software like LiveState? But I guess that would be the case with any non-snapshotted VM...

And you would have to keep an eye on the number of snapshots you were keeping - I am not sure about limits etc...

Sounds good - let us know how you go!

<OOPS - sorry about the DP.>

[hillda01]

Hi,

I've had another idea... would this work?

If I have two virtual hard disks of the same size - one set to independant mode so snapshots wont affect it and the other in normal mode so snapshots work as normal... Convert both disk's to dynamic and make a RAID1 array between the both disks so they are mirrored then install SQL - as the second disk is able to be snapshotted I will have an exact mirror of the disk.

I've not tested this yet but will do tomorrow - just wondered if this is feaseable....

I'd like to know everyones views...

Regards

Dave