Dynamic Ports value changes

Hi,

Using SQL Configuration Manager, i have set my local instance to use TCP Dynamic Ports by setting the value under IPAll to be 0 (the value TCP Port is blank). However, when i start up the server this value gets set to a specific port. ie Before startup TCP Dynamic Ports = 0, After startup TCP Dynamic Ports = 2832. This value persists throughout SQL Server restarts.

Is this behaviour correct as I would have expected this value to stay 0?

I am using SQL Standard, SP2. SQL Browser is running.

Thanks in advance!

No the value will not stay to 0. The value displayed while SQL is running is the current port that SQL is listening on.

When setting SQL Server for a dynamic port, SQL Server selects an available port at random when starting up. That TCP port is used for the duration of the time that SQL Server is running. Upon shutdown and restart of SQL Server another port is selected.

|||Thanks very much for your response.

I'd just like to clarify that although the port selected will replace the 0 in the configuration GUI, the next time it starts up, it would still be aware that it is in dynamic ports "mode". I just want to ensure the following scenario would not happen:-

Dynamic Ports = 0
SQL Server starts up and dynamically assings port 2234
Dynamic Ports = 2234
Shut down SQL Server
New application starts and listens on port 2234
SQL Server tries to start up on port 2234 and fails.
Manually have to reset dynamic ports = 0

If this isn't the case thats ok though i find it a little confusing that the value doesn't stay at 0 in the GUI as you don't really have any indication that its set to use Dynamic Ports do you?
|||

Correct, it is still in dynamic mode.

The only way to know that it's using dynamic ports is that the number is in the "TCP Dynamic Ports" section not in the "TCP Port" section.