SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

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.

Everyday SQL

Patrick Keisler is a Premier Field Engineer for Microsoft with over 15 years of SQL Server experience working in various fields such as financial, healthcare, and government. He currently holds an MCSE Data Platform certification, MCITP certifications in SQL Server 2008 for administration and development, and CompTIA Security+. You can follow him on Twitter or listen to him speak at various SQL Saturdays and user group meetings.


Leave a comment on the original post [www.patrickkeisler.com, opens in a new window]

Loading comments...