When does a port get assigned if SQL Server is set to TCP Dynamic Ports

Posted on

Question :

I’m trying to determine when a dynamic port is assigned to SQL Server. If the TCP/IP Properties have the TCP Dynamic Ports enabled, does that port get assigned at the time the Server is installed or does it get assigned each time the instance is started. Is it therefore possible that a new port will get assigned each time the instance is restarted? I’m mostly curious about the 2008 through 2014 versions.

Answer :

Is it therefore possible that a new port will get assigned each time the instance is restarted?

Every time you start the SQLServer Instance, it uses the port that is allocated.

When a dynamic port is assigned to sql server instance, if the port is used by another program, then SQL Server chooses another port at the time of restart i.e. A dynamic port is chosen at first startup, and will generally remain the same through future restarts (stored in the Registry) – but if its used by another program then SQL server will choose a new port.

I generally prefer to use static ports for my sql server installation.

Refer to my answer to a sort of similar question.

Leave a Reply

Your email address will not be published.