• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 654
  • Last Modified:

SQL Browser server not returning dynamic ports on SQL cluster

Hi I have a newly installed 2 node cluster running on windows 2003 x64 ENT. I have two sql instances installed on the server one SQL 2008 and a second SQL 2005 instance.

When trying to create an ODBC connection from a remote server to either database instance it fails with a server could not be found error. If I specify the correct TCP port in the ODBC settings the connection works for both instances.

Any ideas as to how I can get the dynamic discovery of TCP ports working correctly?
0
matthewsj11
Asked:
matthewsj11
  • 3
  • 2
1 Solution
 
Chris MConsulting - Technology ServicesCommented:
Hi Matthew,
When it comes to clustering, more than a single SQl clusters will always use different ports (one usually takes the default and the other takes up an assigned port).
Most SQL clients will default to 1433, meaning that named and default instances will easily be looked up easily if they are on port 1433 but this is not the situation for you.
The option you have is to configure aliases in your clients' configuration (with the right ports) to lookup these SQL instances on your Windows cluster.
It's a painful exercise but there's no other option unfortunately, otherwise you will have to specify the port in your connection options.
Regards,
Chris.
0
 
matthewsj11Author Commented:
Thanks for your answer. I thought that the SQL browser service was supposed to direct incoming connections to the correct port for named instances. The instances are using ports 1756 and 1999

Does this not work when they DBs are part of a cluster?

James
0
 
Chris MConsulting - Technology ServicesCommented:
Hi James,
Whether the instances are clustered or not, if running SQL browser, then the work of SQL browser is to listen to incoming requests and provide information about the SQL server instances running on that machine or cluster (e.g instance name, port & pipes).
SQL server browser service was developed to replace SQL 2000's similar but limited service called SQL server resolution protocol (SSRP); SSRP listens on UDP port 1434 to respond to client requests with names of installed instances, ports etc.
This is how SQL browser works: it runs on UDP port 1434 (like SSRP was), reads the registry, identifies SQL instances installed on your server or cluster and notes pipes and ports they use so that when SQL clients make service requests,  the client network library (via UDP port 1434) sends a message to the server to which SQL browser responds with details like named pipes, port number of the desired instance.
The process I just explained, for some reason may not work as the theory states, so it will require an advanced check and sometimes there's little to change to get it fixed so for starters ensure that SQL browser is running, then test connectivity to your instance but if this does not work then use the workaround I stated earlier in the previous post because it always works and is independent of SQL browser.
To answer your query: dynamic discovery of ports is done by running SQL browser service (this is supposed to be automatic for SQL 2000).
If you remember in SQL 2000 clustered instances, it was sometimes hard to connect to the named instance that uses a port which is not 1433, and in this case configuring aliases in the client configuration was necessary yet SSRP was running by default.
Lastly, you may wish to enable both protocols (TCP/IP, Named pipes), because out of testing, some clients connect more easily using named pipes than just TCP protocol. Find out what best suits your SQL clients (Note that most SQL clients support all the mentioned protocols).
Regards,
Chris
0
 
matthewsj11Author Commented:
Thanks for your reply Chris.

We have a fat application client to install on users work stations and I was hoping to simplify the ODBC setups. Oh well I guess it is time to write some good instructions for our users.

Thanks for your help.

0
 
Chris MConsulting - Technology ServicesCommented:
hehehe alright James.
You can write an easy/simple and straight forward procedure for your users in case your client support team cannot help all of them in on time.
All the best, God bless.
Chris.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now