Forum Discussion

vdpatel's avatar
vdpatel
Level 3
9 years ago
Solved

Best practice to configure backup for the VM running MSSQL 2008 database

NetBackup 5330 appliance configured with NetBackup version 7.7.1.

We are migrating backup to NetBackup and configuring backup for all the client server. And we have some of the VM's are hosted with MSSQL database. So I want to know what is the best method for the SQL database backup on VM and restore procedure for the SQL database.

1 - BMR backup for the VM + SQL backup for the database (Full+increment+log)      =or=
2 - Snapshot backup for the VM (VMDK)+ SQL backup for the database (Full+increment+log) 

What is preferred standard or best practice and what are the pros and corns, please provide your expert comment.

Any suggestion would be appreciated.

Thanks,

7.7
Backup and Recovery
NetBackup
Windows Server (2003-2008)
  • Michal_Mikulik1's avatar
    Michal_Mikulik1
    9 years ago

    Hello,

    this is my approarch:

    - one policy of VMware type and "Enable SQL Recovery" attribute checked (for quick DR), running rarely

    - another policy of MS-SQL-Server type and schedules according your RPO/RTO needs, running more frequently

    I prefer VMware based backups/restores to BMR-based backups/restores, unless you are planning V2P conversions during recovery. However both are possible, with the following difference:

    - after BMR restore, you must perform MS-SQL-Server DR steps first (master/msdb/model databases recovery), then go on with user databases restore

    - after VMware restore where "Enable SQL Recovery" was employed, alll databases including master/msdb/model are restored to the time of VMware backup. So you can go on directly with user databases restore up to the required point-in-time

    Briefly said, you can meet shorter RTO with VMware backup than with BMR backup.

    regards

    Michal

     

     

  • Michal_Mikulik1's avatar
    Michal_Mikulik1
    Moderator
    9 years ago

    Hello,

    this is my approarch:

    - one policy of VMware type and "Enable SQL Recovery" attribute checked (for quick DR), running rarely

    - another policy of MS-SQL-Server type and schedules according your RPO/RTO needs, running more frequently

    I prefer VMware based backups/restores to BMR-based backups/restores, unless you are planning V2P conversions during recovery. However both are possible, with the following difference:

    - after BMR restore, you must perform MS-SQL-Server DR steps first (master/msdb/model databases recovery), then go on with user databases restore

    - after VMware restore where "Enable SQL Recovery" was employed, alll databases including master/msdb/model are restored to the time of VMware backup. So you can go on directly with user databases restore up to the required point-in-time

    Briefly said, you can meet shorter RTO with VMware backup than with BMR backup.

    regards

    Michal

     

     

  • Will_Restore's avatar
    Will_Restore
    Level 6
    9 years ago

    I like to install the NetBackup client on all SQL servers and setup MSSQL policy for each one.

    Pretty simple and it works. 

  • vdpatel's avatar
    vdpatel
    Level 3
    9 years ago

    Thank you Will, I have already installed NetBackup client and created MSSQL policy for the database, hence I need suggestion for the best practice or standard for the recovery for the server. Can we restore with snapshot backup and top of that we can restore database backup? or it is better to recover through BMR and restore database.

  • Will_Restore's avatar
    Will_Restore
    Level 6
    9 years ago

    We generally don't protect VMs with NetBackup since the admins claim they can provision another "within minutes". 

    Actual practice is a bit longer but it saves a lot of my time letting them handle that aspect.  Restoring SQL data is pretty quick.

    I'm going to take another look at BMR as my experience with it a few years ago was not a happy one.  Maybe configuration and maintenance is easier these days. 

  • Marc_LHeureux's avatar
    Marc_LHeureux
    Level 4
    9 years ago

    Our standard is MSSQL Agent for the DB and basic VMWare (no application settings) for the OS, all drives.  Yes the SQL data gets captured by both backups.

    In our experience simple VADP restore puts back both the OS and the DB can be recovered just the same as if from a sudden power outage, but they also have the MSSQL backups use for recovery in case the running files are totally unusable.

    Additionally, our DBAs insist on having a local dump, so actually the DB data is covered 3 times (SQL Agent, live data files in OS backup, dumps in OS backup)

    The DBA's don't want to use application aware VM backups because they want finer control of log file truncation, this is the main reason they want the Agent backups.

  • vdpatel's avatar
    vdpatel
    Level 3
    9 years ago

    Thank you Marc, Yes our DBA raised same concern to recover database with point in time recovery and all transection backup. so we have installed the Agent on the SQL server for the database. so my concern is incase that VM hosted ESX crashed or in any situation we required to restore SQL server. what is the method to restore that server? can we restore from BMR backup and restore MSSQL database on that?


    right now, situation is I have VM running with WIN 2008 R2 and running MSSQL database. NetBackup agent is installed and I have configured the BMR + MSSQL database + log backup.


    I need suggestion whether we can configure snapshot backup + Agent backup for the database? and how to restore server in this scenario?

  • vdpatel's avatar
    vdpatel
    Level 3
    9 years ago

    Thank you Michal, this explains my concerns. so vmdk backup is faster in comparission to BMR restore.