cancel
Showing results for 
Search instead for 
Did you mean: 

PREFERREDREPLICA TRUE not working correctly

a04andno
Level 3

Hi again,

There seems to be another problem here. The "PREFERREDREPLICA TRUE" key word in the batch script seems to not be working correctly. I have set it up so that one full backup is taken on saturdays and every hour one transactionlog backup is taken of the databases. But it seems like every bakcup thats done are done on both my server and not only on the prefered replica (se picture). I have set the option for the availability group and I have also the keyword in the batch script as you can see (se pictures). Does anyone know what could be wrong here?

Regards Anders

 

 

15 REPLIES 15

a04andno
Level 3

Hi again,

There seems to be another problem here. The "PREFERREDREPLICA TRUE" key word in the batch script seems to not be working correctly. I have set it up so that one full backup is taken on saturdays and every hour one transactionlog backup is taken of the databases. But it seems like every bakcup thats done are done on both my server and not only on the prefered replica (se picture). I have set the option for the availability group and I have also the keyword in the batch script as you can see (se pictures). Does anyone know what could be wrong here?

Regards Anders

Marianne
Moderator
Moderator
Partner    VIP    Accredited Certified

Marianne
Moderator
Moderator
Partner    VIP    Accredited Certified

Why do you have 
PREFERREDREPLICA TRUE 
and
OPERATION BACKUP
on the same line?

The examples that I have seen looks something like this:

GROUPSIZE #
OPERATION BACKUP
DATABASE ABC
SQLHOST HOST1
PREFERREDREPLICA TRUE
NBSERVER MASTER
MAXTRANSFERSIZE ....
etc.


Hi,

Thank you for your answer Marianne. That might be it, I will try that. Somehow I was convinced that I did it the right way by adding it before "OPERATION COMMAND" and I haven't seen any examples of how to do it (no examples in the manual).

Regards Anders

 

 

Unfortunatly it doesnt seem to help. The transaction logs backed up after the change still is taken on both the servers instedad of the secondary which is the prefered replica. Can this be some kind of caching issue? The only thing I did was to change in both the scripts so PREFERED REPLICA comes on its own on a separate line.

Regards Anders

Thiago_Ribeiro
Moderator
Moderator
Partner    VIP    Accredited

Hi @a04andno

Can you check if some of these limitation can be applied to your case?

SQL_Availability_Groups_Limitations.JPG

 

Thiago

Marianne
Moderator
Moderator
Partner    VIP    Accredited Certified

Hopefully your script does not have the same typo error as per your previous post ( PREFERED REPLICA) ?

Please copy dbclient logs on both nodes to .txt files (e.g. node1.txt and node2.txt) and upload here as attachments.

If dbclient log folder does not exist, please create it on both nodes before next backup.

Hi,

No, the script should be correct. I have now created the folders and are waiting for backups to be done.

Regards Anders

Here Are the logs requested.

Regards Anders

Here Are the logs requested.

Regards Anders

Hi Thiago,

I dont think this backups could refere to any striction on the list. Sure we use vmware but this backup is ordinary full set up from Netbackup client from each SQL-server (converted to copy-only) and transaction-log which are using legacy backup option with scripts thats pointed out in the Netbackup policy. The scripts tells what database to backup and to use the preferred replica. The setup is using an availability group and backups should be taken on the secondary replica only.

Regards Anders

Thiago_Ribeiro
Moderator
Moderator
Partner    VIP    Accredited

Hi @a04andno,

I'd like to ask if this backup was working before or is a new backup job? I didn't see error from your logs, so I'd like to know more about you policy configuration.

Forgive me but I havent knowledge about it....So , from what I understood you want the backups runs on secondary replica only right?

So, at the screenshot there are some limitations about backups running on secondary replica, did you see?

  • Backups of non-readable secondary replicas

NetBackup can only back up databases in a replica when you allow user connections for the replica.

If a secondary replica is the preferred replica and it is non-readable, the backup fails. If a secondary replica is not the preferred replica, NetBackup skips the backup of that replica.

SQL Server does not support the following types of backups on a secondary replica

■ Full backups - If a full backup takes place on a secondary replica, NetBackup converts the full backup to a copy-only backup.
■ Differential backups -  Backups of this type result in a failed backup.
■ Copy-only transaction log backups -  Backups of this type result in a failed backup.

Also check on SQL Server

■ NetBackup backs up the preferred replica, as determined by SQL Server.

 

Thiago

Hi Thiago,

The backup is working but instead of only backing up the preferred replica it backsup both primary and secondary so every time there are two backups done.

Thats right only run on the secondary replica.

* Backups of non-readable secondary replicas

The AG is set up to read from secondary first.

* Full backups

I have full backups but they are converted to copy-only backups as said in the manual.

* Differential backups

I dont have these.

* Copy-only transaction log backups

I dont have these only ordinary transaction log backups.

* NetBackup backs up the preferred replica, as determined by SQL Server.

I have configured the AG to only take backups of secondary (Secondary only).

 

Regards Anders

 

Marianne
Moderator
Moderator
Partner    VIP    Accredited Certified

Apologies - I have not had time to go throught the logs - very busy with my real day-time job! 

Which server is the primary and which one the secondary?

Because the same scripts exist on both nodes, I actually expect that jobs will be generated on both nodes. 
What I will be looking for in the logs is some evidence of 'not being the preferred replica' and 'skipping'. Or something to that effect... 

In all honesty, I don't have experience with AG backups. I have only seen a presentation on how to config.

Hi Marianne,

Thank you for taking the time to answer! Mssql3 is primary at the moment and mssql4 is secondary. I have gone through the logs and as you describes there is the "skipping" and the "backing up" part, so this seems fine to me. I have also checked the SQL-server logs and these also looks fine. Only AG backups on the secondary. But despite this there is two copies of each backup when I check the Netbackup client program on one of the sql-servers (see pic). I dont know if this is what it should look like or if im thinking the wrong way about this!?

Regards Anders