cancel
Showing results for 
Search instead for 
Did you mean: 

SQL 2014 Mirror on VMware

arctics2
Level 5

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.  

2 ACCEPTED SOLUTIONS

Accepted Solutions

Michal_Mikulik1
Moderator
Moderator
Partner    VIP    Accredited Certified

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

View solution in original post

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.

View solution in original post

8 REPLIES 8

Marianne
Moderator
Moderator
Partner    VIP    Accredited Certified

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.



 

P_Kesavan
Level 4
Certified

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

Kesavan.P

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:

Database mirroring is a software solution that increases the availability of a SQL Server database. It uses two database instances (normally on different hosts), which contain copies of the same SQL Server database. These databases are identical in both name and content. The copies are the principal and the mirror. The mirror serves as a hot standby to the principal, where transactions take place. The mirror is very closely synchronized with the principal through transaction log porting.  It is immediately available in case the principal fails.
 
 

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

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.

Kesavan.P

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

Kesavan.P

Michal_Mikulik1
Moderator
Moderator
Partner    VIP    Accredited Certified

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

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.