SQL Availability Group and Qlik Replicate Configuration

[KhanhMan]

General and some specific questions in regards to initial the Distributor database setup and failover

1.  How are you guys configuring the distributor database?  Is this just the standard local distributor setup? and not the one outlined here:  

https://learn.microsoft.com/en-us/sql/relational-databases/replication/configure-distribution-availa...

2.  the SQL Agent jobs that gets created when running the configured Qlik task for the first time are only created on the primary.  Do we need to manually create those tasks on the other servers in the AG as well?

3.  When failing to another server in the AG, we get an alert this alert, The process could not execute 'sp_replcmds' on 'Server01', but replication continues to work.  The alert goes away when i manually stop the agent from running.  Is this the proper way to handle this?

[Dana_Baldwin]

Hi @KhanhMan 

I am not able to directly address your questions but wanted to ensure you had reviewed our documentation on the subject as it may address at least some of your questions: Working with Microsoft SQL Server AlwaysOn Availability Groups | Qlik Replicate Help

Hope this helps,

Dana

[SushilKumar]

Hello team,

If our response has been helpful, please consider clicking "Accept as Solution". This will assist other users in easily finding the answer.

Regards,

Sushil Kumar

[KhanhMan]

I believe I figured it out but still running a few tests with failovers.  There's still a couple of items that aren 't working like it should and once those are figured out, I will outline the steps taken to get it to all work happily together.

[PGN]

I've commented on this before.  Prior to SQL 2016, distribution db was not supported in AG.  That changed in 2016 and is described in the link you provided. However, you can still configure a remote distribution db as described here:

https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/configure-replicat...

In that case, the jobs you mention in item 2 will be created on the remote distributor. The distributor is not protected by AG failover.  But when we've encountered any issues with the distributor agent jobs, Qlik Replicate will still function since it's basically looking for articles to search for in the TLOG. You may however, encounter issues with the TLOG not clearing the log_reuse_wait flag.

[deepaksahirwar · Accepted solution]

Dear @KhanhMan ,

Thank you for reaching out to us in the Qlik Community portal.

• When working with AlwaysOn Availability Groups, you need to specify the IP address/host name and access credentials of the AlwaysOn Listener in the connection properties Microsoft SQL Server source endpoint settings. You also need to set up Microsoft SQL Server for Replication as described in Setting up Microsoft SQL Server for replication. The Qlik Replicate Help also provides some prerequisites and limitations for working with AlwaysOn Availability Groups.

• According to my understanding , the SQL Agent jobs that get created when running the configured Qlik task for the first time are only created on the primary. You do not need to manually create those tasks on the other servers in the AG as well. However, you may need to configure the replicas to allow connections and set up a remote distributor.

• The alert you mentioned. It may be related to the latency or the backup logs in AlwaysOn Availability Groups. You may want to check the Qlik Replicate logs or contact Qlik Support for more assistance.

If our response has been helpful, please consider clicking "Accept as Solution". This will assist other users in easily finding the answer.

Regards,

Deepak 

[KhanhMan · Accepted solution]

A little bit of research, testing, and help from you folks here has yielded this setup for me and it looks to be working.

1. I had to configure a remote distributor using this link: https://www.sqlshack.com/configure-sql-server-replication-for-a-database-in-sql-server-always-on-ava...

2. The steps outlined in the link that was important for me was to make sure to execute the stored procedure for the redirected publisher.  This was the step that took care of the error, The process could not execute 'sp_replcmds', when I would fail over to other replicas in the AG.

3. After a few rounds of testing, I purposefully turned of the original primary and found that the Qlik replicate task would fail saying that it couldn't connect to the server that was powered off to mimic an automatic failover.

4. Searching the forums, I found that enabling this setting of the Internal Parameters  "AlwaysOnSharedSynchedBackupIsEnabled" fixed that error.