Retrieve hostname in clustered environment from active node

Question

Retrieve hostname in clustered environment from active node

bryan van ritter

SSCrazy

Points: 2270
More actions
February 22, 2010 at 3:44 am

#219339

Hi,
I have a clusterd sql server 2005 environment with multiple named instances and i am trying to do the following. I am trying to get the hostname on which the instance is running. To administer the environment i am working from one node out of 4. So when i log on to an instance on node a which is actually running on node b and execute:
select host_name() i get the actual host_name from node a, but i want to retrieve the actual host_name where my instance is runnig. Is there a way to get this information?
thanks in advance

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Schadenfreude-Mei SSChampion Points: 14083 More actions · Answer 1

I was going to suggest 'xp_cmdshell 'hostname' but i have tried it and it just returns the virtual server name (though works when done straight in dos).

So I created a cmd file to do the 'hostname' from dos and then pipe the result into SQL, that gave me the same response.

Sorry dude, maybe this is of 'some' help? :crying:

Adam Zacks-------------------------------------------Be Nice, Or Leave

Lowell SSC Guru Points: 323518 More actions · Answer 2

i thought select serverProperty('ComputerNamePhysicalNetBios') was supposed to give you the actual machine you are connected to in a clustered environment, am i wrong?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Schadenfreude-Mei SSChampion Points: 14083 More actions · Answer 3

That works great!

So theres the answer 🙂

Adam Zacks-------------------------------------------Be Nice, Or Leave

bryan van ritter SSCrazy Points: 2270 More actions · Answer 4

Hi guys,

ComputerNamePhysicalNetBios also tried this, but the problem is that in an environment where you will have to deal with lots and lots of named instances i am managing those instances from one gui on one server. So when you connect via the management studio to one instance which is not running on the node from which you are connecting, you will get the hostname from that server and not the actual hostname on which the instance is running.

bryan

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 5

BOL states:

ComputerNamePhysicalNetBIOS
NetBIOS name of the local computer on which the instance of SQL Server is currently running.
For a clustered instance of SQL Server on a failover cluster, this value changes as the instance of SQL Server fails over to other nodes in the failover cluster.
On a stand-alone instance of SQL Server, this value remains constant and returns the same value as the MachineName property.
Note:
If the instance of SQL Server is in a failover cluster and you want to obtain the name of the failover clustered instance, use the MachineName property.
NULL = Input is not valid, or an error.
Base data type: nvarchar(128)

Did you try ..

select Serverproperty( 'MachineName' ) as MachineName

, Serverproperty( 'ComputerNamePhysicalNetBIOS' ) as ComputerNamePhysicalNetBIOS

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

bryan van ritter SSCrazy Points: 2270 More actions · Answer 6

Hi,

Thanks for the information. I've now seen what my problem is. We work with a veritas clustered environment on windows. So we configured each instance as an virtual machine with his own ip-adres. After installing we configured sp_helpserver to say something like this TEST_ACC\TEST_ACC which is obviously not the physical node name but the name of the named instance as it is acting like a node. So when obtaining the machinename it gives me the virtual name instead of the actual machine lets say "NODE_A" where it is running on. So i am still trying to figure out how to get this information. Maybe i will have to look on machine level where the process of the named instance is running???? If there are still any ideas they would be very welcome.

thanks again in advance

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 7

Apparently you have been bitten by your own messing around with logical names !

If you start fooling around with systems info or settings, don't be surprised you may not be able to use systems stuff to get to the actual data.

sp_helpserver doesn't show node names.

My advise .... remove the fog.

Remove the hostname(s) you have been altering and use the predesigned commands to get to your wanted info.

This is a typical result on my clustered instances:

Select Serverproperty( 'ComputerNamePhysicalNetBIOS' ) as ComputerNamePhysicalNetBIOS

, Serverproperty( 'InstanceName' ) as InstanceName

, Serverproperty( 'IsClustered' ) as IsClustered

, Serverproperty( 'MachineName' ) as MachineName

, Serverproperty( 'ServerName' ) as ServerName

ComputerNamePhysicalNetBIOS*InstanceNameIsClusteredMachineNameServerName

UABE0DB11*GNKE0011UABE0DB71UABE0DB71\GNKE001

needed to add the * to get the results a little bit formatted