Replication with AlwaysOn AG misidentifies distributor

Question

Hi,

I have two servers (MYSQL1, MYSQL2), Windows-clustered, each with a default SQL instance (port 1433) and each with a named instance (MYSQL1\REPL1, MYSQL2\REPL2, both on port 1435).

SQL Server 2016 SP2 CU14 Enterprise.

I have an AG across the default instances, with my published databases. Listener: SQLLISTENER

I have an AG across the named instances, configured as the distributor (needs to be remote, hoping named instances on the same server is remote enough...) Listener: DISTLISTENER,1435

Database MyDB is published, and a table added to a transactional replication publication.

Node 1 is initially Primary for both publisher and distributor.

Both Log Reader and Snapshot Agent on the distributor fail with the same message: "The server 'MYSQL1' is not configured as a Distributor.". Well, that's correct, it's not!

If I fail the distributor over to Node 2, the message for the Log Reader updates to "The server 'MYSQL2' is not configured as a Distributor."

sys.servers on MYSQL2\REPL2 (now the Primary) shows that the data_source for 'repl_distributor' is indeed 'MYSQL2\REPL2'

Any thoughts as to why the Replication agents are trying to connect to the default instance on the distributor host server, rather than the defined instance?

Answer 1

Hi Simon,

Please have a try to setup an alias for the listener and the non-default port (Listener: DISTLISTENER,Port 1435 in your situation).

If the listener for the availability group hosting the distribution database is configured to use a non-default port, then its required to setup an alias for the listener and the non-default port.

You could refer more details and steps from below links:
Set up replication distribution database in Always On availability group
Walkthrough Publisher, Distributor, Subscriber in AlwaysOn Availability Groups

If the response is helpful, please click "Accept Answer" and upvote it.

Best regards
Melissa