Monday 20 April 2015

SQL Jobs fail when run on a mirrored server

We have jobs that run on a mirrored server. When these jobs are running on the principal server, no problems, but when they ruin on the mirror nothing works.
Ok, there are several solutions to this problem. Disable jobs on the mirror, but you have to remember to enable these when the mirror fails over and then disable the jobs from the other. Yes you could script this all out.

The current approach I am using is to add a new step to the job, making it the first step. This executes the following SQL.

DECLARE @mirroring_role INT

SET @mirroring_role = (SELECT mirroring_role FROM msdb.sys.database_mirroring WHERE database_id = db_id('Database Name'))

IF @mirroring_role = 2
   raiserror('The database is running as a mirror',11,1)


Ensure that you set the steps "On failure action" to "Quit the job reporting success"

No comments: