question

SQLServerMonkey avatar image
SQLServerMonkey asked

Clustered SQL Server Instance Name Resoloution Failing

Hello **Environment** Two Node Windows Failover cluster Windows Server 2008R2 SP1 SQL Server 2005 SP4 SQL Server 2008R2 SP1 ![alt text][1] **Issue** Red Gate SQL Backup fails when executed from a SQL Agent job or via query in SSMS, this only happens when the SQL Server instance is hosted on Server A but works fine on Server B. The Error that is being returned is as follows;
SQL error -1: SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF]. Server : Server A\SQL01 Windows authentication 04/10/2012 17:13:12: Failed to connect to SQL Server instance: Server A\SQL01
Another observation made when testing connectivity to the SQL 2005 Instances using the Surface Area configuration tool, was that when directed to a specific cluster name such as ClSvr01 it would return the instance name of SQL02 which is not present within ClSvr01. This would be the case for all instances that I tried to connect to while on Server A, performing the same actions from Server B worked as would be expected. By disabling Named Pipes for the specified instance and this seems to have resolved the issue with the Surface Area Configuration tool but not the backup software. Connection to all instances via SSMS using TCP/IP or Named Pipes is fine, and running queries such as SERVERPROPERTY and @@SERVERNAME return the results I would expect to see with regards to the server knowing who it is. There are no entries in the host files or anything like that. I am unable to replicate this issue within any of my testing VM systems so am not 100% sure where else to look. **Question** Can anyone think of why I might be getting the incorrect resolution of Server & instance names on one server and not the other, I am almost certain that it is a configuration issue outside of SQL Server but I cannot think where to look. Any assistance would be very much apprecaited in where/what to look for, or if anyone has had a similar issue. Many thanks John [1]: http://www.gliffy.com/pubdoc/3932742/L.png
sql serverbackupclusterclusteringredgate
4 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

JohnM avatar image JohnM commented ·
Also, any firewall rules that could be blocking things? Just throwing out ideas.
1 Like 1 ·
JohnM avatar image JohnM commented ·
Any errors in either the SQL Server log file for failed logins or the windows event viewer?
0 Likes 0 ·
SQLServerMonkey avatar image SQLServerMonkey commented ·
Error logs are clear, nothing is even hitting the SQL Server to fail from the looks of it. All logins seem to work fine and all services using the instance are working as normal. All rather confusing.
0 Likes 0 ·
SQLServerMonkey avatar image SQLServerMonkey commented ·
Thomas Thanks for that suggestion, however it does not seem to have worked :-( this is all rather confusing. John
0 Likes 0 ·

3 Answers

·
SQLServerMonkey avatar image
SQLServerMonkey answered
I have now found a workaround that gets the node that was causing me a problem up and running. If you go into the properties of the SQL Backup agent clustered resource and check the option to "Use Network Name for computer name" it should get you up and running. ![alt text][1] I am still looking into why this worked on one node and not the other with it unchecked, and should I find anything I will post a comment to this. I stumbled over this while doing a totally manual install of the cluster components. [1]: /storage/temp/431-clusterresourceconfig-sanitised.png

clusterresourceconfig-sanitised.png (14.5 KiB)
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Blackhawk-17 avatar image
Blackhawk-17 answered
Check for entries in an LMHOSTS or HOSTS file.
6 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Blackhawk-17 avatar image Blackhawk-17 commented ·
Double check your DNS configuration for the NICs on both servers.
1 Like 1 ·
SQLServerMonkey avatar image SQLServerMonkey commented ·
There are no entries in either LMHOSTS or HOSTS files on either of the servers.
0 Likes 0 ·
Fatherjack avatar image Fatherjack ♦♦ commented ·
Yeah, my chief suspect in this would be DNS. What do you see via Ping/TelNet while SQL Backup is having issues? Have RedGate Support got in on this yet? I'm sure they'd have plenty of ideas ..
0 Likes 0 ·
SQLServerMonkey avatar image SQLServerMonkey commented ·
DNS Config is the same on both nodes, LMHosts, Hosts etc all fine. SQL Server does not even think that anything is trying to connect. nothing showing with Ping etc. Red Gate support have a call raised but not seen this before from what I understand.
0 Likes 0 ·
Fatherjack avatar image Fatherjack ♦♦ commented ·
I meant actually in the DNS config, every once in a while I have an issue with the DNS server not giving IP addresses correctly, routing going a little askew etc. When you say "nothing showing with Ping" you mean that you cant successfully ping the server? That is out and out network/clustering issue IMO.
0 Likes 0 ·
Show more comments
ThomasRushton avatar image
ThomasRushton answered
I recall having to go through a server rename process on older versions of SQL Server to change the servername from the individual server within the cluster to the name of the cluster. [`sp_dropserver`][1] and [`sp_addserver`][2] are your friends. See [How to: Rename a Computer that hosts a stand-alone instance of SQL Server 2005][3]. You'll need to carry out the process with SQL running on each of the two servers in the cluster. We had similar problems with Quest's LiteSpeed for SQL Server, and this fixed it. [1]: http://msdn.microsoft.com/en-us/library/ms174310(v=sql.90).aspx [2]: http://msdn.microsoft.com/en-us/library/ms174411(v=sql.90).aspx [3]: http://msdn.microsoft.com/en-us/library/ms143799(v=sql.90).aspx
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.