AlwaysOn and Agent Jobs

Question

AlwaysOn and Agent Jobs

joepacelli

SSCrazy

Points: 2088
More actions
September 3, 2014 at 12:22 pm

#397294

We are researching AlwaysOn and we have an Agent Job that runs against our database to purge data
We copied the Agent Job to both nodes, and want to code the job so it does not run on secondary read-only node
We tried putting the following code in the step
DECLARE @dbMode varchar(10)
SELECT @dbMode=CONVERT(sysname,DatabasePropertyEx('APF_OLTP','Updateability'))
print @dbMode
if (@dbMode <> 'READ_ONLY')
BEGIN
USE [APF_OLTP]
EXEC [Archive].[PurgePremiseEvent] @NumberOfDaysToRetain = 30
END
I'm trying to test on the secondary read-only node running this in a query window and get the following error
Msg 978, Level 14, State 1, Line 6
The target database ('APF_OLTP') is in an availability group and is currently accessible for connections when the application intent is set to read only. For more information about application intent, see SQL Server Books Online.
If we comment out the USE statement, I get
READ_ONLY
So I know my dbMode is READ_ONLY
How do you check make an Agent Job only run if on primary Node?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Shawn Melton SSC-Insane Points: 24685 More actions · Answer 1

Include logic in the job to verify if you are running on primary replica or not. You can do this by checking sys.dm_hadr_availability_replica_states and the "is_local" column is set to 1 (one) or not.

Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton

joepacelli SSCrazy Points: 2088 More actions · Answer 2

The code needs to be able to run on non-AlwaysOn environment's

For example, existing client on SQL Server 2008 R2 who updates to our latest release

Our goal is to be able to provide our clients with an All-In-One solution

They can run on SQL Server 2008 R2 with a single database and setup their own HA/DR using replication or whatever means they want

or

They can upgrade to SQL Server 2012 and utilize the name AlwaysOn feature.

Shawn Melton SSC-Insane Points: 24685 More actions · Answer 3

joepacelli (9/3/2014)
The code needs to be able to run on non-AlwaysOn environment's
For example, existing client on SQL Server 2008 R2 who updates to our latest release
Our goal is to be able to provide our clients with an All-In-One solution
They can run on SQL Server 2008 R2 with a single database and setup their own HA/DR using replication or whatever means they want
or
They can upgrade to SQL Server 2012 and utilize the name AlwaysOn feature.

So include a check to verify major version equal to or greater than 11. This would mean you are on 2012 and up and that DMV will exist.

Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton

Shawn Melton SSC-Insane Points: 24685 More actions · Answer 4

If this is a solution you are providing to multiple clients it is much more efficient to put any code in a procedure and not the SQL Agent job itself. You introduce to much difficulty maintaining updates or patches to code. Updating a procedure is much easier than trying to write all the msdb commands required to update a SQL Agent job.

Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton

joepacelli SSCrazy Points: 2088 More actions · Answer 5

It is a procedure within our database that purges out data.

The job calls this procedure.

But in AlwaysOn we have to put this job on every node

On the secondary node this job throws an error since the database is read-only mode

Prior to trying to implement an AlwaysOn solution, the job reside on our primary server and we replicated data to a reporting server

These jobs did not reside on the replicated server.

Now with AlwaysOn this job needs to be on both the primary and secondary node

Within the properties of the job we use to set to our database

And it would call the stored procedure

EXEC [Archive].[PurgePremiseEvent] @NumberOfDaysToRetain = 30

But as I stated, in AlwaysOn on the read-only secondary this job with throw errors

So we changed the database to be master

and added the following logic

DECLARE @dbMode varchar(10)

SELECT @dbMode=CONVERT(sysname,DatabasePropertyEx('APF_OLTP','Updateability'))

print @dbMode

if (@dbMode <> 'READ_ONLY')

BEGIN

USE [APF_OLTP]

EXEC [Archive].[PurgePremiseEvent] @NumberOfDaysToRetain = 30

END

Everything works fine except it still throws an error.

Which makes no sense.

I get the Updateability of the database, which returns READ_ONLY

My if statement checks this value.

So if I'm on the secondary it should not execute but it seems to execute the USE statement because I get the error mentioned above

If I comment out the USE statement it executes fine

but I can not do this because I'll need it to connect to this DB to execute my procedure

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 6

One thing you can do is to make sure the Job is enabled on the right node.

You can achieve this by having another job running on both the primary and read-only secondary that checks the role and enables or disables the Job.

-Roy