Recently, one of my clients encountered an issue with their SQL Server database mirroring after patching their SQL instances. They approached me for assistance, and after analyzing the problem, I asked them to share the ERRORLOG file to identify the root cause of the issue.
In the ERRORLOG file, I found some interesting messages that shed light on the problem:
2016-11-03 22:11:04.32 spid24s The Service Broker protocol transport is disabled or not configured. 2016-11-03 22:11:04.33 spid18s Database mirroring has been enabled on this instance of SQL Server. 2016-11-03 22:11:04.32 spid24s Error: 26023, Severity: 16, State: 1. 2016-11-03 22:11:04.32 spid24s Server TCP provider failed to listen on ['any' 5022]. Tcp port is already in use. 2016-11-03 22:11:04.50 spid24s Error: 9692, Severity: 16, State: 1. 2016-11-03 22:11:04.50 spid24s The Database Mirroring protocol transport cannot listen on port 5022 because it is in use by another process.
Upon further investigation, I asked my client if they had multiple instances of SQL Server and whether any of them were also using port 5022. They confirmed that they had other instances, but those instances were not using mirroring.
To confirm this, I suggested running the following command from the command prompt:
netstat -aon | find /I "5022"
The output of the command revealed that port 5022 was indeed in use by another process with PID 1452.
After identifying the conflicting process, we found that it belonged to another instance of SQL Server. Although that instance was not using database mirroring, it had an endpoint created on the same port.
To resolve the issue, I recommended two possible solutions:
- Remove the endpoint from the other instance of SQL Server.
- If they wanted to use mirroring, they could choose a different port for the endpoint.
Once my client deleted the endpoint from the other instance of SQL Server and restarted the endpoints on the affected instance, the database mirroring functionality was restored.
It is important to ensure that the ports used by different SQL Server instances do not conflict, especially when enabling features like database mirroring. By identifying and resolving port conflicts, you can avoid disruptions in your SQL Server environment.