Determining which node a clustered SQL Server is running on

Category : Tips User Rating : 4.5 Stars      Views : 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).

Related Articles

The following articles may also be of interest :

Link back to this article : https://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 by Robbert Michel on Mon 06 Aug 2018 11:31. Report Inappropriate Post
Website : http://www.centennium.nl
For activecluster try the following:

select nm.ag_name
from sys.dm_hadr_instance_node_map inm
join sys.dm_hadr_name_id_map nm on nm.ag_resource_id = inm.ag_resource_id
where instance_name = @@SERVERNAME
Post by Pushkar on Mon 22 Jul 2019 13:17. Report Inappropriate Post

thank you
Post by Ashis on Mon 22 Jul 2019 13:30. Report Inappropriate Post

Thanks very much
Post by Pushkar Joshi on Wed 24 Jul 2019 11:08. Report Inappropriate Post

ohh.....wowwww
Post by MARSHALL TAAH on Tue 30 Jul 2019 15:23. Report Inappropriate Post

Excellent solution and straight to the point
Post by Neha on Tue 28 Jan 2020 19:39. Report Inappropriate Post

How to get the previous node from the error log?
Post by Greg on Mon 28 Jun 2021 00:29. Report Inappropriate Post

Hey quick question for the experts out there, when SQL active passive cluster is setup, when it fails over to the passive cluster, how do all apps using that DB server know the new hostname? or does the hostname remain the same? Really appreciate the help.
Post by Liturgist on Fri 27 Oct 2023 16:47. Report Inappropriate Post

This is a helpful explanation of finding the CURRENT node.

How can the PRIMARY node be identified?
Post by Krithi on Wed 15 Nov 2023 13:42. Report Inappropriate Post

Sqlcluster quorum is pointing to passive node how to fix it

Post a comment   No login required !

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