Forum Discussion

Systems_Team's avatar
Systems_Team
Level 6
8 years ago
Solved

Queries regarding MS-SQL backups with Log Shipping

Hi Folks,

I'm looking for some thoughts from those of you who have configured NetBackup to backup MS-SQL databases that are using Log Shipping. I'm aso thinking of raising a case with Veritas, but as I'll have to give the case a lower severity I'm thinking it may take a little while to get a response.

My current environment is 2 x NetBackup Master Servers (7.7.3) running on Windows 2012 R2, with 2 x NetBackup 5230's (2.7.3) as Media Servers. SLP's and AIR are used to cross-pollinate the data between sites. I currently use a mix of script based SQL backups, Intelligent SQL Policies and VMware Intelligent policies using the SQL functionality (most of my SQL backups use the last option).

I have a new environment coming onboard (HPE Content Manager) which will have two SQL servers (one at each site) with Log Shipping enabled. I've read the manual (in this case it is the Veritas NetBackup for Microsoft SQL Server Administrator's Guide for Windows Release 7.7.2, as it wasn't re-released for version 7.7.3), and I have a couple of queries as some of the info is not quite clear to me.

Point 2 on page 150 (Under the heading "To configure NetBackup to support database log-shipping) states the following:
"Any policy that is used to back up the primary should also specify the host that contains the secondary database. See “Backing up SQL Server in an environment with log shipping” on page 150."

On the same page under the heading "Backing up SQL Server in an environment with log shipping" states that:
However, a backup must not be performed on a secondary (or standby) server. Databases must always be backed up on the primary server and restored on the primary server. This requirement is based on the Microsoft SQL Server restriction that is outlined in Microsoft knowledge base article 311115. If you try to perform a backup on the secondary server, you see a message in the dbclient log similar to the following:
16:33:26 [1208,2348] <16> CODBCaccess::LogODBCerr: DBMS MSG - ODBC message. ODBC return code <-1>, SQL State <37000>, Message Text <[Microsoft][ODBC SQL Server Driver][SQL
Server]Database 'Mumbo' is in warm-standby state (set by executing RESTORE WITH STANDBY) and cannot be backed up until the entire load sequence is completed.>

My query with these two points is that if we also have to add the secondary SQL server to the policy that backs up the primary, when the policy runs I would think it will also backup the secondary. That sort of seems to contradict the manual. Or does it not back it up even though it is in the same policy because it knows that it is in the wrong mode to do a backup? Hope that doesn't cause incomplete backups :)

My other query is to do with log truncation. As I read it from the manual, it would appear that you still do transaction log backups, but with no truncation - correct?

Based on what I've read, I think I'll have quite a bit of reconfiguration to do. VMware Intelligent policies using the SQL functionality won't be useful any more as there are other DB's on the same servers that do need log truncation. I'm thinking I'll have to do VM Intelligent policies without the SQL options to give me a backup of the machine as a whole, and then several script based SQL backups or Intelligent SQL Policies to backup DB's with and without log truncation as required. Messy and more policies, but I don't know I'll have a choice.

Thanks in advance,
Steve

7.7
windows server 2012
  • Systems_Team's avatar
    Systems_Team
    7 years ago

    Hi Jeanfernandes,

    I certainly did, and I should have come back and marked the solution.  As noted in one of my posts (which I'll mark as the solution), it is pretty much as noted in the NetBackup MS-SQL admin guide.  The steps were:

    • Configure secondary SQL server to point to same Master & Media servers as the primary.
    • Add the VCenter server that currently controls the secondary SQL server (at DR site) to the PROD site (Credentials, Port 443 between VCenter & Master, Port 902 between Media & ESX). I almost forgot about this Smiley Happy Currently my VCenters at each site only talk to the Master and Media servers at their respective sites.
    • Re-tweak my VIP query so that it can backup servers from two different sites.
    • Set up the redirected restores as documented for log shipping.

    The only one I didn't do was setting up the redirected restores as I haven't needed that yet.

    One issue that came out of this is that using a VMware policy for SQL will result in an incomplete backup (status 1), as databases in log shipping mode are not backed up.  The live database backs up fine.  This was sort of expected, as MS-SQL will not backup databases that are in certain states.

    I raised a case with Veritas to go through this, and as I suspected the VMware SQL policy doesn't have enough intelligence to deal with these sort issues.  The only way to get around the status 1 would be to use SQL Intelligent policies.  This has the smarts (and a particular set of options) to deal with databases that are offline, restoring etc.  The option is called "Skip unavailable (offline, restoring, etc.) databases", and is available for both databases and transaction logs.  In fact if memory serves me correctly, the passive database in a log shipping config is in fact in a restoring state.

    I have a name resolution issue with getting the SQL Intelligent Policy working, so for now I'm using the VMware SQL policy and putting up with the incomplete backup - but I know that is not an issue due to the database state.

    Let me know if you need anything more.

    Steve

  • Systems_Team's avatar
    Systems_Team
    Level 6
    8 years ago

    Hi Folks,

    A little update:

    I went back and had a look at the ASC job details for one of these backups, and I can see NetBackup is smart enough to know that the DB is in log shipping mode, so will not truncate logs - so I guess that part of my query is solved :)

    I just have to get the secondary server using the same Master as the primary and disable SQL backups for it and I think I'm almost good.  Based on that I'd say my remaining query is:

    If we also have to add the secondary SQL server to the policy that backs up the primary, when the policy runs I would think it will also backup the secondary. That sort of seems to contradict the manual. Or does it not back it up even though it is in the same policy because it knows that it is in the wrong mode to do a backup? Hope that doesn't cause incomplete backups Smiley Happy

    Many Thanks,

    Steve

    • Michal_Mikulik1's avatar
      Michal_Mikulik1
      Moderator
      8 years ago

      Hello,

      yes it sounds like contradiction, unless we consider non-replicated databases only. I guess that SQL Intelligent policies are smart enough to omit databases which are not in a suitable mode for backup, for example when they are in standby mode on the Log Shipping secondary.

      So I would create intelligent sql policy with both clients/sql instances in it as they advice it in the documentation:

      Then on primary node/instance the policy will backup all the databases (non-replicated dbs like master, msdb etc. and replicated primaries)

      Then on secondary node it will backup non-replicated dbs like master, msdb etc., and it will skip replicated secondaries.

      Once switchover occurs on MS SQL side, then no reconf will be needed on NetBackup side.

      Regards

      Michal

       

      • Systems_Team's avatar
        Systems_Team
        Level 6
        8 years ago

        Hi Michal,

        Many thanks for your input. Hopefully once I have both SQL servers connected to the same Master and Media servers then I will see successfully completed backups.

        At the moment (with both DB's on different Master and Media servers) the primary (and other DB's in the same policy) complete successfully with an informational note that the logs on the primary were not truncated due to it being in log shipping mode. However the secondary at the other site ends with a status 1 incomplete for the backup and ASC jobs with the warning that "The database is in the RESTORING state and cannot be protected".

        I guess the steps I have let to do are:

        • Configure secondary SQL server to point to same Master & Media servers as the primary.
        • Add the VCenter server that currently controls the secondary SQL server (at DR site) to the PROD site (Credentials, Port 443 between VCenter & Master, Port 902 between Media & ESX). I almost forgot about this :) Currently my VCenters at each site only talk to the Master and Media servers at their respective sites.
        • Re-tweak my VIP query so that it can backup servers from two different sites.
        • Set up the redirected restores as documented for log shipping.

        I haven't had to set up for redirected restores before, although my DBA's occassionally use it for SAP and MaxDB restores.  Reading the doco, it appears I could either create the "No.Restrictions" file as and when needed, or use the "altnames" file for particular hosts.  I had a look at an existing altnames host file from a redirected SAP restore, and while the file is named the same as the host (as in the doco), it also contains the name of the alternate host.  The SQL Admin doco doesn't describe that and just reads as if it is an empty file named the same as the host.  Not sure if that is SAP specific, but think I might just go with the No.Restrictions file when needed.

        It could take me a couple of days to get the rest of this config done, but when I'm done I'll pop back and share the results.

        Many Thanks,

        Steve

  • jeanfernandes's avatar
    jeanfernandes
    Level 0
    7 years ago

    Hello,
    Steve, have you been able to configure netbackup to support LOG SHIPPING in SQLSERVER?
    How did this environment work?

    • Systems_Team's avatar
      Systems_Team
      Level 6
      7 years ago

      Hi Jeanfernandes,

      I certainly did, and I should have come back and marked the solution.  As noted in one of my posts (which I'll mark as the solution), it is pretty much as noted in the NetBackup MS-SQL admin guide.  The steps were:

      • Configure secondary SQL server to point to same Master & Media servers as the primary.
      • Add the VCenter server that currently controls the secondary SQL server (at DR site) to the PROD site (Credentials, Port 443 between VCenter & Master, Port 902 between Media & ESX). I almost forgot about this Smiley Happy Currently my VCenters at each site only talk to the Master and Media servers at their respective sites.
      • Re-tweak my VIP query so that it can backup servers from two different sites.
      • Set up the redirected restores as documented for log shipping.

      The only one I didn't do was setting up the redirected restores as I haven't needed that yet.

      One issue that came out of this is that using a VMware policy for SQL will result in an incomplete backup (status 1), as databases in log shipping mode are not backed up.  The live database backs up fine.  This was sort of expected, as MS-SQL will not backup databases that are in certain states.

      I raised a case with Veritas to go through this, and as I suspected the VMware SQL policy doesn't have enough intelligence to deal with these sort issues.  The only way to get around the status 1 would be to use SQL Intelligent policies.  This has the smarts (and a particular set of options) to deal with databases that are offline, restoring etc.  The option is called "Skip unavailable (offline, restoring, etc.) databases", and is available for both databases and transaction logs.  In fact if memory serves me correctly, the passive database in a log shipping config is in fact in a restoring state.

      I have a name resolution issue with getting the SQL Intelligent Policy working, so for now I'm using the VMware SQL policy and putting up with the incomplete backup - but I know that is not an issue due to the database state.

      Let me know if you need anything more.

      Steve