Jobs on the Mirror are running against the Primary

Question

I have a mirrored set of jobs between the my current primary and mirrored failover SQL Server setup. In the past, the jobs on the mirrored server, when they are enabled, fail as they are not able to open the local database since it's in a restoring state. However I'm now seeing the jobs succeed and are getting forwarded to the current primary databases.

I've seen this behavior for the SQL Server Driver that .Net uses so it will allow for an automatic reconnect if a failover occurs. I haven't seen this behavior before on a SQL Job and I can't seem to find any documentation around it.

I've found two other mentions of this behavior ( one , two ) with work arounds, however I'm having a hard time locating documentation on this feature so I know if it was a configuration change that I made or if it was enabled with an updates I installed.

Is this capability documented in any of Microsoft's references?

The jobs are T-SQL steps, and they are reading and (successfully) writing. Some are inline T-SQL and others call SPs in the target databases. I'm using standard mirroring and not availability groups.

Answer 1

It seems there are many who have faced this issue in the past and they all have implemented one or the other work-around to get rid of it. Not documented at Microsoft site or presented by any known SME(Subject Matter Expert).

First of all, I would say database mirroring is deprecated and will not be supported by Microsoft in near future, so you should plan to change it and replace with another methodology.

This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use Always On availability groups instead.

Having said above, I think there are few interesting work arounds which could be used to avoid this issue:

1. To disable SQL Server Agent jobs for the database which are part of database mirroring. This can be done using code given at end:
2. To use database name as a first step for jobs running on secondary database, since database will be restoring or stand-by state. Job will fail.
3. Stop SQL Server Agent if all the jobs are related to databases part of database mirroring.
4. Get the value of mirroring role from msdb.sys.database_mirroring and include this in your job step. Read more here how to do it.

To disable job steps, you may use below script if there are many jobs:

DECLARE @sql nvarchar(max)
    ;WITH cte AS (
    SELECT  j.job_id,
            j.[name]
    FROM msdb.dbo.sysjobs j
    WHERE j.[name] like 'Job_Name%' 
    )
    SELECT @sql = (
        SELECT 'EXEC msdb.dbo.sp_update_job @job_id = '''+CAST(job_id as nvarchar(max))+''', @enabled = 0;'+CHAR(10)
        FROM cte
        FOR XML PATH('')
    )
    EXEC sp_executesql @sql

Hope this helps.