SQL dynamically-allocated ports don't change after a restart?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Spin - 18 Mar 2008 11:53 GMT

Gurus,

Running SQL Server 2005 SP2 configured for Windows Authentication on a
two-node cluster running Windows Server 2003 SP2 inside a single Active
Directory domain. SQL Server 2005 is configured for two named instances
and dynamically-allocated ports. I noticed that these two
dynamically-allocated ports, one set for 2661 and the other 2749, keep using
those same port numbers even after a reboot. I would have expected them to
change since they're "dynamically-allocated". Am I wrong?

Signature

Spin

Reply to this Message

Tibor Karaszi - 18 Mar 2008 14:33 GMT

SQL Server will only use some new port if the one stored in the registry is in use by somebody else.

Signature

Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

> Gurus,
>
[quoted text clipped - 4 lines]
> numbers even after a reboot. I would have expected them to change since they're
> "dynamically-allocated". Am I wrong?

Reply to this Message

Linchi Shea - 18 Mar 2008 15:06 GMT

By design, it doesn't keep changing its listening port. Here's what BOL says:

"Named instances, however, dynamically assign an unused TCP port number the
first time the instance is started. The named instance can also dynamically
change it's TCP port address on a subsequent startup if the original TCP port
number is being used by another application. SQL Server only dynamically
changes to an unused TCP port if the port it is currently listening on was
dynamically selected. "

Linchi

> Gurus,
>
[quoted text clipped - 5 lines]
> those same port numbers even after a reboot. I would have expected them to
> change since they're "dynamically-allocated". Am I wrong?

Reply to this Message