SQL Agent Jobs With AlwaysOn Availability Group

SQL 321 Reputation points
2023-04-28T19:21:43.78+00:00

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!

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,102 questions
0 comments No comments
{count} votes

Accepted answer
  1. Erland Sommarskog 103.8K Reputation points MVP
    2023-04-28T20:09:58.14+00:00

    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.


1 additional answer

Sort by: Most helpful
  1. AniyaTang-MSFT 12,341 Reputation points Microsoft Vendor
    2023-05-01T07:16:06.53+00:00

    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

    0 comments No comments