Dynamic Ports are being set to different ports

Question

On one of our instances of SQL Server (12.0.5571.0 if you really need to know) we use multiple IP addresses to connect.

• 127.0.0.1 - Dynamic Port set to 0. No static port set
• 172.16.0.5 - IP Address for the server that the SQL Server is on. Dynamic Port set to 0 (autgenerating). No static port assigned
• 172.16.0.6 - IP address for the SQL Server instance (that way teams just have to hit the IP address and not know which server they really are on). No Dynamic port assigned. Static port 1433

I've been playing with puppet and have been messing with the registry settings

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL12.InstanceNameX\MSSQLServer\SuperSocketNetLib\Tcp

to see if I could use puppet to automate the configuration of IP addresses and port assignments. I created a new registry path path called 'IP172016000006'

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL12.InstanceNameX\MSSQLServer\SuperSocketNetLib\Tcp\IP172016000006

which I templated from

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL12.InstanceNameX\MSSQLServer\SuperSocketNetLib\Tcp\IP1

but now whenever I start the instance of SQL Server my dynamic ports for 127.0.0.1 and 172.16.0.5 are different.

For example, I will stop the instance of SQL Server, go into

• 'SQL Server Configuration Manager'
  • navigate to the network configuration for the instance
    • tcp/ip
      • IP addresses
        • and set the 'TCP Dynamic Ports = 0 for the IP addresses 127.0.0.1 and 172.16.0.5.

After I restart SQL Server, the instance will be listening on 127.0.0.1:43340 and 172.16.0.5:43341 (it seems to always be 1 more then the other port)

I've read the documentation on SQL Server Browser and found that SQL Server Browser will return the first port that it finds.

I've also read the BOL for setting a static port for SQL Server, which funny enough covers how to set dynamic ports. The BOL says that SQL Server will try to connect to the port that is specified, but if it fails it will get a new port. That makes sense for once in a long while, but SQL Server is consistently having inconsistent dynamic port assignments.

I've removed the offending registry values, but SQL Server is still having troubles setting the dynamic ports to be the same. I've restarted the whole server. I've set the TCP dynamic ports to be '0' multiple times.

How do I get consistent dynamic ports again?

• Windows Server 2012 R2
• SQL Server 12.0.5571.0
• 8 Instances of SQL Server installed - 3 of which are clustered (it's a test config box for us DBAs; don't judge too harshly)

Answer 1

The point of dynamic ports is that you don't care what port the server runs on. SQL Server Browser directs clients to the dynamically chosen port.

If you don't want dynamic ports, just assign a port at the outset, then it will never change.