sql cluster failed on failed over with sql browser service set disabled

Hi
We run sql2008 with named instance with our cluster SQL2008, however we did not use dynamic port, in the network configuration manager, the IPAll have been set to listen to port 5566.
The problem happens when I start to failover the node with the node where the sql browser set to disabled and stopped. SQ server did not comes online.
what are the solutions I can do in order to get sql named instance work with specific port assigned and sql browser service set to stopped ?
motioneyeAsked:
Who is Participating?
 
Ryan McCauleyConnect With a Mentor Data and Analytics ManagerCommented:
Why are you stopping the SQL Browser service in the first place? Maybe there's a legitimate reason for it, but if it works when the service is online and doesn't work when it's not online, why not just leave it up?

Are dynamic ports enabled on the SQL Instance? You can check in the SQL Server Configuration Manager for that instance - open the network configuration, select the instance and view the TCP configuration, and then scroll to the bottom of the "IP Addresses" tab - if the "Dynamic ports" section is blank, dynamic ports are not enabled, and if it has a value, they are. The only thing I can think of is that your SQL Server is trying to register with the browser because it's using a dynamic port (and without registering, no client would be able to connect), though I wouldn't think that would actually lock up the SQL Instance during startup.
0
 
Ryan McCauleyConnect With a Mentor Data and Analytics ManagerCommented:
The SQL Browser does only a single thing - takes connection requests that include an instance name instead of a port - and routes them to the correct SQL port. To do this, it only listens on UDP port 1434. It's not actually required to run at all - you can specify the port number explicitly and connect directly to the instance, without using the browser.

That said, cluster instances are a bit funny to work with - even though each database instance as a dedicated IP address, it still requires you to connect using the instance name and use the browser, which listens on that port for every virtual SQL Server IP. To set the port explicitly, you can use the following instructions:

http://msdn.microsoft.com/en-us/library/ms177440.aspx

However, it sounds like you've already done this, setting all your instances to listen to port 5566 on their dedicated IP addresses. When you say the SQL Server "doesn't come online", do you mean that the service won't start, or that you're never able to connect to it? If it doesn't come online, that's a more serious problem, but one not involving the browser or connectivity at all, as the SQL service doesn't care if a port is open when it brings the databases up. If it comes up but you can't connect remotely over that port, are you sure the firewall isn't blocking that port on the second node? Can you connect locally, using a command line tool like SQLCMD?

I've done this same configuration before and I go through the steps here:

http://trycatchfinally.net/2009/05/accessing-a-clustered-sql-server-instance-without-the-instance-name/

It sounds like this might be what you're doing - in that case, make sure you note the caveat: the older OLE client will balk at connecting this way, without a port or instance name, with an "Unknown Instance" error. You'll have to provide either the port number or the instance name, even though you're connecting to a dedicated IP address. For some reason, the SQL Native Client is fine this way, and doesn't care that you're not giving an explicit instance name.
0
 
DBAduck - Ben MillerConnect With a Mentor Principal ConsultantCommented:
Just remember that cluster or not, if port 5566 is open by some other application on any node and in your case the failover node, SQL will not start because it cannot listen on that port.

You can test this by setting your IPAll to 1433 and attempting to start it on the failover node. If it starts, you have a conflict.  

But there is a way to tell even without doing that.  In the LOG\ERRORLOG file you will see the attempt to start up on the failover node, and it will indicate what the issue was and why it would not start.  Event logs will help as well.

Before you go and mess with a clustered SQL Server, you should do some investigation on why it would not start on either node.  

Here is a good article on SQL Browser:
http://msdn.microsoft.com/en-us/library/ms181087(v=sql.105).aspx
0
 
motioneyeAuthor Commented:
Hi,
I try on other node with sql browser services started, and happily can fail over the node without any problems.

Then I try to fail it over again to node where I stop sql browser service, id does not comes online at all. what surprise me is in clusteradmin it show online pending, and in configuration manager it show started. but when clusadmin failed to bring sql service online then it sill stop.

so I change again, sql browser now started, I can failed over  over the node without any problems at all.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.