Probably most of the DBAs are already aware of how to identify which is active SQL Server node in the clustered environment, however, this will certainly help the beginners.
The dynamic management views and functions return server state and database state information. A user who has permissions to VIEW SERVER STATE on the server and VIEW DATABASE STATE on the database will be able to query against these views.
SQL Servers nodes information will be available in dm_os_cluster_nodes.
SELECT [NodeName],
[Status],
[status_description],
[is_current_owner]
FROM [master].[sys].[dm_os_cluster_nodes]
As per the above screenshot, there are 4 nodes and SQLNode1 is the active node in the cluster.
