none
SQL Named Instances TCP Ports RRS feed

  • Question

  • Hi Guys,

    need some opinion on the following setup:

    Two SQL named istances (SQL Server 2016), the one listening on TCP Port 1433 and trhe other is configured to listen on TCP 1434. 

    Is this OK or is not a good idea to use TCP 1434 when the SQL Browser Service needs UDP 1434?

    I already went through:

    Troubleshoot Connecting to the SQL Server Database Engine

    but it only says this:

    "This message indicates that this instance of SQL Server is listening on all the IP addresses on this computer (for IP version 4) and is listening to TCP port 1433. (TCP port 1433 is usually the port used by the Database Engine. Only one instance of SQL Server can use a port, so if there is more than one instance of SQL Server installed, some instances must use other port numbers.)"

    Can I use TCP 1434 for the second named instance? What other static ports can I use? What do you use?

    Many thanks in advance and regards!


    (Please take a moment to "Vote as Helpful" and/or "Mark as Answer" where applicable. This helps the community, keeps the forums tidy, and recognizes useful contributions. Thanks!) Blog: https://blog.pohn.ch/ Twitter: @StoyanChalakov


    Friday, June 28, 2019 8:38 AM

Answers


  • Can I use TCP 1434 for the second named instance? What other static ports can I use? What do you use?


    I have not tired that but I believe you may use. As you asked, this would not be a good idea to go with. The reason is, UDP port 1434 is used by SQL Server browser service when it start so if you are using 1434 for SQL Server you might get conflict with browser service when both starts at same time. You can still use 1434 port for database engine instance and keep browser service disabled but below needs to be take care if browser service is disabled. Please read Browser Service

    • Any component that tries to connect to a named instance without fully specifying all the parameters (such as the TCP/IP port or named pipe).

      Any component that generates or passes server\instance information that could later be used by other components to reconnect.

      Connecting to a named instance without providing the port number or pipe.

      DAC to a named instance or the default instance if not using TCP/IP port 1433.

      The OLAP redirector service.

      Enumerating servers in SQL Server Management Studio, Enterprise Manager, or Query Analyzer.

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP


    Cheers,

    Shashank


    Monday, July 1, 2019 7:51 AM
    Moderator

All replies

  • From the web: 

    TCP port 1433 is the default port for SQL Server. This port is also the official Internet Assigned Number Authority (IANA) socket number for SQL Server. Client systems use TCP 1433 to connect to the database engine; SQL Server Management Studio (SSMS) uses the port to manage SQL Server instances across the network. You can reconfigure SQL Server to listen on a different port, but 1433 is by far the most common implementation.

    TCP port 1434 is the default port for the Dedicated Admin Connection. You can start the Dedicated Admin Connection through sqlcmd or by typing ADMIN: followed by the server name in the SSMS Connect to Database Engine dialog box.

    UDP port 1434 is used for SQL Server named instances. The SQL Server Browser service listens on this port for incoming connections to a named instance. The service then responds to the client with the TCP port number for the requested named instance.

    TCP port 2383 is the default port for SQL Server Analysis Services.

    TCP port 2382 is used for connection requests to a named instance of Analysis Services. Much like the SQL Server Browser service does for the relational database engine on UDP 1434, the SQL Server Browser listens on TCP 2382 for requests for Analysis Services named instances. Analysis Services then redirects the request to the appropriate port for the named instance.

    TCP 135

     TCP port 135 has several uses. The Transact-SQL debugger uses the port. TCP 135 is also used to start, stop, and control SQL Server Integration Services, although it is required only if you connect to a remote instance of the service from SSMS.

    TCP 80 and 443

    TCP ports 80 and 443 are most typically used for report server access. However, they also support URL requests to SQL Server and Analysis Services. TCP 80 is the standard port for HTTP connections that use a URL. TCP 443 is used for HTTPS connections that use secure sockets layer (SSL).

    Unofficial TCP Ports

    Microsoft uses TCP port 4022 for SQL Server Service Broker examples in SQL Server Books Online. Likewise, BOL Database Mirroring examples use TCP port 7022.

    This summary should cover your most pressing port needs. You can find more detailed information about the TCP and UDP ports that SQL Server uses in the Microsoft article "Configure the Windows Firewall to Allow SQL Server Access."


    Friday, June 28, 2019 8:50 AM
  • Hi,

    thanks. So, if I got this correct, 1434 TCP is a "No Go". What ports are you using then when configuring multiple instances? Ports from the dynamic port range are of course an option. What if I take 1450 (TCP), considering the fact that there will be no other process on the server that will listen on 1450?

    Many thanks.


    (Please take a moment to "Vote as Helpful" and/or "Mark as Answer" where applicable. This helps the community, keeps the forums tidy, and recognizes useful contributions. Thanks!) Blog: https://blog.pohn.ch/ Twitter: @StoyanChalakov


    Friday, June 28, 2019 8:59 AM
  • Have a look at this chat Multi Instances it will give you a lot. However, I'd try using dynamic ports. 

    Please mark as answer if this post helped you

    Friday, June 28, 2019 9:10 AM
  • Hi,

    I can use dynamic ports too, but that was not the actual question I asked. It was: Can I use TCP ports, which are not from the dynamic port range, a port like 14xx for example. Of course, if there are no other processes on that sever, which leverage the port. 

    So, TCP 1434 is used by the DAC, then I assume I cannot use it. I know that discussion you've posted, have researched a bit before asking, but it only states that you cannot use tcp 1433 for multiple instances, not quite the answer to my question. 

    So if I have to put it in a clearer way:

    Can I run one instance on 1433 TCP and the other on 1453 TCP for example?

    Thanks!


    (Please take a moment to "Vote as Helpful" and/or "Mark as Answer" where applicable. This helps the community, keeps the forums tidy, and recognizes useful contributions. Thanks!) Blog: https://blog.pohn.ch/ Twitter: @StoyanChalakov

    Friday, June 28, 2019 9:20 AM
  • Hi Stoyan Chalakov,

     

    >>Can I run one instance on 1433 TCP and the other on 1453 TCP for example?

     

    As long as the 1453 port is not used by other process, you can use 1453 for the named instance. The dynamic port range you are talking about is only the range that sql server specifies when you don't specify a specific port. (The ports in this range are rarely occupied.) As long as the port you specify is not occupied by other processes, you can use it for the named instance in the sscm:

     

     

     

    Hope this could help you .

    Best regards,

    Dedmon Dai


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, July 1, 2019 7:01 AM

  • Can I use TCP 1434 for the second named instance? What other static ports can I use? What do you use?


    I have not tired that but I believe you may use. As you asked, this would not be a good idea to go with. The reason is, UDP port 1434 is used by SQL Server browser service when it start so if you are using 1434 for SQL Server you might get conflict with browser service when both starts at same time. You can still use 1434 port for database engine instance and keep browser service disabled but below needs to be take care if browser service is disabled. Please read Browser Service

    • Any component that tries to connect to a named instance without fully specifying all the parameters (such as the TCP/IP port or named pipe).

      Any component that generates or passes server\instance information that could later be used by other components to reconnect.

      Connecting to a named instance without providing the port number or pipe.

      DAC to a named instance or the default instance if not using TCP/IP port 1433.

      The OLAP redirector service.

      Enumerating servers in SQL Server Management Studio, Enterprise Manager, or Query Analyzer.

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP


    Cheers,

    Shashank


    Monday, July 1, 2019 7:51 AM
    Moderator