SQL Server failover cluster - determine active nod

问题:

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

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

(I've read a bit about making the program cluster aware, but that seems heavily overkill for this simple scenario.)

回答1:

From SQL Server:

Select ServerProperty('ComputerNamePhysicalNetBIOS')

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

回答2:

You can check like that:

1. Check Availability Group Status:

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. prevent executing job on secondary:

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

or

3. check write availability on 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 newer:

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