Hi,
i have implemented AG fail over alerts to my servers and next my target is
Whenever failover happens from primary server to secondary server jobs needs to disable on the old primary server and job needs to enabled in new primary (secondary) server
Can you please suggest how to do this and please give the script if you have.
in our ag setup we have a dedicated bi server.
ssas
ssis
ssrs
I would highly recommend that approach.
Then it is a matter of changing a config file
I'd recommend a different approach, one that I use. Add a query to check which server is the primary, and return immediately or throw an error if it's not. Then copy the job to every server in the AG.
I add the check in every job step that needs it, and have it return if it's a secondary. That way individual steps just fall through on the secondary.
If you throw an error, you can place it as the first step of every job and have the job quit on error, it's a bit simpler.
We do it slightly differently. All the jobs check if they are secondary. If they are, they just return. They are scheduled and run on all the nodes. But on the secondaries they just return before doing any processing.