Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
In one of the scenarios, my mirror server wasn't able to connect using port 5022 but when I explicitly opened this port, it would start working as expected but I don't need to open any ports maunally while setting up a mirror.
Looked at the detour log from networking point of view and i could see that there are no requests for opening the port
20090808002116916 988 50.60: traceapi: 030 bind(70c,576150,10)
20090808002116916 988 50.60: traceapi: 030 Binding to port:1433
20090808002116916 988 50.60: traceapi: 030 bind(,,) -> 0
20090808002116916 988 50.60: traceapi: 030 Bind returned:0
20090808002116916 988 50.60: traceapi: 042 bind(778,9ef23d0,10)
20090808002116916 988 50.60: traceapi: 042 Binding to port:1434
20090808002116916 988 50.60: traceapi: 042 bind(,,) -> 0
20090808002116916 988 50.60: traceapi: 042 Bind returned:0
Also in SQL error log, it wouldn't show up to be listening on that specific port.
So sql server isn't sending the request to create and bind to port 5022. I added the port number to String value (REG_SZ) 5022 here HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.2\MSSQLServer\SuperSocketNetLib\Tcp\IPAll and this worked which is equal to opening the port manually.
SQL Server service broker should take care of this part.
Ran
select is_broker_enabled, service_broker_guid from sys.databases where database_id =2
Result -- 0 which is disabled.
GUID : AA0539D0-3614-4A14-AC76-EF4AFEE5AD00 -- So Service broked is not enabled on TempDB database.
Ran:
DROP ENDPOINT endpoint_mirroring
ALTER DATABASE tempdb SET NEW_BROKER
Now while looking at netstat, I saw that 5022 is on a time_wait state. So removed the port number from the registry which was earlier added and now all is well.