SQL Server Jobs on High Availability Secondaries

SQL Server Administration
1

We have about 100 jobs on our Primary SQL Server. We have a Secondary Server in High Availability. Are concern is if we have to fail-over to the Secondary Server our SQL Server jobs aren't there. What is a good approach to handling this? I'm guess there isn't a way to fail SQL jobs over?

2

There's 2 main ways to approach this:

  1. Create all jobs on all replicas, but disable them on the secondaries. Have another job or monitor process check which node is the primary, and enable the jobs there, while disabling on the secondaries.
  2. Add some logic to your job steps to test if the database(s) they're accessing are primary or not, and either fail the job or use a RETURN statement immediately to cancel further execution.

I use the second technique in my SQL Agent jobs, with this function:

Basically I add to the top of each job step:

if fn_hadr_is_primary_replica('myDatabase')=0 return;
-- rest of code

I found that to be a lot easier to manage than disabling or enabling jobs every failover. Every job can stay enabled, and the job does nothing if it's running on a secondary. I even have some jobs that only need read access, so I could run them on a secondary and take some load off the primary.

That logic also works on databases not in an AG, the function will return NULL in that case, so the IF will not evaluate to true.

The linked video is from a PASS Virtual Chapter meeting that discusses how to do the first technique:

3

thanks

4

I forgot to add that my technique only works for Availability Groups, if you're using failover cluster instances or database mirroring that function won't help. You could still apply some logic to test which is the primary node and act accordingly.

EDIT: I just remembered an FCI uses shared storage and only single copies of databases, so the jobs run the same regardless of which node is active, you don't need to check.

5

@denis_the_thief have a look at MSX (Master) / TSX (Target) servers in SQL Server. It will allow you to deploy the same job to multiple servers.

With a logic that handles primary/secondary, it will be a single place to manage your jobs. Look for "Create a Multiserver Environment - SQL Server Agent"