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.