Sample Header Ad - 728x90

in maintenance job, how to manually signal success or failure to notify task

0 votes
1 answer
47 views
i have a primary/secondary instance setup. I would like to only run a task when its the primary instance. To test:
tsql task- checks for primary 
if primary   -> notify primary  task  -> email with subject primary
if secondary -> notify secondary task -> email with subject secondary
DECLARE @role_desc varchar(20),
                  @msg varchar(50) = 'ERROR -- no primary instance';
    IF EXISTS( SELECT hars.role_desc FROM sys.DATABASES d INNER JOIN sys.dm_hadr_availability_replica_states hars ON d.replica_id = hars.replica_id WHERE hars.role_desc = 'PRIMARY' )
    begin
      SELECT @role_desc = 'PRIMARY' 
    end
    ELSE
    begin
      SELECT @role_desc = 'SECONDARY' 
      RAISERROR(@msg, 5, 1);
    end
so far, i am only getting primary, although i am on the secondary instance
Asked by user30197 (11 rep)
Jan 10, 2025, 05:43 PM
Last activity: Jan 21, 2025, 12:41 PM