cancel
Showing results for 
Search instead for 
Did you mean: 

MS-SQL and VMware policy question

Sortid
Level 6

Hi,

Using NetBackup 7.6.0.1 and SQL 2008/2012, and ESXi 5.1.

I am trying to work out the best way to back up our SQL servers as we have moved to VMs.  Traditionally we have used the MS-SQL policy type to back up databases, with local SQL agent jobs backing up transaction logs.  Now we have introduced servers which also run applications as well as databases, so I have to capture the whole server.  If I run an MS-SQL policy to capture databases, then run a VMware policy which will capture the entire machine, what is the effect on the database?  I won't be using "Enable SQL Server Recovery" (option in VMware policy), so the database will be backed up with out file-level recovery in this policy as well.  In particular, for databases which are in Full recovery mode, with local transaction log backups, will the two backups interfere with the point-in-time recovery of the databases?

Thanks.

1 ACCEPTED SOLUTION

Accepted Solutions

Dan_42
Level 4
Partner Accredited

When you say configure the VMware policy not to process SQL, you mean by not ticking the SQL recovery box?  

>Yes

Does this ignore SQL altogether?

>Sort of. It's not going to do anything special for it, but rather treat it as just any other block of data  

The default for the VMware policy is to back up ALL_LOCAL_DRIVES, so doesn't it try to back up the flat files?

> Yes, but that does nothing to the backup chain, and isn't really something you "should" count on restoring from. Especially in a scenario where you care enough to run Full Recovery model to begin with

 I thought if you didn't tick the box it would still back up SQL but the only way to restore it would be to restore the entire vmdk.

>It backs up the drives in their entirety, but you'll get a "crashed" server scenario and potentially angry SQL when things come back around

If I use a SQL policy to back up the databases, would either an MS-Windows policy capture the databases as well (using ALL_LOCAL_DRIVES) or the VMware policy without ticking the SQL recovery option?

>Best practice is to exclude the MDF/LDF files from MS-Windows policy. Otherwise the files will get "backed up" as files, but don't count on restoring from them. 

 

Either way will work, just don't do both. If you do separate SQL you'll get a little more flexibility if you're seeking tigher RPOs, for example. (I'm not a fan of VMware backups during production due to the snapshot "blips" in connectivity, but you can generally get away with breaking off some transaction logs in most environments). If you do VMware policy only you'll have the benefit of only having to worry about just the one job, and you'll potentially save space on the destination storage, depending on what you're doing. 

View solution in original post

4 REPLIES 4

Dan_42
Level 4
Partner Accredited

As you've gathered, more than one backup chain of SQL in full recovery model is generally a no-no. Two separate chains (obviously) breaks things with the sequence numbers on the incrementals when it comes time to put things back together. You should be ok as long as you don't configure the VMware policy to process SQL. 

Always perform test restores after modifying your big-picture backup architecture, of course. 

Sortid
Level 6

Thanks for the response, Dan.  When you say configure the VMware policy not to process SQL, you mean by not ticking the SQL recovery box?  Does this ignore SQL altogether?  The default for the VMware policy is to back up ALL_LOCAL_DRIVES, so doesn't it try to back up the flat files?

I thought if you didn't tick the box it would still back up SQL but the only way to restore it would be to restore the entire vmdk.

Thoughts appreciated!

Sortid
Level 6

To clarify, I am asking how people back up the file system of a SQL server without capturing SQL again.  If I use a SQL policy to back up the databases, would either an MS-Windows policy capture the databases as well (using ALL_LOCAL_DRIVES) or the VMware policy without ticking the SQL recovery option?

Dan_42
Level 4
Partner Accredited

When you say configure the VMware policy not to process SQL, you mean by not ticking the SQL recovery box?  

>Yes

Does this ignore SQL altogether?

>Sort of. It's not going to do anything special for it, but rather treat it as just any other block of data  

The default for the VMware policy is to back up ALL_LOCAL_DRIVES, so doesn't it try to back up the flat files?

> Yes, but that does nothing to the backup chain, and isn't really something you "should" count on restoring from. Especially in a scenario where you care enough to run Full Recovery model to begin with

 I thought if you didn't tick the box it would still back up SQL but the only way to restore it would be to restore the entire vmdk.

>It backs up the drives in their entirety, but you'll get a "crashed" server scenario and potentially angry SQL when things come back around

If I use a SQL policy to back up the databases, would either an MS-Windows policy capture the databases as well (using ALL_LOCAL_DRIVES) or the VMware policy without ticking the SQL recovery option?

>Best practice is to exclude the MDF/LDF files from MS-Windows policy. Otherwise the files will get "backed up" as files, but don't count on restoring from them. 

 

Either way will work, just don't do both. If you do separate SQL you'll get a little more flexibility if you're seeking tigher RPOs, for example. (I'm not a fan of VMware backups during production due to the snapshot "blips" in connectivity, but you can generally get away with breaking off some transaction logs in most environments). If you do VMware policy only you'll have the benefit of only having to worry about just the one job, and you'll potentially save space on the destination storage, depending on what you're doing.