0

We have AGs set up (1 primary and 2 secondaries (Includes DR)).

We have an archival job set up in each replica with same schedule and enabled in all three replicas.

This job runs in a frequency of about 15 minutes each day.

My question is:

Since the job in each replica is pointed to primary server while running :

  1. Can I let the job run only in Primary Replica and disable the Job in secondary replicas ? Will it affect otherwise ?

  2. In AlwaysOn AGs will the jobs also be failed over ?

  3. If jobs not failover

My thinking is once the primary is failed over to other node (which is would be primary or current secondary synchronized) and since the secondary replica is now primary and the job will be in disabled state (If at all we can disable the jobs in secondary ?), I just need to enable it on the current replica.

Kindly correct my thinking ?

Regards

1

just need to enable it on the current replica.

You will need to modify you sql agent jobs to include below step

CREATE FUNCTION dbo.fn_hadr_group_is_primary (@AGName sysname)
RETURNS bit 
AS
BEGIN 
DECLARE @PrimaryReplica sysname; 
SELECT @PrimaryReplica = hags.primary_replica 
FROM 
sys.dm_hadr_availability_group_states hags
INNER JOIN sys.availability_groups ag ON ag.group_id = hags.group_id
WHERE
ag.name = @AGName;

IF UPPER(@PrimaryReplica) =  UPPER(@@SERVERNAME)
RETURN 1; -- primary

RETURN 0; -- not primary
  END; 

GO

So you have to create the above function in all your replicas and add as first step in sql agent jobs you wish to execute

If the replica is not primary,you can modify the job to Quit

This has been covered in depth here :
AlwaysOn Availability Groups and SQL Server Jobs, Part 7: Detecting Primary Replica Ownership

  • Hi dba.stackexchange.com/users/31995/thegameiswar,thank you for the response.What if we mention in the job step GUI,command section to run through PowerShell and mention -dbs "AOAGListener",do you think that should be fine ? What I mean is ,though the same job is running in all three replicas,they point each time,same schedule to AOAGListener which is Primary server! So in this case can I disable the jobs in secondary replicas as they point to primary each time and does nothing at all, only the primary replica job does matters!.and enable the job in secondary replica when turns primary – Sil Ence Apr 5 '17 at 6:42
  • 1
    what is the need for pointing a job in secondary replica to primary – TheGameiswar Apr 5 '17 at 7:07
  • Yes,understood my mistake.Thanks a lot for the fix.And sorry I cannot up your answer as I donot have the privilege yet. – Sil Ence Apr 5 '17 at 7:10
  • 2
    There is already a DMF provided in 2014 for checking primary - once again this series goes over and beyond to make AG Jobs way more complicated than it needs to be. msdn.microsoft.com/en-us/library/dn249345.aspx – Sean says Remove Sara Chipps Apr 5 '17 at 16:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.