SQL Agent Jobs With AlwaysOn Availability Group

Question

We have Primary (01) and Secondary (02) AlwaysOn Availability Group. We have SQL Agent jobs and the first step in the job is to check whether it is Primary or Secondary. When this is run on Secondary the job will not run.

IF NOT EXISTS ( SELECT  1
            FROM    sys.dm_hadr_availability_group_states hags
            JOIN    sys.availability_groups ags ON hags.group_id = ags.group_id
            WHERE   hags.primary_replica = @@SERVERNAME
            AND     ags.name = 'XYZ'
          )
BEGIN
    RAISERROR('This is not Primary Node', 16, -1);
END;

The above solution works fine, but when the failover does happen then on Primary (01) the Job is quit as success and we don't know when it failed over.

Want to know whether there are any other better alternatives to handle the AG SQL Agent Jobs.

Thanks!

Answer 1

If you are on SQL 2022, or are willing to upgrade, you can make the AG a contained AG, which means that the AG has its own msdb, so the job travels with the AG when there is a failover.

I have not tried this myself yet, but it certainly sounds exciting.

Answer 2

Hi @SQL

I found this link for you. It describes several ways to manage agent jobs on availability group servers, and perhaps you can use it as a reference: https://sqlundercover.com/2020/01/16/managing-agent-jobs-on-availability-group-servers/.

Best regards,

Aniya