question

Narodul avatar image
Narodul asked

SQL Server 2016 Replication in Availability group misidentifies distributor

Hi,

This is a similar setup to another question asked here: SQL 2016 replication within Availability Groups, but the problem is different (hence the new question....)

Setup: Two node Windows cluster (MYSQL01, MYSQL02), each node running SQL Server 2016 SP2 CU12 Enterprise, with a default instance and a named instance (REPL01, REPL02).

I have an AG across the default instances containing published databases. The listener is defined as PUBLISTENER on port 1433.

I have another AG across the named instances, containing the distribution database (Transactional Replication). The listener is defined as DISTLISTENER_01\REPL01,1435.

Both AGs failover correctly, and appear to be working as planned.

However, when I create a publication, with a subscriber, the Log Reader fails with the following message:

Connecting to Distributor 'DISTLISTENER_01'
Agent message code 14114. The server 'MYSQL01' is not configured as a Distributor.

If I failover the distributor, the Log Reader (on Node 2 now) fails with

Connecting to Distributor 'DISTLISTENER_01'
Agent message code 14114. The server 'MYSQL02' is not configured as a Distributor.

Any ideas why the distributor is being misdirected to the default instance, rather than the named instance?

Thanks

replicationavailability groups
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Magnus Ahlkvist avatar image
Magnus Ahlkvist answered

I have only done transactional replication with publisher, subscribers and distributor in availability groups once and it wasn't super duper straight forward. I had a stage environment to test it in which was really good, since I had to remove replication and start over a couple of times (simply because I took shortcuts which didn't work). To add to the complexity, I did it with Subscriber and Distributor on Standard edition, which isn't actually supported but I got it working with a few failovers as I configured the distributor and subscribers.

Anyway: What you need to do is to follow the steps in these two articles really carefully. Don't miss any steps and don't change the order of any steps.

(They do describe setting up replication before setting up availability groups which isn't really necessary, you can setup the Availability Groups for publisher and subscriber before configurating replication. But with distribution, you'll have to setup distribution database on one node first and then add it to the AG, and then configure distribution on the publisher and subscribers.)

One more thing to keep in mind: You must configure subscribers to use pull subscription, push subscription doesn't work. What that means is the subscription agents need to be on the subscriber, not on the distributor. That is because with SQL Server 2016 and later, failing over the AG where the subscription database is will also failover the agent jobs to the other node.

Here's how to configure replication with Availability Groups:

https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/configure-replication-for-always-on-availability-groups-sql-server?view=sql-server-ver15

And Specifically how to configure distribution on Availability Groups:

https://docs.microsoft.com/en-us/sql/relational-databases/replication/configure-distribution-availability-group?view=sql-server-ver15

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.