henrydenhengst
Contributor
Contributor

databases

Jump to solution

We are at the point where we want install OS & SQL database VM on our VI3.

The backup will be done with VCB.

Is VCB enough when you restore your VM with SQL database? Is than your SQL database still consistent?

Or do you need to backup this with a agent or rman?

0 Kudos
1 Solution

Accepted Solutions
matt-brewster
Enthusiast
Enthusiast

I've done a bit of homework in this area (as it's what I'm implementing at the moment), my findings are thus:

1) You cannot 100% rely on VCB backups of any server, as they are crash consistent only. In 99% of cases you will power your restored VM back on, the OS will recover and everything will be OK. 1% of the time (probably when you most need it) the VM will not power on gracefully - you'll get a BSOD, or the apps on the server won't start properly etc.

2) If your server does not run a database (SQL, Exchange, Oracle, AD) you can implement the LGTO SYNC driver in VMware tools which will quiesce the file system, meaning your VCB snapshot will contain as much as it can - no half-open file writes.

3) If your server does run a database, do not use the LGTO SYNC driver unless you plan on stopping the database services using the pre-freeze script and starting them again using post-thaw script. If you do not, the DB services can fall over during the LGTO SYNC freeze operation.

On my SQL VM box I use the pre-freeze/post-thaw scripts to stop the SQL services before the VCB snapshot and start them again afterwards. This obviously means some downtime, but we're not a 24h operation here so it is fine.

Before this happens I also have an SQL agent job to backup the databases to remote storage (my VCB proxy) - this gives me a "fallback" backup should the VM power-on fail. Also, by using an SQL job it ensures the transaction logs get truncated, as VCB is not application-aware.

Also, once a week I have a backup script that powers down the SQL VM and does a full VCB backup - this is my "last resort" backup that could be max 5 days out of date, but I know it will power up without issues, and I can restore the SQL backup jobs in order.

Hope this helps

Matt

View solution in original post

0 Kudos
4 Replies
RParker
Immortal
Immortal

you need a program like Vizioncore vRanger to include a feature called VSS (virtual shadow service) to quiesce the databases inside the VM. That's exactly what that feature is supposed to do, it notifies that database server that it's going to be backed up, it sets the files to a "snapshot" of sorts, so that when you restore the database it will be restored properly because it can corrupt the database if not done properly.

I believe any file level backup (for VCB) can do this, I just know Vizion Core has this feature specifically included. You need to make sure that this is on your backup software, it's not included in VCB or other 3rd party backup programs.

That's why you need specific backup software packages that are specifically designed for VCB / ESX server VM's. Netbackup and other 3rd party backup programs only backup the entire VM, and I am pretty sure they don't have this since they aren't designed to backup VM's, they treat VM's like files, and thus aren't aware of the need to perform this level of database support.

You only need to do this for VM's that have the database inside the VM itself. With VC the best practice is to use a separate database server, so this would not be an issue in VI/VC, since the database isn't in the same instance as the VC service.

jrenton
Hot Shot
Hot Shot

You could use SQL Enterprise Manager to run SQL backups, then use VCP to backup the VM including the backup files after this has occurred. The databases can then be restored from the SQL backups.

matt-brewster
Enthusiast
Enthusiast

I've done a bit of homework in this area (as it's what I'm implementing at the moment), my findings are thus:

1) You cannot 100% rely on VCB backups of any server, as they are crash consistent only. In 99% of cases you will power your restored VM back on, the OS will recover and everything will be OK. 1% of the time (probably when you most need it) the VM will not power on gracefully - you'll get a BSOD, or the apps on the server won't start properly etc.

2) If your server does not run a database (SQL, Exchange, Oracle, AD) you can implement the LGTO SYNC driver in VMware tools which will quiesce the file system, meaning your VCB snapshot will contain as much as it can - no half-open file writes.

3) If your server does run a database, do not use the LGTO SYNC driver unless you plan on stopping the database services using the pre-freeze script and starting them again using post-thaw script. If you do not, the DB services can fall over during the LGTO SYNC freeze operation.

On my SQL VM box I use the pre-freeze/post-thaw scripts to stop the SQL services before the VCB snapshot and start them again afterwards. This obviously means some downtime, but we're not a 24h operation here so it is fine.

Before this happens I also have an SQL agent job to backup the databases to remote storage (my VCB proxy) - this gives me a "fallback" backup should the VM power-on fail. Also, by using an SQL job it ensures the transaction logs get truncated, as VCB is not application-aware.

Also, once a week I have a backup script that powers down the SQL VM and does a full VCB backup - this is my "last resort" backup that could be max 5 days out of date, but I know it will power up without issues, and I can restore the SQL backup jobs in order.

Hope this helps

Matt

0 Kudos
v01d
Enthusiast
Enthusiast

Vizioncore's VSS implementation is incomplete. They only implement the freeze and thus your snapshot will have an open VSS witer. They do not guarantee you will get a will get a consistant backup. In a proper implementation the "shadow copy" is outside the scope of the snapshot itself.

0 Kudos