Solved

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

Posted on 2013-01-14
4
738 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
  • 2
4 Comments
 
LVL 28

Assisted Solution

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Possible fixes for Windows 7 and Windows Server 2008 updating problem. Solutions mentioned are from Microsoft themselves. I started a case with them from our Microsoft Silver Partner option to open a case and get direct support from Microsoft. If s…
This tutorial will walk an individual through locating and launching the BEUtility application and how to execute it on the appropriate database. Log onto the server running the Backup Exec database. In a larger environment, this would generally be …
This tutorial will walk an individual through configuring a drive on a Windows Server 2008 to perform shadow copies in order to quickly recover deleted files and folders. Click on Start and then select Computer to view the available drives on the se…

728 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now