July 2, 2014 at 12:40 am
Hi all,
I need a quick help over here. Currently, we have 3 scripts that needs to be executed and setup as job on a secondary-replica. The only problem is that we want to run it from secondary-replica and it requires application-intent read only which is still not implemented in the application. The job is right now failing on the secondary and we need to make it work maybe by using some programming logic but I am not sure how to do. Could you pls. suggest some measures and I know there is no way in sqlagent where we can set it to read-intent only. Pls. let me know if someone has implemented this and we only need to run it from secondary.
Regards,
Faisal
July 3, 2014 at 6:41 am
sounds like the secondary is not set read only, check this and post back
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 3, 2014 at 6:48 am
Hi Perry,
Its been set to read only but not read-intent (I mean the application doesn't have this option yet) the job is still failing on the secondary, and as said it has to run from secondary. Could you pls. let me know if this can be taken care programmatically.
Regards,
Faisal
July 3, 2014 at 6:51 am
Well, you could change the "Readable Secondary" to "YES", instead of "Read-Intent only".
That is the only way you will be able to read from the secondary db, unless the application has the "applicationintent=readonly" parameter.
Steve
July 3, 2014 at 7:01 am
Hi Steve,
I already said that it's read only for secondary. I am only concerned whether I could run a job from secondary and if yes then it should not fail for some reason. Basically, can we handle this programmatically to check whether this is secondary or primary and then based on that run the job. I mean some kind of handle before the actual statement begins.
Regards,
Faisal
July 3, 2014 at 7:07 am
I must have misunderstood your original post:
" run it from secondary-replica and it requires application-intent read only "
As you saw, there are three options "NO","YES","Read-Intent Only". Setting to "YES" allows you to read from the secondary without the application parameter.
In response to your follow up to me, yes, you can check if you are on the primary, or secondary node prior to running. That is how I set up all of my jobs, where each job is duplicated on each server in the AG. The first step in the job is to check "am I primary replica", if "yes" then proceed, else exit the job.
Steve
July 3, 2014 at 7:12 am
Do you have a script to check if it's primary or secondary? Could you pls. post it in case if you have one.
Regards,
Faisal
July 3, 2014 at 9:07 am
faisalfarouqi (7/3/2014)
Do you have a script to check if it's primary or secondary? Could you pls. post it in case if you have one.Regards,
Faisal
This will pull the Primary replica for an AlwaysOn group
select b.name, a.primary_replica,
a.primary_recovery_health_desc
from [sys].[dm_hadr_availability_group_states] a
inner join [sys].[availability_groups] b
on a.group_id = b.group_id
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply