locked
SQL Agent Jobs and mirroring RRS feed

  • Question

  • Good morning,

    We have database mirroring implemented in high availability mode. Servers are 2008 Ent.

    The servers have a few jobs created. Each of the jobs includes one or more T-SQL steps - SP calls. Those jobs have different schedules - some of them executing once per day, some are constantly running. Sql Agent is running on both principal and mirror instances.

    We noticed quite interesting behavior. When SQL Agent service is (re)started, jobs on the mirror server fails with an error "database is not available". When failover occurs, jobs on the new principal (former mirror) start to run (which we expected). What surprise us that jobs from the new mirror server (former principal) failovers too and running on the new principal. As result, we have 2 instances of the jobs running after failover.

    Is this expected behavior? Can we change something in configuration to prevent that?

    As the workaround we developed another job which is checking the status of the database and stop/disable jobs on the instance if DB is in restoring mode. It works although I wonder if there are better solutions available.

    Thank you

     

     

    Friday, June 25, 2010 1:41 PM

All replies

  • hi Dmitri,

    The workaround appears to be good but the jobs running on both principal and mirror server may be a concern. When this happens, are both the databases online (should ideally not be this) on principal/mirror ? Do the jobs run under context of the respecting principal/mirror database or do they run in context of master/"non-mirrored" database ?

    Sunday, June 27, 2010 8:58 AM
  • Hi,

    Database Mirroring works at the database level. When a failover occurs, the mirror server cannot take over the SQL Server Agent jobs automatically. To run the jobs on the mirror server, you need to create the same jobs on the both server. In the case of the failover, disable the jobs on the principal server and enable the jobs on the mirror server. Another workaround is using SQL Agent job categories to automate SQL Agent job enabling with Database Mirroring. For more information, please refer to http://blogs.msdn.com/b/sqlcat/archive/2010/04/01/using-sql-agent-job-categories-to-automate-sql-agent-job-enabling-with-database-mirroring.aspx.


    If you have any more questions, please let me know.
    Thanks.


    ***Xiao Min Tan***Microsoft Online Community***
    Monday, June 28, 2010 8:32 AM
  • Thank you for the replies, Vijay and Xiao-Min!

     

    Xiao-Min, this is exactly the behavior I am expecting. Although it does not work that way. Like I said before, when failover occurs, both instances of the jobs are running - from the principal and from the mirror. When I trace it in SQL profiler, I can see 2 instances of the stored procedure running from 2 different hosts - principal and mirror. Again, it happens only after failover. If I restart SQL Agent service on the mirror instance, jobs on the mirror fail.

    Vijay, those jobs are running under context of the mirrored database. When failover occurs, one database is online and second one is on "restoring" stage.

    My current solution is very similar to what mentioned in the blog. It works. I just wonder why it happens.

     

     

    Monday, June 28, 2010 1:14 PM

  • Thank you for the replies, Vijay and Xiao-Min!

     

    Xiao-Min, this is exactly the behavior I am expecting. Although it does not work that way. Like I said before, when failover occurs, both instances of the jobs are running - from the principal and from the mirror. When I trace it in SQL profiler, I can see 2 instances of the stored procedure running from 2 different hosts - principal and mirror. Again, it happens only after failover. If I restart SQL Agent service on the mirror instance, jobs on the mirror fail.

    Vijay, those jobs are running under context of the mirrored database. When failover occurs, one database is online and second one is on "restoring" stage.

    My current solution is very similar to what mentioned in the blog. It works. I just wonder why it happens.

     

     

    This is very interesting because I am experiencing the same issue on my servers - a failover occured and since then both servers are trying to run job against the same instance of DB...

    It seems that jobs on the current mirror (formerm Principal) switch to new location of the database as any other client application using parner IP address.

    The problem is that this behaviour is unexpected and I found this because a deadlock which occured when both jobs tried to do an update against the same table.

     

    Tuesday, July 27, 2010 11:48 AM