On which server is the SQL instance running

How many times have you asked yourself: ‘On which server is this SQL Server instance running?’ (In a clustered environment, that is.)

One way to find out is to log in to the cluster, open the Cluster Manager console and check. Right? The problem with this approach is, that by the time you actually log in and check, the instance might fail over to another node. 🙂

Here is a good single line script which will give you this information:

SELECT  SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS ServerName

And it get only better: the best way to do it is to use the Central Management Server, which is part of the SQL Server 2008 Management Studio. This way you can group your servers, let’s say, to a cluster group, test server group, DW group and so on. The advantage is that you can run a query to the entire group. For example, you can run the above query to the entire Cluster group, and this way you will get a resultset of all instances and the corresponding nodes.

Feodor Georgiev