-
I am asking two questions in this post. If I see activity in netstat with port 1433 for an unnamed instance, whose tcp addresses are not enabled, what does that mean?
-
What is the preferred way to configure a dynamic port that occurs with my second, named instance? Here are the details:
This post is slightly different than my first post, because in the last post I overlooked a critical detail with the default, unnamed instance, which was I could find no evidence that any of the TCP addresses were enabled. Here is some background:
I have a Windows 2016 server with an unnamed and a named MS SQL SERVER instances installed. TCP and Memory connections are turned on for both, until you get to the protocol section for TCP.
Both instances use the same IP address. The unnamed instance uses port 1433; and the named instance uses 49365.
The unnamed instance was installed first, and hence was assigned port 1433. The firewalls on the server and the client (Windows firewall) are set to allow the necessary UDP and TCP traffic.
Here are two pictures denoting most of the port configurations for the unnamed instance. Please note enabled is set to No for all settings, there is no dynamic port, and the port used is 1433.
This database belongs to a vendor, whose clients are able to connect to the database, so I am trying to figure out that happens in the hope that information will help me to connect to the named instance.
I've tried all suggestions in the prior post, read on-line, and my named database was able to do an import from an older SQL Server database, 2008.
Using netstat, I can see port 1433 is in use, so there is no point in co-opting it for the named instance. Should I just pick a port I know won't be used, or is not likely to be used?
Best Answer
I believe we have several misunderstandings here, so lets try to sort them out one by one and see if that clarifies the big picture in the end.
What you refer to as an "unnamed instance" is what we call a default instance. Just as FYI.
It is not the first installed instance that get port 1433. 1433 is a Well Known Port for the default instance. This is from back in the days when we couldn't have more than one instance (pre-SQL Server 2000).
A named instance get a port number from the Windows dynamic range at first startup. "Dynamic" does not mean a different port number at every startup. The assigned port is stored in the registry and is attempted to be used at following startups. "Dynamic" controls what happens if that fails (i.e., the port last used is now busy). "Dynamic" means "get a different port from Windows". The other option means "fail to start SQL server".
What you see in the GUI you posted as an IP being "disabled" is bad GUI presentation. The individual IP sections are being overridden by the bottom-most config (IPAll). Having a port there means all IP addresses are using that port (as far as I understand). Readers are welcome to comment/edit here.
As for your named instance, I suggest you set the port number under IPAll to 0 (and have nothing for the "static port") and re-start your SQL Server. It should now get a new port from the dynamic Windows range.