How do I automatically enable or disable report jobs at AOAG failover?

by Nova Sys Eng   Last Updated June 18, 2018 13:06 PM

I have an AlwaysOn Availability Group with 2 DB servers. I'm attempting to use SQL Server Agent Alerts to programmatically disable a handful of SQL Server Agent jobs (that must not run on a secondary) when either of these DB servers is restarted (after it's been powered down for whatever reason).

I see that message 35266, e.g., AlwaysOn Availability Groups connection with primary database established for secondary database 'XXXX' ...... is logged when the DB server is powered up. So, I can set an alert for it and execute a job to disable the jobs I mentioned above. However, I've noticed that this message is consistently logged twice per database, one immediately after the other.

Why is message #35266 logged twice per database?



Answers 2


I'm attempting to use SQL Server Agent Alerts to programmatically disable a handful of SQL Server Agent jobs (that must not run on a secondary)

Instead of doing that, just check if the replica is primary or not using :

If sys.fn_hadr_is_primary_replica ( @dbname ) <> 1   
BEGIN  
-- If this is not the primary replica, exit (probably without error).  
END  
-- If this is the primary replica, continue to do your stuff.  

you can also create a generic SP for all your agent jobs to check first in master to check few status e.g. dbo.usp_CheckDBOnline

  • check if replica is primary or not
  • check if a database is part of mirroring or AlwaysON availability group and is accessible or not.
Kin
Kin
May 31, 2018 14:38 PM

As it turns out, AOAG event ID #35266 quite regularly occurs 2 or 3 times as a node is coming up or down. So long as what you when a 35266 is detected isn't toggle-like in nature, you should be ok. It works fine in my case because what I do when I detect a 35266 is to (depending on the text in the event) either enable or disable specific jobs. So if I enable my job 2 or 3 in rapid succession, no big deal

Here is what I ended up with.

Create SQl Server Agent job 'Enable Or Disable Rpt Jobs'

Accept defaults except as follows

  • Name: Enable Or Disable Rpt Jobs
  • Owner: account that has permissions to run T-SQL to enable/disable SQL Server Agent jobs
  • Enabled: Checked

Create job step:

  • Step name: enable_or_disable
  • Type: Transact-SQL script
  • Database: master

Command:

DECLARE @DB nvarchar(25)
DECLARE @MSG nvarchar(200)
SET @DB = '{DB name that provides report data}'

If sys.fn_hadr_is_primary_replica ( @DB ) = 1  
BEGIN 
    EXEC msdb.dbo.sp_update_job @job_name='{job name}',@enabled = 1
    .
    .
    .
    EXEC msdb.dbo.sp_update_job @job_name='{job name}',@enabled = 1
    SET @MSG = '"' + @DB + '" is PRIMARY. Enabled report jobs'
    PRINT @MSG
END 
ELSE
BEGIN
    EXEC msdb.dbo.sp_update_job @job_name='{job name}',@enabled = 0
        .
        .
        .
    EXEC msdb.dbo.sp_update_job @job_name='{job name}',@enabled = 1
    SET @MSG = '"' + @DB + '" is not PRIMARY. Disabled report jobs'
    PRINT @MSG
END

Schedule:

  • Enabled: Yes
  • Schedule Type: Start automatically when SQL Server Agent Starts

Create SQl Server Agent Alert 'Alert Disable Rpts on 35266'

Accept defaults except as follows

  • Name: Alert Disable Rpts on 35266
  • Database name:
  • Error number: 35266
  • Raise alert when message contains: checked
  • Message Text: connection with primary database established for secondary database '{DB name that provides report data}'

Example: connection with primary database established for secondary database 'DB_NAME'

Response:

  • Execute job: checked
  • Select the job: Enable Or Disable Rpt Jobs

Create SQl Server Agent Alert 'Alert Enable Rpts on 35266'

Accept defaults except as follows

  • Name: Alert Enable Rpts on 35266
  • Database name:
  • Error number: 35266
  • Raise alert when message contains: checked
  • Message Text: connection with secondary database established for primary database '{DB name that provides report data}'

Example: connection with secondary database established for primary database 'DB_NAME'

Response:

  • Execute job: checked
  • Select the job: Enable Or Disable Rpt Jobs
Nova Sys Eng
Nova Sys Eng
June 18, 2018 13:04 PM

Related Questions


How to script alerts in SQL Server?

Updated June 06, 2017 11:06 AM

High Availability Options For SQL Server

Updated May 11, 2017 16:06 PM

SQL Server AlwaysOn FCI vs AG Doubts

Updated June 30, 2017 21:06 PM