Identify the active node in SQL Server Cluster

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]

Which Node is running

 

As per the above screenshot, there are 4 nodes and SQLNode1 is the active node in the cluster.

 

 

2 comments

  1. I was curious if you ever considered changing the layout of your blog?
    Its very well written; I love what youve got to say.
    But maybe you could a little more in the way of
    content so people could connect with it better. Youve got an awful
    lot of text for only having one or 2 pictures. Maybe you
    could space it out better?

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s