Qlik Replicate and SQL Server AG HA Handling

shashi_holla — Fri, 29 Dec 2023 15:57:29 GMT

When dealing with SQL Server Availability Groups and how Qlik Replicate can interact with it there can be significant challenges.

By default, you specify the AG Listener and Qlik Replicate will read the backup information from the MSDB on the primary replica and then each secondary replica until the backup is found that contains the LSN that it is looking for.

There are situations where a secondary may not be setup for read access such as when it is kept in a down state to save on license costs. In this situation you can use a SQL Server Client alias to redirect the down replica back to the listener.

Qlik Replicate can be configured to connect to only the primary replica via the listener by using the internal parameter ‘AlwaysOnSharedSynchedBackupIsEnabled’ (Qlik Replicate: How to set Internal Parameters and what are they for?). This forces Qlik Replicate to treat the primary replica as a regular standalone SQL Server instance and will not try to connect to the secondary replicas.

Using the above internal parameter comes with its own issues. Namely if the transaction log backup is performed on a secondary replica the primary replica will not have the information in the MSDB for the backups being done. This is a deficiency in the SQL Server Availability Group environment where the information about backups are not synchronized.

As the internal name implies (AlwaysOnSharedSynchedBackupIsEnabled) it expects the backup information to be synchronized between the replicas. To accomplish this you must register the backup on the other replicas after it is performed. A simple way to do this is to run a script on the replica where the backup is being done that registers the backup information on the other replicas. See appendix A below for an example of the script.

In situations where a replica is not up and available to register a backup then you can register all backups located in a directory when the replica becomes available. See appendix B below for an example of that script.

article Qlik Replicate and SQL Server AG HA Handling in Official Support Articles

Qlik Replicate and SQL Server AG HA Handling

Appendix A

Appendix B

Environment