x

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
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

more ▼

asked Oct 04, 2012 at 05:08 PM in Default

SQLServerMonkey gravatar image

SQLServerMonkey
190 2 2 3

Any errors in either the SQL Server log file for failed logins or the windows event viewer?
Oct 04, 2012 at 05:44 PM JohnM
Also, any firewall rules that could be blocking things? Just throwing out ideas.
Oct 04, 2012 at 05:46 PM JohnM

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.
Oct 08, 2012 at 09:33 AM SQLServerMonkey

Thomas

Thanks for that suggestion, however it does not seem to have worked :-( this is all rather confusing.

John
Oct 08, 2012 at 09:37 AM SQLServerMonkey
(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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

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.

more ▼

answered Oct 08, 2012 at 01:03 PM

SQLServerMonkey gravatar image

SQLServerMonkey
190 2 2 3

(comments are locked)
10|1200 characters needed characters left

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 and sp_addserver are your friends. See How to: Rename a Computer that hosts a stand-alone instance of SQL Server 2005.

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.

more ▼

answered Oct 04, 2012 at 05:57 PM

ThomasRushton gravatar image

ThomasRushton ♦
33.8k 18 20 44

(comments are locked)
10|1200 characters needed characters left
Check for entries in an LMHOSTS or HOSTS file.
more ▼

answered Oct 04, 2012 at 05:13 PM

Blackhawk-17 gravatar image

Blackhawk-17
11.9k 28 31 36

There are no entries in either LMHOSTS or HOSTS files on either of the servers.
Oct 04, 2012 at 05:26 PM SQLServerMonkey
Double check your DNS configuration for the NICs on both servers.
Oct 04, 2012 at 05:43 PM Blackhawk-17

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 ..
Oct 08, 2012 at 09:38 AM Fatherjack ♦♦
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.
Oct 08, 2012 at 11:40 AM SQLServerMonkey

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.
Oct 08, 2012 at 11:46 AM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x189
x92
x55
x46
x2

asked: Oct 04, 2012 at 05:08 PM

Seen: 2077 times

Last Updated: May 17, 2013 at 02:04 AM