2

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)
6

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.

  • I am trying to use SQL Server Browser to direct me to my instance, but SQL Server Browser assumes that all of your dynamic ports are the same. I posted in my question about it. "I've read the documentation on SQL Server Browser and found that SQL Server Browser will return the first port that it finds." Furthermore the documentation in the link that I provided says "When a server has two or more network cards, SQL Server Browser returns the first enabled port it encounters for SQL Server." – SQLing4ever Apr 20 '18 at 14:48
  • 1
    So use a manually-specified port? – Max Vernon Apr 20 '18 at 14:58
  • I can, and I probably will, but if I get a dynamic port conflict SQL Server will then randomly give me 2 different port numbers again. I'm trying to automate this configuration across my whole development environment (a lot of instances) and I don't really want to try to have to hunt down and fix the port assignments if they get off. – SQLing4ever Apr 20 '18 at 15:29
  • What makes you think you'll get a port conflict if you manually assign them? Sorry, I don't get it. I manage 2500+ instances and have literally never had that problem. We assign all ports across the enterprise, BTW. – Max Vernon Apr 20 '18 at 15:47
  • Thank you for your suggestions. As I said before I will probably statically assign the ports now since somehow I have broken the dyanmic port assignments. I understand that this is a very edge case scenario and most people have not run into this, but the whole point of my forum question was to see whether or not someone HAD run into this problem and what they did to get SQL Server to work again. I think this is causing us both undue stress over a question that is edge case, I will just leave it alone. Thank you for the practical solution. – SQLing4ever Apr 23 '18 at 13:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.