I used to have 2 servers on an availability group, but there was a problem with one of them servers involved,while patching, and to make a long story short I removed all the databases from the availability group.
If sys.fn_hadr_is_primary_replica ('apcore') =1 BEGIN EXEC [APCore].[app].[usp_upd_applicationStatusTimeExpired] END
But now that the current server is the only server in the Availability group, this script
select sys.fn_hadr_is_primary_replica ('apcore')
retuns null, and therefore is not accurate.
I have been developing a function that should return a
bit 1 in one of the following cases:
1 - we are not part of an availability group
2 - we are part of an availability group and we are the primary server
The question is:
Would this function work for a distributed availability group? Is there any other situation that I haven't thought of that might prevent this function to return the expected value?
and here is the function: the function is to be used inside jobs and check whether we should run the job (either because we are in an availability group and we are the primary, or we are not in an availability group at all).
--========================================= -- scalar-valued function dbo.check_HADR_status -- returns 1 when either primary or we are standalone -- (not part of an availability group) -- USAGE: -- SELECT MASTER.dbo.check_HADR_status() --========================================= USE MASTER GO IF OBJECT_ID (N'dbo.check_HADR_status') IS NOT NULL DROP FUNCTION dbo.check_HADR_status GO CREATE FUNCTION dbo.check_HADR_status() RETURNS BIT WITH EXECUTE AS CALLER AS BEGIN RETURN (SELECT CASE WHEN EXISTS (select * from sys.availability_replicas) THEN CASE WHEN ( SELECT COALESCE(a.role_desc,'RADHE') FROM sys.dm_hadr_availability_replica_states AS a JOIN sys.availability_replicas AS b ON b.replica_id = a.replica_id WHERE b.replica_server_name = @@ServerName) LIKE 'PRIMARY' THEN 1 ELSE 0 END ELSE 1 END) END GO IF (SELECT MASTER.dbo.check_HADR_status()) = 1 BEGIN PRINT 'RUN THE JOB STEP' END ELSE BEGIN PRINT 'DON''T RUN THE JOB AS WE ARE IN A SECONDARY REPLICA' END