matthewsj11
asked on
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
ASKER
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.
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.
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.
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.
ASKER
Does this not work when they DBs are part of a cluster?
James