December 14, 2018 at 6:37 am
I have 2 servers which are part of AG. On the primary server, we have some agent jobs (around 20 jobs consist of SSIS packages) which run on a daily basis. I have not created those jobs on the secondary yet. If I create these jobs and schedule them on a secondary server, they will fail because the DBs are read only. Is it possible to do something like this when I create a job?declare @AGRole varchar(100)
select @AGRole = count(*) FROM master.sys.dm_hadr_availability_replica_states
where role = 1
IF (@AGRole > 0)BEGIN
go to step 2 and run the ssis package else don't run if the server is not primary
END
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
December 17, 2018 at 2:45 pm
Okay, question 1). Why create the jobs on the secondary at all ? You are seeking to not have them run...
Question 2.) See question 1.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
December 20, 2018 at 8:31 am
sgmunson - Monday, December 17, 2018 2:45 PMOkay, question 1). Why create the jobs on the secondary at all ? You are seeking to not have them run...
Question 2.) See question 1.
In cases of automatic failover, he's wanting the script to run when the server its on becomes the primary (when it used to be secondary).
I think the OP can do it, but its not 100 percent in my opinion. I would look for the following cases:
- Its probably best to make sure each step has its own job. For instance, if you surround all 20 integration jobs with this conditional statement that skips step 2 when its the secondary, and the server fails over in the middle of it running on the primary, to the secondary (and the secondary then becomes the primary), and the matching job is on the same schedule, then it has already finished by definition on what was the secondary (because all 20 jobs were skipped while it was the secondary and skipping jobs happens much faster than running jobs) and may be only partially run on the primary.
LOL hope I didn't murder that explanation too much but its a consideration I ran into.
- During failovers, this means that the job the server is currently running on will itself fail because for whatever reason the current primary is now transitioning to be the secondary. Make sure you are prepared for jobs that have "partially run."
December 21, 2018 at 9:02 am
I am using the listener name instead of the server name and it is working just fine. Tested the job by manually failing over so no issues so far.
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
December 21, 2018 at 9:34 am
Syed_SQLDBA - Friday, December 21, 2018 9:02 AMI am using the listener name instead of the server name and it is working just fine. Tested the job by manually failing over so no issues so far.
Awesome! One last concern left though is if you are falling over because the primary has legit problems then that might take out the agent too, but that could just be me overthinking it.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply