Sql-server – SQL Server 2014 FCI Add Port

clusteringsql server

Scenario:
Windows Server 2012R2 & SQL Server 2014 SP2, no CUs FCI adding a non-default port and keeping the default port

I made the change on both nodes via SQL Configuration Manager, however, neither failover or complete resource restart allows a connection to be made on the new port. We have tested connection to the port with the application, telnet and PoSH (Test-NetConnection) to no avail. The SysInternals PortMon utility does not show SQL Server listening on the added port. I did confirm the port shows in the registry on both nodes.

Edit:
A huge detail I should have listed but forgot to. When the service restarts, there is not indication that it is listening on the new port, just the default.

What did I miss or what else do I need to check?

Obviously, I cannot test any changes without some notice.

Thank you in advance!

Best Answer

You will also need to configure an additional TCP endpoint inside of SQL Server itself. If you run a query of

SELECT * FROM sys.tcp_endpoints;

I would expect you to see the default endpoint, as well as the Dedicated Admin endpoint. You will need to add another endpoint:

USE master;  
GO  
CREATE ENDPOINT [MyNewTCPEndpoint]  
STATE = STARTED  
AS TCP  
   (LISTENER_PORT = YourTCPPort, LISTENER_IP =ALL)  
FOR TSQL() ;  
GO

And then you may need to re-grant access to the default endpoint

GRANT CONNECT ON ENDPOINT::[TSQL Default TCP] to [public];

See the Microsoft documentation on configuring SQL for multiple TCP ports for full details.

Related Question