Contents
When to enable or disable jobs on failover?
On all 3 instances we have the jobs AG Database Health and AG Replica Health. These jobs should only be enabled to run if the instance is the primary replica for AGroup_Dev. First, create a new job category that will be assigned to any jobs that must have a specific action applied to it if a failover occurs.
What happens if the always on availability group fails?
Always On Availability Groups monitors the health of both replicas in an automatic failover set. If either replica fails, the availability group’s health state is set to CRITICAL. If the secondary replica fails, automatic failover is not possible because the automatic failover target is unavailable.
How to create a job category on failover?
First, create a new job category that will be assigned to any jobs that must have a specific action applied to it if a failover occurs. In my example below, I have created the job category AG1 on the AlwaysOn1 instance.
How to enable or disable jobs on failover sqlrx-SQL Server expertise?
SET @SQLString = ‘EXEC msdb.dbo.sp_update_job @job_name = ”’ + @jobname + ”’ , @enabled = 0′ So, now let’s put it all together and fill in the rest of the code….. In our development server we are using the VM’s and instances that were used in the AlwaysOn Monitoring and Alerting post and the AGroup_Dev availability group that was set up.
Can you enable or disable AG database health jobs?
As you can see the AG Database Health and AG Replica Health jobs are now disabled on AlwaysOn1 and are enabled on AlwaysOn2. Of course the job code can be changed to accommodate more than 1 availability group and more than 1 job category. Be creative and have fun with it!
How to enable or disable jobs in SQL Server?
All I would need to do is add some logic that would roll through (yes, sadly that means a cursor) each job returned by the query above and run sp_update_job to enable or disable it. SET @SQLString = ‘EXEC msdb.dbo.sp_update_job @job_name = ”’ + @jobname + ”’ , @enabled = 0′ So, now let’s put it all together and fill in the rest of the code…..