SQL SERVER – Error: 9642 – An error occurred in a Service Broker/Database Mirroring transport connection endpoint

During my consultancy engagement, my client’s DBA team was checked ERRORLOG and asked me the possible cause of below error about service broker.

DateTime spid74s      Error: 9642, Severity: 16, State: 3.
DateTime spid74s      An error occurred in a Service Broker/Database Mirroring transport connection endpoint, Error: 8474, State: 11. (Near endpoint role: Target, far endpoint address: ”)

From the above message, we can figure out that there is some system session in SQL which is trying to connect to some endpoint. I checked and they confirmed that they were not using service broker. But they were using Always On Availability Group.

I checked data synchronization and it was fine. Rows which were modified on primary was reaching to secondary as well. This means there was no issue with data movement also.

While enquiring further I learned that they are using a Read-Only Routing feature that was not working properly.

WORKAROUND/SOLUTION

As soon as I checked the availability group properties and checked Read-Only Routing configuration, everything has fallen in place and started making sense.  Here is what I saw in the properties.

Do you see a problem here?

SQL SERVER - Error: 9642 - An error occurred in a Service Broker/Database Mirroring transport connection endpoint ao-ror-sb-err-01

When the request hits primary, it finds the routing and request try to go to 5022 port on secondary. That port is not meant for client connectivity and hence the error.

Whenever we try to connect to a listener and perform read-only routing at the very same time, we were seeing mirroring endpoint error.

To fix the issue, we corrected the port number to the ports on which SQL Server was listening, After fixing the issue, routing started working and we also found that error disappeared.

The same error can be reproduced if you try to connect to SQL Server on a port on which SQL is listening but not for client connectivity.

Reference: Pinal Dave (https://blog.sqlauthority.com)

, , ,
Previous Post
SQL SERVER – FIX: Install Error: A Network-Related or Instance-Specific Error Occurred While Establishing a Connection to SQL Server
Next Post
SQL SERVER – Installation Error: System.ArgumentNullException – Value Cannot be Null

Related Posts

2 Comments. Leave new

  • Babu Venugopalan
    January 22, 2020 8:46 am

    Can you please tell me the port number we should use, instead of 5022 to fix this error ?

    Reply
  • What do you mean by changing the port 5022 to the port SQL is listening to? Do you mean the Listener port no in this case?

    Reply

Leave a Reply

Menu