How do I automatically enable or disable report jobs at AOAG failover? 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?
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 do 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 on the primary node 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