The SQL Server Server Properties window contains a property which shows whether a SQL Server is in a failover cluster.
Unfortunately it doesn’t show the node that the SQL Server is currently running on. However this is readily available
from a SQL command :
To determine whether the SQL Server is configured in a failover cluster run the SQL command below :
This returns a 0 or 1. Alternatively look at the Server Properties window (right click on the server in the Object
Explorer window in SQL Server Management Studio and select Properties) and check the “Is Clustered” property :
You can also see a list of possible cluster nodes by querying a data management view (DMV), note however that this
doesn’t identify which nodes are active :
SELECT * FROM sys.dm_os_cluster_nodes
Whilst we’re looking at server properties, there are three more arguments to the SERVERPROPERTY system function
that may be useful, which return the machine server name, the instance name and the server name (i.e. the machine
and instance name) respectively :
In a clustered situation the MachineName argument returns the name of the virtual server
(so it won’t change if a failover occurs).