Menu
Does anyone know how to determine the active node of a SQL Active-Passive Failover Cluster programmatically from T-SQL?
@@SERVERNAME only returns the virtual server name, which is identical from both nodes.
I don't plan to make any decisions based on the data - I trust the failover to do its thing - but I would like to include the information in an event log so I can tell which node in the cluster was active when the event occurred, or help determine if exceptions come up as a result of a failover.
864
1. In “Failover Cluster Management” console, you can see the current active node under the label “Current Owner” from summary screen. 2. Run the following T-SQL in SQL Server Management studio.
Like the active-active cluster configuration, an active-passive cluster also consists of at least two nodes. However, as the name "active-passive" implies, not all nodes are going to be active. In the case of two nodes, for example, if the first node is already active, the second node must be passive or on standby.
Use 'scstat -p' and look for online and offline. The output is ONLINE/OFFLINE if is online the current node is ACTIVE for the resource otherwise the current node is PASSIVE. If you have OFFLINE to all node the resource is down.
Select ServerProperty('ComputerNamePhysicalNetBIOS') This works in newer versions:
SELECT * FROM fn_virtualservernodes(); full details at https://docs.microsoft.com/en-us/sql/relational-databases/system-functions/sys-fn-virtualservernodes-transact-sql?view=sql-server-2017
SELECT * FROM sys.dm_os_cluster_nodes; full details at https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-os-cluster-nodes-transact-sql?view=sql-server-2017
42
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
