AlwaysOn and SQL Agent Jobs

Tom AlwaysOn, SQL2012, sqlagent 2 Comments

Now we have our AlwaysOn solution and the same SQL Agent jobs running on each availability replica and they reference the availability group listener. So we’re all set up and running with our Availability solution but our SQL Agent jobs are unaware of all of this. The solution is to script copies of each SQL Agent job on each Availability replica.

Then the job is only to run if the current server is the primary. We can use an extra starting T-SQL script in our job to determine if we are the primary replica.

DECLARE @IsPrimary int
SET @IsPrimary = 0
SELECT @IsPrimary  = 1
FROM sys.availability_groups sag
INNER JOIN sys.dm_hadr_availability_group_states hags ON sag.group_id = hags.group_id
WHERE hags.primary_replica = @@SERVERNAME AND sag.name  = [Your Availability Name]
IF @IsPrimary  = 1
BEGIN
       RETURN
END
ELSE
BEGIN
       RAISERROR (‘Database is running as secondary’, 16, 1)
END

If the task fails we can go to a final step that succeeds so as to report the actual job as successful completion and not alert us accordingly as a failed job. If the task succeeds it should use the next step to run these rest of the job as we running on the primary replica.

AlwaysOn and SQL Agent Jobs

2 thoughts on “AlwaysOn and SQL Agent Jobs”

  • prasad
    August 13, 2019 at 4:09 am
    Permalink

    How to run the Above script ….did you mean i need to put in step1 of each SQL agent job ?

    Reply
    • sqltomato
      August 16, 2019 at 10:10 am
      Permalink

      You can put the TSQL in the starting job step and then based on success or error direct to other job steps. Hope that helps – Thanks for reading!

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Pin It on Pinterest