SQL SERVER – How to Listen on Multiple TCP Ports in SQL Server?

During my recent engagement of Comprehensive Database Performance Health Check, Sr. DBA asked if it is possible to run SQL Server on multiple TCP Ports. The answer is Yes, it is very much possible.

To configure the SQL Server Database Engine to listen on an additional TCP port first open SQL Server Configuration Manager. Once it is open expand SQL Server Network Configuration and then click Protocols for your instance and a further expand Protocols for and then select TCP/IP. After that, you can scroll down all the way and go to option IPAll and click on properties.

Now if you have a default installed you will see a default port 1433 listed there. If you want to add any additional port here, just add a new port with comma over there. For example, if you want to add port 1600 along with 1433, you can specify ports as 1433,1600.

Following image demonstrate how you can configure the same with SQL Server Configuration Manager.

SQL SERVER - How to Listen on Multiple TCP Ports in SQL Server? multipleIPListen1

Next, you must restart your SQL Server services to this new port to take effect. You can restart the SQL Server services from SQL Server Configuration Manager as well.

SQL SERVER - How to Listen on Multiple TCP Ports in SQL Server? multipleIPListen2

Once you restart your SQL Server services, you can specify your port number in SQL Server Management Studio in the format IP address (Comma) Port Number.

SQL SERVER - How to Listen on Multiple TCP Ports in SQL Server? multipleIPListen3

Makes sure that your firewall allows the IP address and Port to connect to the server. If you notice, it is indeed simple to configure SQL Server to listen to multiple TCP Ports.

Reference: Pinal Dave (https://blog.SQLAuthority.com)

, , ,
Previous Post
SQL SERVER – Quick Introduction to Startup Procedures
Next Post
SQL SERVER – How to Check If Instant File Initialization Enabled or Not?

Related Posts

6 Comments. Leave new

  • Uziel Bueno Ramirez
    July 25, 2018 8:51 pm

    Hi Pinal,

    Why do you have to do it in the IPAII only?

    Thanks!

    Reply
  • Hi,
    If we have 2 instances running oon same server is it possible to configure separate port for each instance? Please Clarify.

    Thanks..

    Reply
    • Mustafa ELmasry
      January 6, 2020 1:54 am

      Hi Raj
      what i know if i have two SQL Server instances on the same server we should configure for each one difference port , bot instance can not work with the same port

      Reply
  • Mustafa ELmasry
    January 6, 2020 1:54 am

    Thanks for this information but can i know what is the benefits from this option or what is the case that we can use on it this option

    Reply
  • Vinothkumar Venugopal
    June 27, 2020 9:59 am

    Do we have query to find ports use in multiple instances in Sql ?

    Reply

Leave a Reply

Menu