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)
9 Comments. Leave new
Can you please tell me the port number we should use, instead of 5022 to fix this error ?
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?
I am not clear what do you mean by “we corrected the port number to the ports on which SQL Server was listening,” Could you please explain?
I did not understand the solution
He means to change the SQL Server Read-Only Routing URL to end in 1433 instead of 5022 shown above. It works now for me.
Thanks for clarifying. It works now
Do we need to restart the sql services after change the port number 1433 instead of 5022
If you do not know what are the ports for your AO servers. Run the script provided in this Microsoft’s documentation page to calculate them (very useful).
https://docs.microsoft.com/en-us/archive/blogs/mattn/calculating-read_only_routing_url-for-alwayson
You may check the hard point name with the below script and then start the endpoint with alter script to make endpoint listening to connections.
USE MASTER;
SET NOCOUNT ON;
SELECT
endpoint_id AS EndpointID
, [name] AS EndpointName
, protocol_desc AS ProtocolUsed
, REPLACE([type_desc], ‘_’, ‘ ‘) AS EndpointType
, role_desc AS RoleType
, is_encryption_enabled AS IsEncryptionEnabled
, connection_auth_desc AS ConnectionAuthentication
, encryption_algorithm_desc AS EncryptionAlgorithm
FROM sys.database_mirroring_endpoints WITH (NOLOCK)
WHERE type = 4 –Database_Mirroring
alter endpoint state=started