1

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?

1

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.
  • I created a job that tests sys.fn_hadr_is_primary_replica as you suggested. It enables my report jobs if the DB is primary and disables my them if the DB is not primary. The job runs at agent startup. This covers the scenario when a server is rebooted after a failure and the report jobs need to be disabled on the rebooted (now secondary) server. However, for the other server, i.e., the new primary, I use an agent alert. If AlwaysOn msg 1480 is detected on the new primary (a fail over to this server has been detected), it executes the same job to flip the report jobs from disabled to enabled – Nova Sys Eng May 31 '18 at 15:37
  • I still recommend you to check the database status - if it is online (accessible and is not readonly) and then only fire your jobs. this way you dont have to have the logic of enabling and disabling the jobs. – Kin Shah Jun 18 '18 at 13:29
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.