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