Solved

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

Posted on 2013-01-14
4
792 Views
Last Modified: 2013-01-30
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 ?
0
Comment
Question by:motioneye
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
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:

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
 
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:
http://msdn.microsoft.com/en-us/library/ms181087(v=sql.105).aspx
0
 

Author Comment

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

Accepted Solution

by:
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.
0

Featured Post

Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

Question has a verified solution.

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

Sometimes drives fill up and we don't know why.  If you don't understand the best way to use the tools available, you may end up being stumped as to why your drive says it's not full when you have no space left!  Here's how you can find out...
This article provides a convenient collection of links to Microsoft provided Security Patches for operating systems that have reached their End of Life support cycle. Included operating systems covered by this article are Windows XP,  Windows Server…
This tutorial will walk an individual through locating and launching the BEUtility application to properly change the service account username and\or password in situation where it may be necessary or where the password has been inadvertently change…
This tutorial will show how to configure a new Backup Exec 2012 server and move an existing database to that server with the use of the BEUtility. Install Backup Exec 2012 on the new server and apply all of the latest hotfixes and service packs. The…

690 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