All we need is an easy explanation of the problem, so here it is.
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.
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
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:
- To disable SQL Server Agent jobs for the database which are part of
database mirroring. This can be done using code given at end:
- 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.
- Stop SQL Server Agent if all the jobs are related to databases part of database mirroring.
- Get the value of mirroring role from
msdb.sys.database_mirroringand 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.
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂