:::: MENU ::::

Are SQL Agent jobs mirror aware?

When this first was brought up I thought ‘hmm… no’ and left it at that, until one of the production systems with mirrored databases got some corrupted data.

The DB developer, which also had created the agent job, asked me again, very politely I must say, if maybe there was a chance an agent job starting on the instance holding the mirror database could login to the principal instance and run it’s code there. Again, naively I said no, no It can’t.



Anyway, it seems that under certain circumstances a job that is started in the mirror instance will actually login to the principal and run the code there. I had never seen that before and would still be denying it hadn’t I seen evidence that Spotlight picked up, I could see in the process list logins from the mirror host in the principal host.

So, what happened and why?? It seems that if you create a job with a jobstep where you specify a database that is mirrored, and the job starts in the mirror instance, then it will/could outsmart itself and login to the principal and run the code there. I checked the sp_add_jobstep documentation and there was no mention of it. Have created a ticket with MS, will be interresting to see what they answer.

Will update this when I know more.



So, what do you think ?