Thanks for reading, here is what i need to figure out:
Below script is what i think might fix my issue. I know how to do them individually, but i want them to run as whole.I'll use them in a job or automation in case if the databases on my AlwaysOn Availability group is not a primary, then the sql agent or agent XPs should turn off. This will help me to stop simultaneously jobs running on both Primary and Secondary.
- In short, i need a script to turn off SQL agent when the role from primary change, and turn back ON when the role is primary for always on availability group.
I have two instance, A primary, and B secondary. I have setup everything on my AOAG for my production including backups, however this issue come to my mind and don't wanna do it manually ( not nice ).
Here is what i have
use master go if sys.fn_hadr_backup_is_preferred_replica ('HardDB') = 0 --- If the Database is not a primary replica and is 0, then begin sp_configure 'show advanced options', 1 -- trun on the advanced options reconfigure ; sp_configure 'agent XPs' , 1 -- Then, off the SQL Agent XPs reconfigure go sp_configure 'show advanced options', 0 --- then, trun back off the advanced options. reconfigure go