Forum Discussion

Toddman214's avatar
Toddman214
Level 6
10 years ago
Solved

Preferred backup method for VM's running SQL databases

Windows 2008r2 standard Master and Media servers running NBU 7.6.0.4

 

I'm trying to reclaim some disk space on our backup targets, and one area I see potentially significant savings may me in the backup of several VM's we have that run SQL databases. There are approximately 60 such servers in our environment.

So, what is happening is that the VM policies are backing up the VMDK file (VMware policy type), which is also capturing the volumes which contain the sql databases. But, we are also running daily MS-SQL-Server policies for each of the sql databases that reside on these servers, so we are capturing this data twice.

Since we have quite a few of these, and they are VM's, I'd like to exclude the database volumes from getting captured in the VMDK backup, in the policy. But, since these VM's are SQL servers, they do require the NBU agent to be installed, which means I could also use the standard exclusion lists in the client properties to do this as well (much more time and work).

Choice 1). Use the "exclude data disks" option and continue to back these sql servers up using a VMware policy

Choice 2). Move these SQL vm's into an MS-Windows policy type, and use the client's exclusion list to omit backup of  *.ldf,  *.ndf, *.mdf files.

Please let me know your thoughts.

Thanks all!

 

 

Todd

 

7.6
Backup and Recovery
NetBackup
Windows Server (2003-2008)
  • Haniwa's avatar
    Haniwa
    10 years ago

    Todd,

    VMware policy "Exclude Data Disks" should work, and is by far the cleanest method (single click to cover entire policy). But, all of your VMware VMs would have to be OK with only C: drive backup... if some of them need C: and D: (as an example), this method would not work, since D: is considered a data drive.

    Another thing to be aware of ...

    When using VMware policy on a SQL host without selecting SQL protection checkbox, the backup will trigger the whole stack of VSS providers (via VADP -> VMware Tools), which includes the SQL VSS provider. The resulting VSS provider invocation will update the Last Backup Date field for each database, as if a SQL backup had been taken, unaware that the VMware policy did not take a SQL backup. If your actual SQL backup method (NBU SQL Agent as an example), is a Full, then this will not matter, but if it is a Diff, it will result in a broken backup chain, since the Diff will be based off of a Last Backup Date that never occured.

    To work prevent this, you can disable the SQL VSS Writer service on each SQL VM that is effected, as long as your actual SQL backup method does not depend on it (NBU SQL Agent does not use VSS unless Snapshot Client is selected).

    Ken W

     

     

  • ShelleyK's avatar
    ShelleyK
    Level 4
    10 years ago

    Todd,

         I guess I'm not completely clear on your goal(s). Is your goal not to duplicate backing up the MSSQL files? If so, you can protect your MSSQL data under VMware. Have you looked into this option? You get both the VMware backup AND MSSQL backup in one policy. (See NetBackup7.6_AdminGuide_MSSQL_Win.pdf chapter 6, p 95, for details.) I've done it and it works fine.

         From the Admin Guide:

    Through a VMware backup policy, NetBackup can create consistent full backups of an SQL Server database that resides on a virtual machine. To protect a supported application with a VMware policy, there is a new job or phase during the backup. An Application State Capture (ASC) job executes after the VMware discovery job and before the snapshot job(s). This ASC job contacts the NetBackup client on the guest virtual machine. The ASC job collects and catalogs the specific data that is needed for application recovery.

    You can do the following with VMware backups:

    ■ Perform single pass VMware backups that can quiesce all instances of SQLServer in that guest OS and their databases.

    ■ Use the existing SQL Server restore process to restore and recover data from VMware backups.

    ■ Restore and recover databases from VMware backups to alternate clients. The target destination client can be a physical computer or a virtual machine.

     

         Otherwise, yeah, your policies and configuration get messy.  :-(

     Michelle

     

     

  • Toddman214's avatar
    Toddman214
    Level 6
    10 years ago

    Thanks Michelle. My main point is just to eliminate backing up the databases twice by having them captured both by the vmdk backup AND by the sql policy as well. I'm leaving the ms-sql policy backups as they are, but wasnt sure if using the "exclude data disk' VMware option is the best way to prevent this from happening, or using an MS-windows policy and just excluding all .ldf, .mdf, and .ndf files in the individual client exclusion lists.

  • ShelleyK's avatar
    ShelleyK
    Level 4
    10 years ago

    OK that's what I thought you meant. There are limitations to the method I described in my original post (info in manual) but if they don't apply, it may be worth a try for you.  Good luck!

  • Toddman214's avatar
    Toddman214
    Level 6
    10 years ago

    So, I tested serveral times with a few different clients, and using the *.ldf, *.ndf, *.mdf exclusions in the client properties has worked in each case. It was quite a bit of work, but its working. I wish there was a way to do exclusions like that at the policy level, but my research didnt turn up anything regarding that, aside from "exclude data disk" using vmware policy type, which didnt seem to work well for me.

  • Haniwa's avatar
    Haniwa
    Level 4
    10 years ago

    Todd,

    VMware policy "Exclude Data Disks" should work, and is by far the cleanest method (single click to cover entire policy). But, all of your VMware VMs would have to be OK with only C: drive backup... if some of them need C: and D: (as an example), this method would not work, since D: is considered a data drive.

    Another thing to be aware of ...

    When using VMware policy on a SQL host without selecting SQL protection checkbox, the backup will trigger the whole stack of VSS providers (via VADP -> VMware Tools), which includes the SQL VSS provider. The resulting VSS provider invocation will update the Last Backup Date field for each database, as if a SQL backup had been taken, unaware that the VMware policy did not take a SQL backup. If your actual SQL backup method (NBU SQL Agent as an example), is a Full, then this will not matter, but if it is a Diff, it will result in a broken backup chain, since the Diff will be based off of a Last Backup Date that never occured.

    To work prevent this, you can disable the SQL VSS Writer service on each SQL VM that is effected, as long as your actual SQL backup method does not depend on it (NBU SQL Agent does not use VSS unless Snapshot Client is selected).

    Ken W

     

     

  • Toddman214's avatar
    Toddman214
    Level 6
    10 years ago

    I went with using the exclusion setting in the client properties on the SQL clients, and excluded all database files using * wildcard for all ndf, mdf, and ldf file types. Testing this was successful. It was a lot of manual work, but Ive added the steps to my process for adding new SQL servers to my backups.