-> I was working on a 2-NODE cluster with physical nodes SERVER_A and SERVER_B. The SQL server instance SERVER_C\IN01 was not coming online in node SERVER_B.

-> SQL server version below,
Microsoft SQL Server 2005 – 9.00.5000.00 (Intel X86)
Dec 10 2010 10:56:29
Copyright (c) 1988-2005 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

-> I tried failing over SQL server instance from SERVER_B to SERVER_A to check if SQL server comes online on SERVER_A, the SQL services  failed on node SERVER_A as expected.

-> Opened the SQL server error log and found the below,

2016-12-01 23:27:12.73 Server      Server is listening on [ <IP_Address> <ipv4> 1519].
                2016-12-01 23:27:12.74 Server      Error: 26023, Severity: 16, State: 1.
                2016-12-01 23:27:12.74 Server      Server TCP provider failed to listen on [ 10.65.78.24 <ipv4> 1519]. Tcp port is already in use.
2016-12-01 23:27:12.74 Server      Error: 17182, Severity: 16, State: 1.
2016-12-01 23:27:12.74 Server      TDSSNIClient initialization failed with error 0x2740, status code 0xa.
2016-12-01 23:27:12.74 Server      Error: 17182, Severity: 16, State: 1.
2016-12-01 23:27:12.74 Server      TDSSNIClient initialization failed with error 0x2740, status code 0x1.
2016-12-01 23:27:12.74 Server      Error: 17826, Severity: 18, State: 3.
2016-12-01 23:27:12.74 Server      Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.
2016-12-01 23:27:12.74 Server      Error: 17120, Severity: 16, State: 1.
2016-12-01 23:27:12.74 Server      SQL Server could not spawn FRunCM thread.

-> It seems that TCP port is in use. Tried running netstats -Aon and found that the port 1519 was not in use.

-> Tried checking the SQL configuration manager for any aliases and found none on SERVER_A.

-> Checked the Configuration Manager -> Network Configuration -> Protocol for the Instance -> TCP/IP -> Properties -> I was able to see port 1519 set for all IP’s and TCP Dynamic Port / TCP port as well.

-> Removed the port 1519 from TCP Dynamic Port and started the SQL services and was able to see the same error, PORT IN USE!

-> Tried removing again and started the SQL services and the result was same. When tried refreshing it, the values were present for both Dynamic Port and TCP port. Understood that registry check pointing might cause this.

-> Ran the below command in elevated command prompt to disable registry checkpoint.

cluster res “SQL Server (IN01)” /removecheck: “Software\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLSERVER”

-> Removed the port 1519 from TCP Dynamic Port and started the SQL services and this time SQL services started.

-> Ran the below command from an elevated command prompt enable registry checkpoint.

cluster res “SQL Server (IN01)” /addcheck: “Software\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLSERVER”

-> I failed over the SQL server instance from SERVER_A to SERVER_B to check if SQL comes online on SERVER_B node.

-> The SQL services failed on node SERVER_B.

-> Checked the SQL server error log and did not get much info. Checked the event viewer and was able to understand that SQL services fails while trying for a connection. Suspected this could be due to alias issue.

-> Checked the configuration manager on server SERVER_B and as expected there  was an alias with below details.

Alias name : SERVER_C\IN01
Server name : SERVER_C\IN01
Port : 1476

-> I changed the port number to 1519 in the alias and the SQL services started as expected.

Thank You,
Karthik Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.