Instance name in SQL active/active Cluster

Question

Hi

We oringinal have a active/passive cluster, network name MYserver1 and instance name MSSQLSERVER.

We have installed a second SQL 2005 instance in our cluster, let' call it ins2. Or SQL network name is set to MYserver2 after applying SP4 and chaning the port number to 1433 in the confiuation manager, we can connect to the server using SSMS using only the server name. We cannot connect using the MYserver2\ins2 using SSMS. However, other applications using ODBC or OLEDB must use Myserver2\ins2 to connect.

if you issue a select @@servername, it will return Myserver\ins2.

Can someone shed some light on this behavoiur?

Regards

Eug

Answer 1

What is the error message that you get when you try and connect? Ensure that your browser service is running.

-Feroz

---

Mark as Answer if it helps. This posting is provided "AS IS" with no warranties and confers no rights.

Answer 2

Make sure the SQL Browser service is running on all nodes.  Also make sure that UPD port 1434 is not blocked by your network admins.

---

Geoff N. Hiten Principal Consultant Microsoft SQL Server MVP

Answer 3

The error is generally related to network. this means the application is not even getting to the SQL server. it is going to the wrong place. here is a message if I try to use the SSMS to connect using instance name.

TITLE: Connect to Server
------------------------------

Cannot connect to myserver2\ins2

------------------------------
ADDITIONAL INFORMATION:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=-1&LinkId=20476

I can confirm that applications using ado.net use the server name only to connect, while ODBC and OLEDB must use instance name, and on Windows 7 machines we must specify port number. I can also confirm that SQL Browser is running.

Answer 4

Hi EugeneChen,

In a word, the reason that we get this error message is the client stack could not receive SSRP response UDP packet from SQL Browser. It's easy to isolate the issue.
Here are the steps:
1) Make sure your server name is correct, e.g., no typo on the name.
2) Make sure your instance name is correct and there is actually such an instance on your target machine. [Update: Some application converts \\ to \. If you are not sure about your application, please try both Server\Instance and Server\\Instance in your connection string]
3) Make sure the server machine is reachable, e.g, DNS can be resolve correctly, you are able to ping the server (not always true).
4) Make sure SQL Browser service is running on the server.
5) If firewall is enabled on the server, you need to put sqlbrowser.exe and/or UDP port 1434 into exception.

For more information, please refer to http://blogs.msdn.com/b/sql_protocols/archive/2007/05/13/sql-network-interfaces-error-26-error-locating-server-instance-specified.aspx.

Thanks,
Maggie

 

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.

Answer 5

HI Maggie

I followed the link and here is what I get

portqry.exe -n myserver2 -p UDP -e 1434


Querying target system called:
 myserver2

Attempting to resolve name to IP address...
Name resolved to 172.16.x.x

querying...

UDP port 1434 (ms-sql-m service): LISTENING or FILTERED

Sending SQL Server query to UDP port 1434...

UDP port 1434 (ms-sql-m service): FILTERED

Let me clarify the situration. We have one application running ado.net, it connect the server without instance name. SSMS connects without instance name. Another application using OLEDB, it must use instance name. Biztalk must use instance name. OLEDB on Windows 7 must user instance name plus port number, which is 1433.

I can confirm that we are using the correct spelling.

Answer 6

Hi Maggie

I think I have found the issue however not the solution. on a clustered machine, the Browser is responding to UDP port 1434 via the node name, not the SQL network name. her is wha I get if I do the following.

C:\Windows\system32>portqry.exe -n MyserverNode1 -p UDP -e 1434

Querying target system called:

 MyserverNode1

Attempting to resolve name to IP address...


Name resolved to 172.16.x.x(this is the IP address of the node, not the SQL clustered IP)

querying...

UDP port 1434 (ms-sql-m service): LISTENING or FILTERED

Sending SQL Server query to UDP port 1434...

Server's response:

ServerName Myserver1
InstanceName MSSQLSERVER
IsClustered Yes
Version 9.00.5000.00
tcp 1433
np \\Myserver1\pipe\sql\query

ServerName Myserver2
InstanceName Ins2
IsClustered Yes
Version 9.00.5000.00
tcp 1433
np \\Myserver2\pipe\MSSQL$ins2\sql\query

 l

==== End of SQL Server query response ====

UDP port 1434 is LISTENING

When I open the Configuration manager and go to SQL Server 2005 Services-->SQL Server Browser-->Properties-->Services TAB--Host Name, the value is the node name.

Answer 7

HI Maggie

Finally we have find the answer. the OLE DB connection cannot connect to the SQL Server named instance unless the name is specified. this behavior is by design. The native client however can connect the active/active cluster using virtual name only.We came to this conclusion after seeking help form Microsoft Premium Service located in Shanghai.

Eugene