Oct 042012
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.

  2 Responses to “How to Make SQL Server Listen on Multiple Ports”

  1. This is WINDOWS. Most people aren’t running SQL server on windows. Most people run servers on Linux, so this is completely inapplicable to most people.

    • If you’re trying to say most people aren’t running Microsoft SQL Server on Microsoft Windows, I do believe you are mistaken. Especially in the era these screenshots were created.

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>



This site uses Akismet to reduce spam. Learn how your comment data is processed.