SQL server

TSQL to find physical name of node in a cluster.

I just did a SQL 2005 cluster installation on a windows 2008 machine. I must tell you, Microsoft sure has made things easier for the DBA. When I went to test the fail over features, I launched SSMS from my laptop and connected to the virtual server name. I did some queries and it worked fine. Then, I logged into the virtual server, and did a few clicks to reach the ‘simulate fail over’ link. The link did its work, and the cluster held its ground. I was still able to do queries from my local machine, and the cluster moved the resources to the next node.

Now, as usual, I got thinking: How can I find out from my local machine, the physical node, that SQL is running off. A little bit of searching and testing led me to find the perfect TSQL query. The magic query is:

select SERVERPROPERTY(‘ComputerNamePhysicalNetBIOS’)

I did a few tests from different remote machines (in different domains), and it worked fine. I did find one issue though: The remote server that I had the query open in did not display the change after the fail over. It still showed the same node name. I had to close SSMS and reopen it to see the proper name. You may also be able to use the xp_cmdshell route, but using a single TSQL query without changing the configuration options is much faster/easier.


Thinking about someting? Leave a Reply...

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s