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?
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)
