Attunity - Error reading backup log file after failower in AlwaysOn

[Orlandis]

Hi
We are trying to use Attunity CDC to get data from SQL Server. The base, which is the source for Attunity CDC, is located in the AlwaysOn availability group. For the AlwaysOn cluster (two nodes), an external distributor is organized that serves transactional publishing for Attunity, for publishing on the distributor, redirection to the lister is configured to connect Attunity.
We use the Backup Log Only method and when we perform a failower to the secondary replica everything goes well, but when we return back and make log backups we get messages -
Failure in starting (with TXN consistency...)
Failure validating initial LSN '00000033:00007340:0005' 'anywhere existance'. Found nowhere.

How do we go about resolving this issue?

[PGN]

I believe it looks for backup logs using msdb entries.  We have an AOG cluster with a similar config...distribution db is on its own server.  We do not rely on backup logs, but use online first.  However, in the event Replicate needs to find older data, it will attempt to read the file and looks at msdb to find them.  We have a script that copies both msdb and the actual log file backups between AOG nodes on a periodic basis to keep them in synch.  We backup our log files locally on each node when that node is active.  

So msdb is local to each node, if node A doesn't have the backup entry that node B made, it will not be able to locate it.  This may be the issue you're having.  

There are also some Internal Parameters needed in the Endpoint Connection to support this configuration.  Support may be able to provide more guidance/details.

[Orlandis]

Thanks for your reply.
In our AlwaysOn scheme, both replicas, depending on which one is the main one, back up logs to one shared storage, where Attunity goes after them.
What you described about msdb is probably an important point in determining the start point of synchronization, I would like a little more information about what exactly you synchronize into these databases between nodes, but the question remains why with the first failower when the secondary replica becomes primary, Attunty works fine, but when you return it is already buggy. Both replicas are configured the same, the Attunity account has the same rights on both nodes

[PGN · Accepted solution]

There are two Internal Parameters we set on our source AOG connection string:

*disclaimer* these were provided by QLIK support a while back for our specific instance, so use at your own risk...it's been my experience that support does not provide these parameter strings unless the situation warrants. 

AlwaysOnSharedSynchedBackupisEnabled = Y

leadingPrimaryReplica = {your primary node name}

Again, these work for us, it may not apply to your situation.

They also provided a Powershell script which basically copies the entries for  msdb.dbo.backupmediaset,  msdb.dbo.backupmediafamily, and  msdb.dbo.backupset from one node to another.

Since you use shared storage for the log backups, you obviously wouldn't need to copy the physical files as we do.

[Orlandis]

Thank you very much for your help, I will be glad to treat you with something if we meet 🙂
We always test everything on a test environment, we usually enable the AlwaysOnSharedSynchedBackupIsEnabled setting, but this is a new cluster, it may not have been enabled, I'll check it tomorrow, thanks.
As for the leadingPrimaryReplica setting, please tell me where it is located (if you can make a couple of screenshots) and what specifically needs to be specified there, you need to specify the name of the listner or the hostname of the replica on which the publication was created, or here you need to change the hostname every time after a failower, so that did the name always indicate the current primary replica?
As for msdb, if possible and not difficult, please share the Powershell script and recommendations from the vendor company

In any case, thanks a lot for your help.

[PGN]

[PGN · Accepted solution]

Powershell code is also attached.  Provided with no warranty.  🙂

[Orlandis]

Hello.
Thank you very much again for your support, today we will test your script on "synchronous" msdb.
Another question on this parameter leadingPrimaryReplica, what role does it play when using AlwaysOn and how to use it in case of failowers? When we perform a manual failover, do we need to somehow manage it, change the name of the node that will become the primary replica, or is it still a listner?

[PGN]

To be honest, I don't know.  The parameter was provided by support a number of years ago.  We've never changed it except in the case of migrating the server during a SQL upgrade.

[Orlandis]

I understand you, thank you very much for your help, I will check all the recommendations and then share our successes.
All the very best to you