I ran across an issue that I haven’t seen since SQL 2000 and had almost forgotten about. Let’s do a quick review of ports in SQL Server. A default instance of SQL Server uses port 1433. That’s just the default and you can change it to whatever you want. Bear in mind that if you change it you’ll have to define that port in your connection strings or enable the SQL Browser Service.
Speaking of the SQL Browser Service, you’ll need that for a named instance of SQL Server for the exact same reason. A named instance of SQL Server uses a dynamic port that can change with every restart of the SQL Server service. Take a look at the following screen shot of a named instance.
We are interested in the very last section which is the “IPAll” section. You can see that the “TCP Dynamic Ports” is set for 49402. That is what SQL Server has dynamically chosen for its port number. For a normal named instance we’ll see a port there, but the “TCP Port” setting would be blank. A default instance would be the other way around with a “TCP Port” defined but the “TCP Dynamic ports” being blank.
So what happens when you have a named instance using dynamic ports and you define a static port? Well the title of post already gave it away, but whatever you put into the “TCP Port” setting will override anything dynamic. I personally think it would make more sense to only be able to define one of the settings at a time. Maybe MS will change that one day where one of them is grayed out until the value is removed from the other setting.
Your statement on the static overriding the dynamic is slightly incorrect. If a static and a dynamic have a value in Configuration Management then SQL Server will listen on both static and dynamic ports. You can verify this by looking at the SQL Error Log and filtering for ‘listen’. Here is an article on why multiple TCP ports may be required.
http://technet.microsoft.com/en-us/library/ms189310(v=sql.90).aspx
You’re right and this is a great reason for me to re-read posts that I wrote ages ago before publishing them. I left out a key word in the title that should have said that the static takes precedence over the dynamic port when using SQL Browser. Although you are correct that SQL will listen on both ports (and thank you for pointing that out since it is a very important distinction) the SQL Browser service will tell connecting clients to use the static port. So the static port does indeed override, but only if you don’t specify the port and rely on the SQL Browser service for that. Of course even in that case it would still be listening on both ports and you could define and use the dynamic port by specifying it in your connection string.
Years back, when you connect to a named instance, you need explicitly put the port number in your connection or comma+port number. Now it doesn’t need it anymore. But not quite sure how local SSMS seeks which port is open in the remote server side.
Correct. The SQL Browser service is what makes that possible. It listens on UDP port 1434. So the client makes a call to the server on that port and the SQL Browser replies with the TCP port that the instance is running on. The client then connects on that port.