When moving application databases to AOAGs they often have a bunch of jobs that go with them (usually SSIS ETL packages). Most vendors leave these to fail whenever respective nodes aren't the primary and this leads to a messy system with spurious errors.

I don't like leaving constantly failing jobs on a server! Instead I prefix each job with a standard job step of my own which checks:

  • Is this the right node (i.e. is it the primary)?
  • Does the database exist and is it ONLINE?

You could very well add a little bit to check for mirroring also. Anyway I do this because aside from avoiding errors sometimes database disappear or are in recovery because a re-sync is in progress.

Here's the template code I use. Create a job step, specify the database in the job step, and set it to exit with a success condition on failure.

Set Nocount On
 
Declare       @DatabasesToCheck Table (
       name Sysname Not Null
)
Insert @DatabasesToCheck
Select Db_Name()
 
If  (
    Exists (
        -- Database in an AG
        Select  Top 1 0
        From    sys.availability_databases_cluster adc
        Join    sys.availability_groups ag
        On      adc.group_id = ag.group_id
        Join    sys.dm_hadr_availability_group_states dhags
        On      ag.group_id = dhags.group_id
        Where   adc.database_name In (Select dtc.name From @DatabasesToCheck dtc)
        )  
    And     Not Exists (
        -- Database in an AG which is Primary on this instance
        Select  Top 1 0
        From    sys.availability_databases_cluster adc
        Join    sys.availability_groups ag
        On      adc.group_id = ag.group_id
        Join    sys.dm_hadr_availability_group_states dhags
        On      ag.group_id = dhags.group_id
        Where   adc.database_name In (Select dtc.name From @DatabasesToCheck dtc)
        And     Upper(dhags.primary_replica) = Upper(@@Servername)
        )
    )  
Begin
    Raiserror('This is not the primary replica.', 16, 1) With Nowait
    Return
End
 
-- Check if the database isn't accessible
If  Not Exists (
    Select  Top 1 0
    From    sys.databases
    Where   databases.name In (Select dtc.name From @DatabasesToCheck dtc)
    And     state_desc = 'ONLINE'
    )
Begin
    Raiserror('The database doesn''t exist or isn''t ONLINE on this node.', 16, 1) With Nowait
    Return
End

Don't forget to make this step exit on failure with success.