July 12, 2011 at 10:40 am
Exactly. You don't configure it. SQL Server Browser for the DB engine listens on UDP 1434 and that cannot change because it is hard coded. The trick is simply to make sure that you have it turned on on both nodes in case of failover and to ensure that if you hard code your ports for an instance to include the port number across all IPs in SQL Server Configuration Manager (so that IP_ANY will work). You don't configure anything to use IP_ANY, this is built in behavior for the DB instance on a cluster. There isn't anything to configure. You just have to know that the Browser uses UDP 1434. And again, this is extremely practical info. I have had countless times where I cannot connect to a named instance by name but I can by port in the connection string. You immediately know it is a Browser issue. Sometimes I have resolved this by cycling the Browser service itself as it has gotten hung; other times it points to a network change. I have found that a router is locked down and they closed UDP 1434, causing an application to fail. By finding what I list above about name vs port, I can usually identify the problem. The biggest clue it is external, though, would be to RDP into the instance itself and try to connect by name. You will fail on a remote machine but succeed on the local box itself. At once you start looking for firewall or router issues.
July 12, 2011 at 10:41 am
SanDroid (7/12/2011)
Gianluca Sartori (7/12/2011)
SanDroid (7/12/2011)
So after all that research and posting and re-posting....
What answer to the question about configuring SQL Browser Service on a cluster fits the best? 1433, 1434, or IP_ANY?
None, I guess, as you can't configure it.
So true... 😉
Reading and posting on all the responses confused me even more. Not ccertain why I started using the words "configure". The QOTD was not asking if you could.
On clusters, the SQL Server Browser listens on which port?
Funny thing. This shows as Clustered in SQL Server configuration Manager on one of our SQL 2005 Clusters if you connect to the Cluster name.
July 12, 2011 at 10:43 am
Well as much as i don't want to believe i just ran
netstat on clustered SQL Server i see sqlbrowser.exe listening to *:*
Borik
July 12, 2011 at 10:44 am
Boris G (7/12/2011)
Well as much as i don't want to believe i just rannetstat on clustered SQL Server i see sqlbrowser.exe listening to *:*
Borik
Exactly... 😎
July 12, 2011 at 10:48 am
Can you please clarify the answer? I thought it was Port 1434 as well.
http://brittcluff.blogspot.com/
July 12, 2011 at 10:50 am
Boris G (7/12/2011)
Well as much as i don't want to believe i just rannetstat on clustered SQL Server i see sqlbrowser.exe listening to *:*
Borik
Uhmm, not exactly.
On my 2005 cluster I ran netstat -abno and the results read as follows:
Active Connections
Proto Local Address Foreign Address State PID
UDP 0.0.0.0:1434 *:* 5860
[sqlbrowser.exe]
*.* is not the local address, it's the foreign address.
-- Gianluca Sartori
July 12, 2011 at 10:51 am
I did this (netstat -a -b -n) on a non-clustered box:
UDP 0.0.0.0:1434 *:* 9140
[sqlbrowser.exe]
And on a clustered box:
UDP 0.0.0.0:1434 *:* 2308
[sqlbrowser.exe]
Looks like UDP 1434 in both cases to me.....
July 12, 2011 at 11:13 am
I think you right, i misread/assumed wrong...
Borik
July 13, 2011 at 2:30 pm
The correct answer is that it listens on all IP addresses on UDP 1434.
IP_ANY refers only listening on any IP address that is bound to the host including all cluster addresses, heartbeat, and loopback. It has nothing to do with which port is used.
I demand a refund!
Craig Outcalt
July 14, 2011 at 1:02 am
SQLBOT (7/13/2011)
I demand a refund!
On what?
The registration fee for SQLServerCentral? OK, done. 😀
The points for the question? You got it by posting your refund request. 😎
-- Gianluca Sartori
July 14, 2011 at 7:55 am
Points don't matter. What matters is that people understand the concept. And I find that the Browser is actually one of the most misunderstood concepts in SQL to begin with, and confusing the matter by linking IP_ANY to ports in this fashion just makes it harder to grasp.
July 14, 2011 at 10:29 am
I read the replies and it would be nice if the author of the question defended his answer.
July 15, 2011 at 1:13 pm
The "TSQL Default TCP endpoint" referred to in the Bol page Network Protocols and TDS Endpoints is not the endpoint of a TCP connection, but a TDS endpoint; "TSQL Default TCP" is the name of one of the six TDS "endpoints" created by default when SQL starts up. But it's so easy to parse this as "TSQL Default" plus "TCP endpoint" and take it for what MS might call a "TCP endpoint" (in other words a TCP port). That BoL page, in the very next sentence of after its use of "TSQL Default TCP endpoint" meaning a TDS endpoint (redundant use of "endpoint" there, " [o]is[/i] an endpoint) talks about a new "TCP endpoint", actually meaning "A TCP-based TDS endpoint", but TCP endpoint will be misunderstood just about every time. The use of "IP_ANY port" in the sentence after that suggests that the author of the page was as confused as most of its readers must be!
The answer's explanation refers not to that horrible page, but to the SQL Browser Service BoL page. That page contains the note
When listening on IP_ANY, when you enable listening on specific IPs, the user must configure the same TCP port on each IP, because SQL Server Browser returns the first IP/port pair that it encounters
which makes it anbsolutely clear that IP_ANY refers to IP Address, not to a port. But the sentence before the note could be misconstrued by someone not aware that no port has been assigned the name or function IP_ANY as referring to a port with that name.
The discussion so far has been amusing to read.
Being aware of the Network Protocols and TDS Endpoints page's faults I was pretty sure that most people would have great difficulty in understanding all this (most people at SQL Server Central don't have the advantage of having worked for fourteen years in data communications, where one learns to be very careful about exactly this sort of stuff) so I took a flier and got my point instead of giving what I am sure is the correct answer. I wonder if that is cheating?
Tom
July 15, 2011 at 2:54 pm
Gianluca Sartori (7/14/2011)
SQLBOT (7/13/2011)
I demand a refund!On what?
The registration fee for SQLServerCentral? OK, done. 😀
The points for the question? You got it by posting your refund request. 😎
I see now that I should have put a smiley face next to my refund demand to indicate it was humorous and friendly.
Craig Outcalt
July 15, 2011 at 7:19 pm
SQLBOT (7/15/2011)
Gianluca Sartori (7/14/2011)
SQLBOT (7/13/2011)
I demand a refund!On what?
The registration fee for SQLServerCentral? OK, done. 😀
The points for the question? You got it by posting your refund request. 😎
I see now that I should have put a smiley face next to my refund demand to indicate it was humorous and friendly.
Now not only have you a refund, you have a refund plus a bonus point;-). Can it get better? Yes :-)- I've got two bonus points :hehe:because I added this boring and pointless comment to my output. You can play the same game if you want to.
Tom
Viewing 15 posts - 46 through 60 (of 60 total)
You must be logged in to reply to this topic. Login to reply