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

Posted on 2013-01-14
Last Modified: 2013-01-30
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 ?
Question by:motioneye
  • 2
LVL 28

Assisted Solution

by:Ryan McCauley
Ryan McCauley earned 334 total points
ID: 38778807
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:

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:

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.
LVL 24

Assisted Solution

by:DBAduck - Ben Miller
DBAduck - Ben Miller earned 166 total points
ID: 38799738
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:

Author Comment

ID: 38804220
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.
LVL 28

Accepted Solution

Ryan McCauley earned 334 total points
ID: 38805568
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.

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
You might have come across a situation when you have Exchange 2013 server in two different sites (Production and DR). After adding the Database copy in ECP console it displays Database copy status unknown for the DR exchange server. Issue is strange…
This tutorial will give a short introduction and overview of Backup Exec 2012 and how to navigate and perform basic functions. Click on the Backup Exec button in the upper left corner. From here, are global settings for the application such as conne…
This tutorial will walk an individual through the steps necessary to join and promote the first Windows Server 2012 domain controller into an Active Directory environment running on Windows Server 2008. Determine the location of the FSMO roles by lo…

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question