Blog Post

How to Make SQL Server Listen on Multiple Ports

,

Have you ever needed an instance of SQL Server to listen on multiple TCP/IP port numbers but didn't know how to implement it?  Well here's a quick step-by-step guide that will work for SQL Server 2005, 2008, and 2012.

Open SQL Server Configuration Manager.

On the left hand side, expand SQL Server Network Configuration.


Select your instance name.


On the right hand side, right click on TCP/IP and select Properties.


Click the IP Addresses tab.

Scroll down to the IPALL section.


Make sure the TCP Dynamic Ports field is empty.


In the TCP Port field, enter each port number separated by commas.


Click OK.

A pop up message will appear stating the changes will not take effect until the service is restarted.  Just click OK to close the message.

In the SQL Server Configuration Manager, on the left hand side, select SQL Server Services.


On the right hand side, right click the SQL Server service and select Restart.


After the restart is complete, SQL Server will be listening on all the port numbers you specified.

The easiest way to verify this is to look in the SQL Server Errorlog.


You will see one entry for each port number.

Server is listening on [ 'any' <ipv4> 10000].
Server is listening on [ 'any' <ipv4> 10001].
Server is listening on [ 'any' <ipv4> 10002].

If you happen to pick a port number that is already in use, then you will see an error like this 
within the SQL Server Errorlog, and SQL Server will fail to start.

Server TCP provider failed to listen on [ 'any' <ipv4> 10000]. Tcp port is already in use.

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating