Configuring MS-REPLICATION in an AAG setup

[Karun]

Hello,

I'm using MS SQL Server as a source endpoint for a Replicate task, and am using an AAG setup. I'm configuring MS-REPLICATION as per the Replicate User Guide, and have configured the Distribution database per instructions (https://help.qlik.com/en-US/replicate/May2021/Content/Replicate/Main/SQL%20Server/sqlserver_replicat...).

My question is, must MS-REPLICATION be configured separately for each node of AAG? If we do so, we will make use of "<Microsoft SQL Server Name> will act as its own distributor" option, will that work in AAG setup? Or is it enough to configure it only in the primary instance?

[I was earlier using active/passive cluster setup where I didn't encounter this issue, hence the question.]

Any help here is greatly appreciated.

[PGN · Accepted solution]

I strongly recommend using a separate distribution server.  You can (and should) set up the distributor on both AG nodes if you decide not to use a separate dist sever, but MS suggests using a separate one.  A video on how do to this is here:  https://www.youtube.com/watch?v=ujUuH-0fLWY .  There are also separate MS articles available as well.  The jobs that are setup after replication (REPL-Log Reader) are setup on the dist server and not each node.  If you setup in the dist on each node, the jobs will continue to fail on the secondary node. 

[Karun]

Thank you for the reply. But here is where I'm confused. Replicate asks us to select the option "<Microsoft SQL Server Name> will act as its own distributor". How does this reconcile with using a separate distribution server? 

The user guide doesn't talk about this specific scenario (setting up MS-REPLICATION in AAG), so I'm unsure about this part.

[PGN · Accepted solution]

Replicate doesn't care where the distributor is.  When the distributor is set up, Replicate will create the publications using whatever is configured on the server.  You can use a separate distributor regardless of whether you use Availability Groups.  As long as replication is configure properly, the publications will get created.

[Karun]

Thanks for the clarifications, will check this approach out.