SQL Server Failover Cluster - Active Node Definition

Question

Is there a way to programmatically determine which node in a SQL Server failover cluster node is active ? Or at least determine if the current computer is an active node?

I have a Windows program that runs on both physical nodes in a failover cluster, but it should work differently depending on whether it runs on the active node. Part of the reason is that this program should not run simultaneously on an inactive and active node .

(I read a little about reporting a cluster of programs, but this is too heavy for this simple scenario.)

Answer 1

From SQL Server:

Select ServerProperty('ComputerNamePhysicalNetBIOS') 

You can also access it through the Microsoft.SqlServer.Management.Smo namespace, as shown here .

Answer 2

You can check like this:

1. Check the status of the availability group:

 if (select ars.role_desc from sys.dm_hadr_availability_replica_states ars inner join sys.availability_groups ag on ars.group_id = ag.group_id where ag.name = 'AvailabilityGroupName' and ars.is_local = 1) = 'PRIMARY' begin -- this server is the primary replica, do something here end else begin -- this server is not the primary replica, (optional) do something here end 

* Remember to change AvailabilityGroupName

or

2. prohibit the execution of tasks on the secondary:

 IF master.dbo.svf_AgReplicaState('my_group_name')=0  raiserror ('This is not the primary replica.',2,1) 

or

3. Check for a record on the secondary:

 IF (SELECT CONVERT(sysname,DatabasePropertyEx(DB_NAME(),'Updateability'))) != 'READ_ONLY' BEGIN -- this server is the primary replica, do something here END 

or

4. for SQL2014 and later:

 IF master.dbo.fn_hadr_database_is_primary_replica('Admin') = 1    BEGIN        -- this server is the primary replica, do something here    END ELSE    BEGIN        -- this server is not the primary replica, (optional) do something here    END