locked
My SQL server port has changed after SQL restart. RRS feed

  • Question

  • Hi,

    I can see the dynamic port has changed after a server restart. I cant find the reason as to why it happened.Is it normal behaviour?

    Wednesday, August 22, 2018 1:02 AM

Answers

  • Hi Preetha7,

    Yes, it is. Because you are using the dynamic port. Please refer to TCP/IP Properties (IP Addresses Tab)

    At startup, when an instance of SQL Server is configured to listen on dynamic ports, it checks with the operating system for an available port, and opens an endpoint for that port. Incoming connections must specify that port number to connect. Since the port number can change each time SQL Server starts, SQL Server provides the SQL Server Browser Service, to monitor the ports, and direct incoming connections to the current port for that instance.

    Also, according to How to configure SQL Server to listen on a specific port

    Dynamic Port Allocation:

    If you configure an instance of SQL Server to use dynamic port allocation, and you have not yet restarted the instance of SQL Server, the registry values are set as follows:
    TCPDynamicPorts = Blank
    TCPPort = 0

    However, if you configure an instance of SQL Server to use dynamic port allocation, and you restart the instance of SQL Server, the registry values are set as follows:
    TCPDynamicPorts = Current port that is used
    TCPPort = Current port that is used 

    Thanks,
    Xi Jin.


    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.


    Wednesday, August 22, 2018 2:36 AM
  • My experience is that you *will* get the same port number for each re-start even when you have it set to "dynamic". The value is stored in the registry and used for the next start. 

    Unless something else is using that port, then it will choose some different port. So I suggest that you investigate why you have two services trying to use the same port number. And, of course consider setting it to "static" as well (which means that if something else uses that port, then SQL Server won't start).


    Tibor Karaszi, SQL Server MVP (Web Blog)

    Wednesday, August 22, 2018 6:56 AM

All replies

  • Hi Preetha7,

    Yes, it is. Because you are using the dynamic port. Please refer to TCP/IP Properties (IP Addresses Tab)

    At startup, when an instance of SQL Server is configured to listen on dynamic ports, it checks with the operating system for an available port, and opens an endpoint for that port. Incoming connections must specify that port number to connect. Since the port number can change each time SQL Server starts, SQL Server provides the SQL Server Browser Service, to monitor the ports, and direct incoming connections to the current port for that instance.

    Also, according to How to configure SQL Server to listen on a specific port

    Dynamic Port Allocation:

    If you configure an instance of SQL Server to use dynamic port allocation, and you have not yet restarted the instance of SQL Server, the registry values are set as follows:
    TCPDynamicPorts = Blank
    TCPPort = 0

    However, if you configure an instance of SQL Server to use dynamic port allocation, and you restart the instance of SQL Server, the registry values are set as follows:
    TCPDynamicPorts = Current port that is used
    TCPPort = Current port that is used 

    Thanks,
    Xi Jin.


    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.


    Wednesday, August 22, 2018 2:36 AM
  • My experience is that you *will* get the same port number for each re-start even when you have it set to "dynamic". The value is stored in the registry and used for the next start. 

    Unless something else is using that port, then it will choose some different port. So I suggest that you investigate why you have two services trying to use the same port number. And, of course consider setting it to "static" as well (which means that if something else uses that port, then SQL Server won't start).


    Tibor Karaszi, SQL Server MVP (Web Blog)

    Wednesday, August 22, 2018 6:56 AM