Managing Agent Jobs in Availability Groups

Posted On: In: Microsoft SQL Server

Availability groups and SQL Server Agent Jobs, how do you go about managing them? In our environment we have two availability group members, for the purposes of this post we will call them S1 and S2 one is always primary and the other is the read-only secondary if the availability group fails over the roles will change, but you knew that right?

So S1 is the primary and S2 is the secondary, I had a number of SQL Server Agent jobs that existed on both members, the job would run on both members but always fail on the secondary, as my jobs are setup to alert when the job fails we were getting alerted that the job had failed on S2 but really it hadn’t it just wasn’t able to run on S2 because that was in a read-only state.

So what can we do?

We could write a check into each job to see if it was the primary every time it runs, setting this at Step 1 so if Step 1 failed the job would exit but this would become messy, I wanted something we could manage centrally.

Break out the Utility Database

Our availability group has a Utility Database, this is where all the DBA stored procedures and views that we use are stored, you may have one called something similar, or you may not have one at all but it is useful to create one if not, it keeps everything nice and tidy.

The Utility database is synchronized as part of the Availability Group so I set to looking for a solution that would allow me to use this database to manage my jobs.

The Setup

This stored procedure uses a while loop – there may be a better way to do this but this way works really well for me.

Inside the Utility Database, we needed to create a table to store the names of the SQL Server Agent Jobs we wanted to disable on the secondary.

Now that we have our table we can populate it with some of the jobs we would like to manage, the active column on the table means that that job will be included in the check.

Once you have populated the table with all of the jobs you would like to include in the check, we will need to create the stored procedure that will enable/disable the jobs depending on the member status. I have included an example below.

What Is Going On?

Alright, let me try and explain what that stored procedure is doing, I will break it down into manageable chunks

The first section is going to check to make sure that the DBA_Tasks database exists, if it doesn’t it will create it, of course, if your utility database is called something else you will want to go ahead and change that section.

At this point it will also check to see if you already have the stored procedure p_ExcludedJobs in the DBA_Tasks database, if it does exist already it will be dropped and re-created.

We also need to make sure that the required table exists, as mentioned in the Setup section, [dbo].[ExcludedJobs] is used to store the name of the jobs we would like the stored procedure to manage for us, if this table is missing it won’t have any jobs to manage.

These are the variables we are going to use in the Stored Procedure, nothing too fancy going on here.

#Excluded_Jobs is a SQL Temp Table, these are per session tables that are deleted when the session ends or when they are dropped as part of the executing batch, this table is going to be used to store all of the jobs we have in the Excluded Jobs table that we created earlier so that we can loop through them.

We now need to insert all of the jobs we are managing into the temp table, you will notice we are only getting jobs where active = 1, if for some reason someone has decided that a job shouldn’t be disabled/enabled automatically and set active = 0 we don’t want to include that in this check.

Now we have two variables

@MaxID – We will set this to the MAX value of the ID column from #Excluded_Jobs essentially this will give us the number of passes we need to do before ending the script, for example, if #Excluded_Jobs has 4 rows @MaxID would be set to 4

@Counter – Initially we are setting this to 1 it will then increment until it reaches the value of @MaxID then the stored procedure will end, this allows us to loop through all of the jobs we are managing and disable/enable them based on the availability group member status.

Availability_RoleĀ  – We need to find out what the member status is of the node where the agent job is running, you will need to change the name ag.name = ‘InstanceName’ to the name of your availability group else NULL will be returned and nothing will happen when the job runs.

This is where the loop begins, we are checking to make sure that @Counter is less than or equal to @MaxId

The next section is where the jobs are either enabled or disabled depending on @Availability_Role at the very top we are going to get the @Job_Name we do this by selecting Job_Name from #Excluded_Jobs where the ID of the row equals the ID of @Couner.

Once we have this we check to see what @Availability_Role is set to, if it is Primary we are going to Enable the Job, if @Availability_Role is not set to Primary we are going to disable the job.

Once the Enable or Disable of the job is done we will increment the @Counter so that the loop can continue or end, depending on if the WHILE is matched.

If the @Counter is greater than @MaxID we will end and drop the table #Excluded_Jobs, that will then end the procedure.

Fitting It All Together

Now that you have all the required elements to make sure that this process is going to work we need to fit it all together.

Your going to need to create a SQL Server Agent Job that will call the stored procedure we created above, when creating the Agent Job I usually set it to run once every minute so that if the Availability Group fails over the jobs will be disabled or enabled before too much goes wrong.

Let’s show you how to do that then yeah?

First, On the object explorer in SQL Management Studio expand the SQL Server Agent by clicking the +

Next, you will see a bunch of folders appear, one of which is called Jobs, right-click that

A context menu will appear, from this menu select New Job.

This will launch a new window where we can setup our job, enter a name in the name field, I have gone ahead and called mine Availability Member Check, you can call it whatever you want, I have also changed the Owner to sa (I have renamed my sa account)

Next, from the new job window, select Steps from the Select a page pane on the right-hand side of the window. Once you have clicked Steps from the bottom select New.

1. Enter a name for the step, I call called this Run Stored Procedure
2. Choose the Database in which the stored procedure lives in, DBA_Tasks should exist if you have followed this post all the way, if not select your utility database.
3. Enter the following T-SQL

4. Once you have completed that, Click OK

From the side panel, of the New Job window, select Schedules, then click New

1. Enter a name for the step, I have called mine Every Minute

2. From the Frequency Section, change the Occurs drop down to Daily

3. From the Daily Frequency section, select the Occur Every radio button and change the drop down to minutes then enter 1 into the box provided.

4. Once you are happy Click Ok

Finally, select the Notifications page from the side bar on the New Job window.

1. Check the email box

2. Choose the notification group who you would like to Notify in the event that this job fails, I have selected The DBA Team.

Once you are happy, Click OK the new job will now be created.