Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2013-01-14
4
Medium Priority
?
850 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 1336 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 25

Assisted Solution

by:DBAduck - Ben Miller
DBAduck - Ben Miller earned 664 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 1336 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

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Resolving an irritating Remote Desktop connection that stops your saved credentials from being used.
This tutorial will walk an individual through the steps necessary to install and configure the Windows Server Backup Utility. Directly connect an external storage device such as a USB drive, or CD\DVD burner: If the device is a USB drive, ensure i…
This tutorial will show how to configure a single USB drive with a separate folder for each day of the week. This will allow each of the backups to be kept separate preventing the previous day’s backup from being overwritten. The USB drive must be s…

618 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