July 12, 2011 at 7:51 am
This whole post was just wrong. Not enough coffee yet. Content has been removed by Author.. 😛 😎
July 12, 2011 at 7:58 am
Great question - It seems that the difference between an IP client application listening "PORT" and a Server IP listening port not well understood, especially on cluster server.
July 12, 2011 at 8:09 am
SanDroid (7/12/2011)
Great question - It seems that the difference between an IP client application listening "PORT" and a Server IP listening port not well understood, especially on cluster server.
I don't see how your explanation affects the discussion. The browser is listening for incoming clients. If you connect to server\instancename, the native client goes to the IP address resolved by "server" on port UDP 1434 and asks it which port instancename is on, and the browser replies back with the proper port for the client to use, at which point all further communication is on that port. If the browser is off -- or if anything is blocking UDP 1434 along the path -- then you cannot connect to a named instance by name. However, you CAN connect by hardcoding the port as in server, portnumber (ex SERVER, 5566). This is behavior I see day in and day out. IP_ANY just means that if a server has a ton of instances, and on a cluster each instance has its own IP address, the browser must listen on all IP addresses. But in every case it is listening on UDP 1434. IP_ANY is an address, not a port.
July 12, 2011 at 8:09 am
SanDroid (7/12/2011)
This whole post was just wrong. Not enough coffee yet. Content has been removed by Author.. 😛 😎
:smooooth:
-- Gianluca Sartori
July 12, 2011 at 8:23 am
SanDroid (7/12/2011)
This whole post was just wrong. Not enough coffee yet. Content has been removed by Author.. 😛 😎
Ah, cool, then only pay attention to factual content in my reply then. 🙂
July 12, 2011 at 8:26 am
jeff.mason (7/12/2011)
SanDroid (7/12/2011)
Great question - It seems that the difference between an IP client application listening "PORT" and a Server IP listening port not well understood, especially on cluster server.IP_ANY just means that if a server has a ton of instances, and on a cluster each instance has its own IP address, the browser must listen on all IP addresses. But in every case it is listening on UDP 1434. IP_ANY is an address, not a port.
We have three cluster servers that use this "feature". Each has only one SQL instance listening to 6 IP adress, 5 host names, 7 differant IP/UDP port cobinations for SQL, SSRS, SSAS, and SSIS application connections for inbound and outbound connections thanks to this FACT:
IP_ANY means you can to listen to or connect with ANY IP / ANY PORT registered to the cluster server service.
UDP 1434 is good for one part of one service. I was commenting to them all.
The SQL Browser service plus IP_ANY helps everything work better on the cluster. Not just inbound UPD 1434 requests to the MS SQL service.
If you are doing specific IP / port connections from your client to just your SQL server, like the ones you mention in your post, BOL says you do not need to do this, and most likely can disable the SQL Browser Service.
If you need all the Client/Server connectivity options available on your SQL cluster, this is they way it is done.
July 12, 2011 at 8:43 am
SanDroid, let's put it this way:
From a client you connect to a named instance by instance name and not by port.
On the cluster, on both nodes, you close UDP 1434 on the local firewall.
Can you identify a case where you can successfully connect by name and not by hard-coded port to a named instance on a cluster without using UDP 1434?
If not, I stand that UDP 1434 is the port that the browser listens for incoming clients on.
July 12, 2011 at 8:58 am
jeff.mason (7/12/2011)
SanDroid, let's put it this way:From a client you connect to a named instance by instance name and not by port.
On the cluster, on both nodes, you close UDP 1434 on the local firewall.
Can you identify a case where you can successfully connect by name and not by hard-coded port to a named instance on a cluster without using UDP 1434?
If not, I stand that UDP 1434 is the port that the browser listens for incoming clients on.
Well, the SQL Browser service also listens on port TCP 2382, to tell the truth (for Analysis Services).
If IP_ANY is meant as "all IP addresses on the machine" + "any TCP or UDP port the SQL Browser can listen on", I can agree with the correct answer.
-- Gianluca Sartori
July 12, 2011 at 9:08 am
jeff.mason (7/12/2011)
SanDroid, let's put it this way:From a client you connect to a named instance by instance name and not by port.
On the cluster, on both nodes, you close UDP 1434 on the local firewall.
Can you identify a case where you can successfully connect by name and not by hard-coded port to a named instance on a cluster without using UDP 1434?
If not, I stand that UDP 1434 is the port that the browser listens for incoming clients on.
Funny you would go into all that. Direct UDP 1434 connections to the SQL server service is not what this is about.
http://msdn.microsoft.com/en-us/library/ms181087.aspx
What the QOTD, the article above, and my posts are about is this:
If you do not install SQL Browser service on both Cluster nodes and enable it to use the Clustered resource IP_ANY port as mentioned in both SQL Server and Cluster server service documentation you will not be able to service connections that are NOT Direct UDP 1434 connections to the SQL server service. Although what you describe, along with shutting off the SQL Browser service is a good way to get the same result on a single system that does not have cluster service installed.
When you fail from one cluster node to another if you have not set this up right you will notice certain kinds of connections that are serviced by SQL Server Browser service will stop working.
Do connections with a direct IP/port combination connection entered at the client still work? YES.
Does IP_ANY mean that SQL Browser listens to some differant UPD port to service connections? NO.
Does IP_ANY allow the SQL Browser to service connections on all IP's registered to both nodes of a cluster server? YES.
Does IP_ANY do this by being a clustered resource that can service a connection to ANY port on any IP adress on ANY cluster node? YES.
Forgot to add one last BOL quote:
If the SQL Server Browser service is not running, you are still able to connect to SQL Server if you provide the correct port number or named pipe. For instance, you can connect to the default instance of SQL Server with TCP/IP if it is running on port 1433.
July 12, 2011 at 9:09 am
Gianluca Sartori (7/12/2011)
jeff.mason (7/12/2011)
SanDroid, let's put it this way:From a client you connect to a named instance by instance name and not by port.
On the cluster, on both nodes, you close UDP 1434 on the local firewall.
Can you identify a case where you can successfully connect by name and not by hard-coded port to a named instance on a cluster without using UDP 1434?
If not, I stand that UDP 1434 is the port that the browser listens for incoming clients on.
Well, the SQL Browser service also listens on port TCP 2382, to tell the truth (for Analysis Services).
If IP_ANY is meant as "all IP addresses on the machine" + "any TCP or UDP port the SQL Browser can listen on", I can agree with the correct answer.
The problem with that reasoning is that it listens on TCP 2382 on all versions of the browser, not just on clustered versions.
Again, though, IP_ANY still doesn't indicate which port is used. It's just a shortcut to tell the browser to use all valid IP addresses. The application is still responsible for selecting ports, and the use of 1434 (and 2382) doesn't change because it is clustered.
July 12, 2011 at 9:21 am
SanDroid (7/12/2011)
jeff.mason (7/12/2011)
SanDroid, let's put it this way:From a client you connect to a named instance by instance name and not by port.
On the cluster, on both nodes, you close UDP 1434 on the local firewall.
Can you identify a case where you can successfully connect by name and not by hard-coded port to a named instance on a cluster without using UDP 1434?
If not, I stand that UDP 1434 is the port that the browser listens for incoming clients on.
Funny you would go into all that. Direct UDP 1434 connections to the SQL server service is not what this is about.
http://msdn.microsoft.com/en-us/library/ms181087.aspx
What the QOTD, the article above, and my posts are about is this:
If you do not install SQL Browser service on both Cluster nodes and enable it to use the Clustered resource IP_ANY port as mentioned in both SQL Server and Cluster server service documentation you will not be able to service connections that are NOT Direct UDP 1434 connections to the SQL server service. Although what you describe, along with shutting off the SQL Browser service is a good way to get the same result on a single system that does not have cluster service installed.
When you fail from one cluster node to another if you have not set this up right you will notice certain kinds of connections that are serviced by SQL Server Browser service will stop working.
Do connections with a direct IP/port combination connection entered at the client still work? YES.
Does IP_ANY mean that SQL Browser listens to some differant UPD port to service connections? NO.
Does IP_ANY allow the SQL Browser to service connections on all IP's registered to both nodes of a cluster server? YES.
Does IP_ANY do this by being a clustered resource that can service a connection to ANY port on any IP adress on ANY cluster node? YES.
NO!
IP_ANY is JUST an address!
IP_ANY just tells the system to use all IP addresses. It specifies nothing about the port.
The question is, "On clusters, the SQL Server Browser listens on which port?"
The answer (assuming you mean the DB engine and not SSAS) is ALWAYS UDP 1434. It may listen on UDP 1434 of IP_ANY, but still UDP 1434.
Why am I making such a fuss? Not for points, don't care about that. It's about accuracy.
Why does accuracy matter? In the real world where another team controls firewalls, you HAVE to know what ports need to stay open, ESPECIALLY given that it is UDP which in my experience is closed by default more often than not. You can't tell a firewall engineer to open IP_ANY on a particular cluster. You have to tell them port number, and for the SQL Server Browser for the database engine this is always UDP 1434, even if it is clustered. All IP_ANY gives you is that you don't need a copy of the browser for each instance, it can service all IPs and all instances. But you have to keep the port concept separate, because ports are how the outside world get at your machine.
Also, 1434 is not to the service, it is to the browser. You are thinking of 1433 for the service.
July 12, 2011 at 9:49 am
Thanks for the question. I learned a lot not only from the question but especially from the discussion. I did't know what IP_ANY was.
---------------------------------------------------------------------
Use Full Links:
KB Article from Microsoft on how to ask a question on a Forum
July 12, 2011 at 9:51 am
Trey Staker (7/12/2011)
Thanks for the question. I learned a lot not only from the question but especially from the discussion. I did't know what IP_ANY was.
I'm beginning to think that I still don't.
Up until today I thought it was IP (Internet Protocal) _ANY. IP is an address 192.168.0.1
PORTS are either TCP or UDP ports and are services operating on top of IP.
Or at least that's what I thought, now I think I want to go back to IPX/SPX or Banyan vines or something.
July 12, 2011 at 9:53 am
interesting question
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 12, 2011 at 9:56 am
jeff.mason (7/12/2011)
IP_ANY is JUST an address!IP_ANY just tells the system to use all IP addresses. It specifies nothing about the port.
As was already pointed out today by someone else earlier IP_ANY is much more than just an Address or a Port.....
http://msdn.microsoft.com/en-us/library/ms191220.aspx
By default, TCP connections use the TSQL Default TCP endpoint. If a new (user-defined) TCP endpoint is created for a specific TCP port, connections to that TCP port will connect to that new endpoint. If a new TCP/TSQL endpoint is created using IP_ANY as the port, then TCP connections will connect to that new endpoint.
...
2.If there is not an exact match, the TCP port is checked against all IP_ANY endpoints, and if the TCP port is listed, that endpoint is used.
Viewing 15 posts - 16 through 30 (of 60 total)
You must be logged in to reply to this topic. Login to reply