SQLTeam.com | Weblogs | Forums

AG and SQL Server Agent Jobs


Sorry I tried searching but didn't see anything that stuck out. We are migrating from a 2008 with failover nodes to a 2014 with Availability groups set up. It is a Primary/secondary set up and have it up and running. My question is what is the best way to handle a job being built on the primary getting created on the secondary. All the googling I have done has code for checking if the job is running on the primary and then logic to run or not. But how does everyone handle creating the new jobs onto the secondary? Do you just manually create them on the secondary once they are set up on the primary? I saw a piece in the SSIS for doing it, but if you delete a job from the primary I don't see how that task would be helpful in that regards. This is our first foray into AG as we have been 2008 up until now.



What you found is right, there are 2 ways, SSIS or manual, I normally do it manually, as I wanted to make sure I got everything covered and tested. But if you have hundred of jobs, then SSIS might be the way to go,but I don't assume all of your servers have that many jobs, so manual might be easier and you can record all the changes.
However, either way should works just fine.

Hope this helps


Thanks Dennis, that makes sense. I have another question though, I see there are basically 2 ways to handle agent jobs. Either have a step at the beginning checking for primary or not, or disable on the secondary until it becomes primary. We have a lot of jobs we are migrating, and was wondering if you knew of a way to add a step at the beginning of the jobs to check for primary and if so continue to next step and if not stop it? I found one example on line but when I ran the sql it added to some and not others and didn't put the actual sql code in. Any help is appreciated.



Disregard that last question, I ended up finding one. Thank you for your input.


Not a problem, it might be best for you to post what you found here as well, it might benefit the others having the same issue.

Just an idea :slight_smile: