Making SQL Agent Jobs Aware of Availability Groups

What happens if you have a SQL Agent job, but the logic is not aware (or cannot be aware) that you may have an Availability Group in place. There is nothing built-in to Agent to account for this, but there is a way can you configure an Agent job to check for the existence of an AG before proceeding.

Here is the scenario.

First, we have a 2-node cluster running a synchronous Availability Group with automatic failover and readable secondaries turned off. Next, we have a requirement to run a SQL Agent job every hour to collect data from a user database. Since the AG is configured for automatic failover, we need to make sure the job will continue to run successfully regardless of which node is active.

With readable secondaries turned off, this creates a problem for any Agent job that needs to read from those user databases. Those jobs will fail with an error saying the database not accessible for queries.

We can easily get around this issue by using a single query and an extra job step.

First, we need to add a TSQL job step that we will call “AG Check” to execute the following query.

IF sys.fn_hadr_is_primary_replica('WSS_Content') <> 1
  THROW 50000, 'This is not the primary replica.', 1;

This query uses a DMF, sys.fn_hadr_is_primary_replica, to determine if a database is a part of the primary or secondary replica. The function will return a value of 1 if we are on the primary replica, so if it returns anything else then we want to throw an error. The error will allow us to change the behavior of the job.

Note: sys.fn_hadr_is_primary_replica was introduced in SQL Server 2014; however, I wrote a function that can do the same thing for SQL Server 2012.

Next, on the Advanced tab, we need to change the On Failure Action to “Quit the job reporting success”, then click OK.

We want to use “Quit the job reporting success” in stead of “Quit the job reporting failure”, so the overall result of the job will be successful. That way we will not cause additional email alerts to be sent.

Finally, on the Steps tab, we need to use the Move arrows to make sure AG Check is the first step on the list, and make sure it is listed as the start step.

The result is we that we now have a job with two steps. The first step checks the status of the replica. If it succeeds, then we can proceed to the next step to do actual work. If the first step fails, then we gracefully exit the job.

There are other ways you can setup an Agent job to account for an Availability group, but this method is quick and easy to setup for almost any environment.