Determining which node a clustered SQL Server is running on

Category : Tips User Rating : 4.5 Stars      Views : 4.5 Stars
One Click Feedback
Please help us to improve the site by rating the quality of this article by clicking a button below.




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 :
SELECT SERVERPROPERTY('ComputerNamePhysicalNetBIOS')
To determine whether the SQL Server is configured in a failover cluster run the SQL command below :
SELECT SERVERPROPERTY('IsClustered')
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 :
Copy Only Backup GUI
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 :
SELECT SERVERPROPERTY('MachineName')
SELECT SERVERPROPERTY('InstanceName')
SELECT SERVERPROPERTY('ServerName')
In a clustered situation the MachineName argument returns the name of the virtual server (so it won’t change if a failover occurs).
Link back to this article : http://www.sqlmatters.com/Articles/Determining which node a clustered SQL Server is running on.aspx

Keywords

TSQL,clustering,node,active,passive


Comments
Post by Michael on Wed 08 May 2013 15:03. Report Inappropriate Post

Good Post. This works when using Microsoft Clustering, be weary if using other forms of clustering like Veritas.
Post by Hamminator on Fri 05 Jul 2013 22:27. Report Inappropriate Post

Nice! Thanks for the good info!
Post by Anil on Tue 29 Jul 2014 22:54. Report Inappropriate Post

Excellent Post.. I got info here I am looking for

Thanks a ton.
Post by ss on Tue 16 Sep 2014 17:11. Report Inappropriate Post

good info thanks
Post by Neel on Wed 11 Feb 2015 17:11. Report Inappropriate Post

Hi,

Can any one help me with the query to pull Window Cluster Name from Management Studio...

Thanks...
Post by David on Tue 07 Apr 2015 13:21. Report Inappropriate Post

Hi

I did this query to show wich node is active and list all servers in a cluster configuration:

With ClusterActiveNode as

(
SELECT SERVERPROPERTY('ComputerNamePhysicalNetBIOS') as NodeName, Cast('Active' as varchar(10)) as Active
),
ClusterNodes as

(

SELECT NodeName FROM sys.dm_os_cluster_nodes
)

Select b.nodename, isnull(active,'Passive') as NodeStatus from ClusterNodes as b left join ClusterActiveNode as a
on a.NodeName = b.nodename
Post by Kevin Kelly on Fri 08 Jan 2016 14:32. Report Inappropriate Post

What a great post! Full of very useful information for internally inspecting my SQL instance(s)

Many thanks!
Post by Kevin Kelly on Fri 08 Jan 2016 14:34. Report Inappropriate Post

Thanks for the bonus code post David. Inspecting all server(s) status is what I was really looking for when I came across this thread.

:)
Post by Anil kumar on Tue 29 Mar 2016 16:23. Report Inappropriate Post

How to know active cluster node and passive cluster node
Post by srini on Mon 11 Apr 2016 14:33. Report Inappropriate Post

Hi All, I need to details of activie cluster node history of my databases like when to when my databases are running on which node? hope someone can help me here and Thanks in Advance.
Post by Vrishabh Patil on Fri 15 Apr 2016 05:32. Report Inappropriate Post

How to check which node is active without using SQL Server Management Studio, means from cluster manager.
Post by ust on Tue 29 Nov 2016 14:44. Report Inappropriate Post

ThanQ David,
Post by jin thakur on Wed 21 Dec 2016 21:37. Report Inappropriate Post
Website : http://askyourquestions.info/
Please try Select SERVERPROPERTY('ComputerNamePhysicalNetBIOS') is what you need
ComputerNamePhysicalNetBIOS does give the active node - the one running the current query.
so you can check this line and find out what node is active. Please run it with every query and if you have active and active ,passive node cluster configuration so the query can go to any node and tell you nodes
Post by kiran M on Thu 18 May 2017 05:17. Report Inappropriate Post

The script doesnt work for active active cluster

Post a comment   No login required !

 
Name :   Email :   Website :  
Will be displayed alongside your comment
Not displayed Optional, but displayed if entered