Qlik Replicate – backup_set_id sequencing breaks after AG failover recovery (AlwaysOnSharedSynchedBackupIsEnabled)
Hi Qlik Support team,
We're running into a recurring issue with a SQL Server source task in Qlik Replicate related to how backup_set_id is tracked across an Always On Availability Group failover event, and we'd like guidance on the correct way to handle this, as we haven't found documentation that covers our specific scenario end-to-end.
Environment / configuration - SQL Server source endpoint using an Always On Availability Group listener - Internal parameter AlwaysOnSharedSynchedBackupIsEnabled is enabled - Readable secondaries are currently configured with HA off (not serving read traffic) - Transaction log backups are currently running on the primary replica
What happened 1. Log backups were running normally on the primary replica, and Replicate was consuming them without issue. 2. We had to temporarily redirect backups to the secondary replica for a short period. 3. We then moved backups back to the primary replica. 4. Because msdb is not shared across AG replicas, the two backups taken on the secondary during that window were never recorded in the primary's msdb. Following the approach described in your official article "Qlik Replicate and SQL Server AG HA Handling", we manually inserted the missing backup records into the primary's msdb so Replicate could locate them. 5. Replicate picked up and processed those two manually inserted backups. However, it then stopped processing entirely. On investigation, we found this was because the backup_set_id values on the manually inserted rows were significantly higher than the primary's own backup_set_id sequence. Once backups resumed on the primary afterward, the primary's native backup_set_id values were lower than the ones Replicate had already consumed, and the task would not move past this point.
Workaround we used As a manual recovery, we identified the specific LSN the task was waiting for from the task log, stopped the task, and used Advanced Run to restart the task from that required LSN. This got us moving again, but it's a manual, reactive fix rather than a supported procedure, and it's not something we want to rely on every time backups need to move between replicas temporarily.
Where we need help - The AG HA Handling article explains how to register secondary-replica backups into the primary's msdb, but it doesn't address what happens to backup_set_id sequencing when backups subsequently move back to the primary. Is there a recommended way to insert those records (e.g. allowing the identity column to generate a new, in-sequence backup_set_id rather than carrying over the secondary's original value) that avoids this conflict? - Is backup_set_id actually used by Replicate as an ordering/watermark value for determining the "next" backup to process, or is this purely LSN-driven under the hood? Understanding this would help us avoid the issue proactively. - Is there a supported/documented way to resume a task from a specific LSN or backup set after this kind of conflict, rather than relying on Advanced Run as a workaround? - More generally, could this end-to-end scenario (temporary backup failover to a secondary, manual msdb registration, then failback to primary) be documented as a supported procedure, since it seems like a fairly common operational event for AG environments and the current article only covers the first half of it?
We use two Powershell scripts to keep both the physical local tlog backup files and MSDB entries in synch across our two AOG nodes. I believe the MSDB script was provided by Qlik support.
Since we store the backup files locally (not on a network share), we needed to keep them in synch as well, so we wrote our own PS script to copy the files between nodes. It also includes a section to delete old files that normally are not deleted on the passive node.
1. Is it possible that you could share these scripts OR can a Qlik Support Engineer mediate this process?
2.Replicate picked up and processed those two manually inserted backups. However, it then stopped processing entirely. On investigation, we found this was because the backup_set_id values on the manually inserted rows were significantly higher than the primary's own backup_set_id sequence. Once backups resumed on the primary afterward, the primary's native backup_set_id values were lower than the ones Replicate had already consumed, and the task would not move past this point.
Can you comment on how you managed this particular circumstance?
Not sure how you manually inserted the entries, but I believe Qlik keeps track of the backup set id or looks it up in MSDB and expects a specific ID.
The Powershell attached copies the entire record including the source id.
The only time we've had an issue is when the backup files were not present in the folder that Qlik was looking for. That usually happens with we do a failover for maintenance and the backup file copy is behind. Once we've manually moved the backup files to where msdb entry indicates, Qlik picks up without an issue.
This Powershell for the backup file copy is pretty straightforward. We keep three days' worth of files (and back them up to another location), in the event we need to go back further. We also use LogStream, but on one occasion in a lower environment, we needed to go back a few more days and was able to recover the backup files from storage and Qlik picked up without an issue.
