09-26-2016 07:25 AM
Backing up SQL 2014 Mirrored VMs on VMware. Netbackup 7.7.1. The backups are always partially successful with a status 1. The Application State Capture job on the passive node returns the following results:
Warning ascc (pid=29156) MSSQL: Skipping database MSSQLSERVER\DB_NAME. The database is in the RESTORING state and cannot be protected.
Just want to confirm this is normal behaviour since it's the secondary node. I find it odd that it would return a status 1 for this reason. I would think that the parent job would be able to determine whether the DBs that are in a "restoring state" were picked up in the child job that runs on the primary node.
Solved! Go to Solution.
09-27-2016 04:26 AM
Hello arctics2,
yes with VMware based policy, you cannot get rid of status 1 in this case.
You must use intelligent MS-SQL-Server based policy with "Skip unavailable databases" option for mirroring partner, if you wanna run without status 1.
However I would rather run VMware policy because it provides quicker DR. I think that run it once a week is enough for mirroring partner (and once a day or more frequently for the principal).
Regards
Michal
09-27-2016 11:04 AM
Thanks Michal. This is exactly the confirmation I was looking for.
Regarding the intelligent policy option, as per the guide, database mirroring is not supported for Intelligent policies.
Regarding changing the frequency, my only concern is that if databases failover to the mirroring partner, and I've reduced frequency for the mirror backups, it will be a problem since they are now the principal, and the original principal is now the mirroring partner.
I will leave it as is so both instances are backed up simultaneously (as recommended in the guide), however, I think the parent job should be intelligent enough to complete with a status 0 since the db's were backed up in the principal node.
09-26-2016 07:32 AM
Did you perhaps mean Clustered? Not Mirrored?
You should not be using VMware to protect clustered SQL Server.
Please see Limitations of using a VMware policy to protect SQL Server in NetBackup for Microsoft SQL Server Administrator's Guide -
Point 4:
■ SQL Servers cannot be clustered.
09-26-2016 09:32 AM
Hi ,
Moreover as per the error log you have posted , it looks like the DB is in restoring state.
MS SQL does not allow to backup of db's in restoring state as it is not completely online (it is understood as unavailable DB until becomes online).
Regards,
Kesavan
09-26-2016 09:34 AM
Marianne,
Thanks for the quick response.
I am actually referring to mirroring, not to be confused with clustering. From the Netbackup for MS SQL admin guide:
09-26-2016 09:42 AM
Kesavan,
Yes, the DB's would be a "restoring" state on the standby node in the Mirror. This is by design in an SQL mirror.
However, as per the Netbackup for SQL admin guide, both nodes in the Mirror need to be added to the policy to ensure they are backed up together, specifically for the reason you specified. The db's on the standby node would not be backed up and only the live db's on the primary node would be backed up.
This is understood, however, if this is the only reason it's returning a status 1 at the parent job, it raises a false concern that something has gone wrong, when in fact, it is running as expected.
Thanks
09-26-2016 10:02 AM
Hi ,
Just to confirm , did you followed the below steps to setup your backups for this mirror patners?
1 Create a policy with a backup schedule for the principal.
2 Add the host that contains the mirroring partner to the client list.
3 Create a batch file and add it to the backup selections list.
4 Create a batch file on the mirroring partner that has the same name as the batch file specified in the backup selections policy. The batch file on the mirroring partner should be identical to the one used on the principal, with one exception. The value for SQLHOST and SQLINSTANCE are different.
09-26-2016 10:28 AM
If you have added both the nodes (active and mirror) , Active one should get succeded and only the mirrored copy should fail with error as it is restoring state .
Is that the case happening there ?
Regards,
P,Kesavan
09-27-2016 04:26 AM
Hello arctics2,
yes with VMware based policy, you cannot get rid of status 1 in this case.
You must use intelligent MS-SQL-Server based policy with "Skip unavailable databases" option for mirroring partner, if you wanna run without status 1.
However I would rather run VMware policy because it provides quicker DR. I think that run it once a week is enough for mirroring partner (and once a day or more frequently for the principal).
Regards
Michal
09-27-2016 11:04 AM
Thanks Michal. This is exactly the confirmation I was looking for.
Regarding the intelligent policy option, as per the guide, database mirroring is not supported for Intelligent policies.
Regarding changing the frequency, my only concern is that if databases failover to the mirroring partner, and I've reduced frequency for the mirror backups, it will be a problem since they are now the principal, and the original principal is now the mirroring partner.
I will leave it as is so both instances are backed up simultaneously (as recommended in the guide), however, I think the parent job should be intelligent enough to complete with a status 0 since the db's were backed up in the principal node.