AlwaysOn - SQL jobs failing on secondary

• Feivel

  SSCertifiable

  Points: 7524

  More actions

  August 15, 2017 at 8:35 pm

  #323177

  I am looking out for a solution that would allow the SQL jobs to run on the secondary but exit out of the job without actually failing the job...pretty much similar to what Ola's scripts does for backup. One of the blogs that I came across suggested to have the code for checking the availability group as step 1 and then if it succeeds move to the next step but if it fails go to step 3 and then execute the last step as below. However, it doesn't seem to be a very clean solution at least not how I see Ola doing it for backup. Can someone point me in the right direction where can I find any useful script for this or if you'll might have done something similar in your environment which would be a much cleaner approach than this. Also, I was also thinking if I could have a SQL job checking for AlwaysOn AG if it is primary and if it is then enable all the disabled jobs etc. but I still don't feel confident about the solution.

  
-- Step 1
IF dbo.fn_hadr_group_is_primary('AGGroupName') = 1
BEGIN
USE master;
SELECT * FROM dbo.CommandLog;
END

  -- Step 2
select * from dbo.CommandLog;

  -- Step 3
PRINT 'This is not the primary database replica, exiting without error'