I cannot change sql server's default port (1433)

Question

I want to add another port to default 1433 port of sql server. So I opened the configuration manager, selected Protocols for MSSQLSERVER and then double clicked on TCP/IP to see its properties. Then I selected the IP Addresses tab and scrolled down right to the IPAll section. and then I change 1433 to 1433,1450 so that I could also connect to the server using 1450 port. The problem is this change requires to restart sql server service and I cannot start the server after stopping it because of the following error:

The request failed or the service did not respond in a timely fashion. Consult the event log or other applicable error logs for details.

I see the following error in event log:

SQL Server could not spawn FRunCM thread.

If I remove 1450 from tcp ports, sql server will be able to run again without any problem.

I have done this before but I don't understand why I cannot do it on this server.

Answer 1

You need to add a new TDS endpoint in the server first, see How to: Configure the Database Engine to Listen on Multiple TCP Ports:

CREATE ENDPOINT [CustomConnection]
STATE = STARTED
AS TCP
   (LISTENER_PORT = 1450, LISTENER_IP =ALL)
FOR TSQL() ;
GO

then configure the engine to listen on both ports, as you're doing. Make sure you choose a free port and make sure the port is allowed in Firewall.

The usual reason to add multiple listening ports is to affinitize clients to NUMA or soft-NUMA, see How to: Map TCP/IP Ports to NUMA Nodes and How to: Configure SQL Server to Use Soft-NUMA.