January 27, 2016 at 6:33 am
Looking for some guidance as this is our first Always On SQL Server.
Running SQL2014 SP1. Databases in Always On.
Questions: Cannot run Check Integrity on the read only copy, job fails... which makes sense.... My question is since you don't know which node the Primary will be on at any one time how do you schedule Check Integrity jobs since the job on the Secondary will always fail?
January 27, 2016 at 6:56 am
What we did was deploy all jobs on all instances from TFS in a disabled state. Then a "controller job" was implemented on both instances that would run once a minute checking to see if the instance was hosting the primary and if so it would enable all the relevant jobs. If it was the secondary it would disable all the relevant jobs.
We built in some flex by implementing a config table in our dba-database to allow us to say which jobs should be controlled by the controller job.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 27, 2016 at 7:06 am
We deployed all jobs to both servers, and for jobs we only wanted to run when it's the primary, we added a wrapper around it like this:
IF (SELECT MASTER.DBO.FN_AG_IsPrimary('<AG_NAME>')) = 1
BEGIN
PRINT 'DOING THE JOB'
END
ELSE BEGIN
PRINT 'NOT DOING THE JOB'
END
It's not the most elegant solution, but it ended up working well for us.
The Redneck DBA
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply