5

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.)

Has QUIT--Anony-Mousse
  • 76,138
  • 12
  • 138
  • 194
Kalle
  • 2,282
  • 1
  • 24
  • 30
  • If you're starting to care about the clustering, I think you'd be far better off investing the energy to do it right - what if there's a manual failover initiated between your check and whatever you're planning to do after that? You'd think you're on the active node when that is no longer true. – Damien_The_Unbeliever Dec 20 '10 at 18:14
  • 1
    @Damien How would you "do it right" in this case? – bzlm Dec 20 '10 at 20:51
  • really not run simultaneously on the inactive and the active node. ? – PreguntonCojoneroCabrón Sep 20 '18 at 20:13

2 Answers2

9

From SQL Server:

Select ServerProperty('ComputerNamePhysicalNetBIOS')

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

Joe Stefanelli
  • 132,803
  • 19
  • 237
  • 235
  • Can you explain how `Select ServerProperty('ComputerNamePhysicalNetBIOS')` tells you whether you are on the active node or not? – Neil Weicher Oct 13 '13 at 16:14
  • @Neilw: From the [documentation](http://technet.microsoft.com/en-us/library/ms174396.aspx): "NetBIOS name of the local computer on which the instance of SQL Server is currently running. For a clustered instance of SQL Server on a failover cluster, this value changes as the instance of SQL Server fails over to other nodes in the failover cluster. On a stand-alone instance of SQL Server, this value remains constant and returns the same value as the MachineName property." – Joe Stefanelli Oct 15 '13 at 13:38
0

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
Krzysztof Gapski
  • 528
  • 6
  • 10