Our company leverages SQL Server Availability Groups for the vast majority of our SQL Server implementations, primarily for resiliency requirements. Qlik Replicate is used to source data from many of these systems and land it into the cloud. Unfortunately we have observed an opportunity with the integration between a SQL Server AG and Qlik Replicate that causes a fair amount of manual work on our end to ensure no data is lost in the integration.
We directly access the backups of transaction logs from SQL Server AGs, all replicas in the AG write their backups to a common data domain path exposed on the network via UNC. The service account running Replicate has access to this UNC path so it reads it directly from there. This pattern works well with Replicate connecting to the AG through the AG Listener, querying the primary for the transaction log backup metadata, then getting the change data from the backups directly on the UNC path.
However, the problem we have is when the primary needs to failover, either as part of a planned activity or some unplanned issue. When Replicate reconnects to the new primary (through the AG listener) it queries the backup metadata for the log file it left off on, which does not exist in the metadata on the new primary since it was not the replica that originally did the backup. Each replica only has metadata about the backups it performed, so at this point Replicate is stuck and cannot advance. The only recovery option we have at this point is to perform a full reload of all the replication tasks.
We are currently live with eight SQL Server AG integrations in production, each of which has a planned failover monthly. Reacting to these failovers takes roughly eight to twelve hours per system, and requires additional manual intervention downstream where the data is being replicated to.
The Replicate user guide recommends avoiding this situation by allowing the Replicate service account to directly connect to each replica in the AG. Unfortunately this is not a solution that works for us as our DBA and source application teams have setup these AGs using best practices provided by Microsoft and are confident that allowing direct connections to the replicas instead of routing through the AG listener will cause major issues in our systems.
We have spoken with our CSM and another SME from within Qlik about this problem, and it does sound like one option is allowing Replicate to look for backup metadata in a user defined database instead of the default databases like master or msdb. This would require us to create a new DB with tables that look like the official backup metadata tables and then for us to write logic that would populate these tables with the metadata and sync that across all replicas in the AG. I've spoken with my DBA team and they are supportive of this solution and would sign up to develop this on our side, we'd just need that feature enabled on the Replicate side.
Please let us know if that's something that's possible, this would save a lot of manual labor every month for us and would make it much easier to scale out our implementations of Replicate on SQL Server at our company.
Thanks!
