SQL Server 2014 FCI Add Port

Question

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!

Answer 1

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.