Unable to listen on a certain port while setting up a mirror DB

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.