locked
I have a VP that wants to turn Mirroring off in the morning and put it back on in the evening. Can this be done as a job in SQL Server Agent and how would you do it? RRS feed

  • Question

  • I have a VP that wants to turn Mirroring off in the morning and put it back on in the evening. Can this be done as a job in SQL Server Agent and how would you do it?


    lcerni

    Wednesday, April 11, 2012 11:31 AM

Answers

  • Hi,

    Why do they want to do this? I would not recommend suspending a mirror session for extended periods or regularly. You could pause or suspend mirroring however this has implications for the transaction log on the principal copy of the database.

    http://msdn.microsoft.com/en-us/library/ms190664(v=sql.105).aspx

    To pause or suspend you could use T-SQL in a job and then resume it via T-SQL also.

    -- To pause the mirroring session
    ALTER DATABASE <mirrored_dbname> SET PARTNER SUSPEND 
    
    -- To resume the mirroring session
    ALTER DATABASE <mirrored_dbname> SET PARTNER RESUME
    


    Sean Massey | Consultant, iUNITE

    Feel free to contact me through My Blog, Twitter or Hire Me.
    Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!


    • Edited by Sean Massey Wednesday, April 11, 2012 11:42 AM
    • Marked as answer by lcerni Thursday, April 12, 2012 6:36 PM
    Wednesday, April 11, 2012 11:40 AM
  • Hi,

    Also, I've seen that the database gets so far out of whack, that the mirroring fails to catch up and there's no alternative but to set it up from scratch.

    What's the motive behind turning off mirroring?  Maybe there's an alternative that can be suggested...



    Thanks,

    Andrew Bainbridge
    SQL Server DBA

    Please click "Propose As Answer" if a post solves your problem, or "Vote As Helpful" if a post has been useful to you

    • Marked as answer by lcerni Thursday, April 12, 2012 6:36 PM
    Wednesday, April 11, 2012 11:43 AM

All replies

  • Hi,

    Why do they want to do this? I would not recommend suspending a mirror session for extended periods or regularly. You could pause or suspend mirroring however this has implications for the transaction log on the principal copy of the database.

    http://msdn.microsoft.com/en-us/library/ms190664(v=sql.105).aspx

    To pause or suspend you could use T-SQL in a job and then resume it via T-SQL also.

    -- To pause the mirroring session
    ALTER DATABASE <mirrored_dbname> SET PARTNER SUSPEND 
    
    -- To resume the mirroring session
    ALTER DATABASE <mirrored_dbname> SET PARTNER RESUME
    


    Sean Massey | Consultant, iUNITE

    Feel free to contact me through My Blog, Twitter or Hire Me.
    Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!


    • Edited by Sean Massey Wednesday, April 11, 2012 11:42 AM
    • Marked as answer by lcerni Thursday, April 12, 2012 6:36 PM
    Wednesday, April 11, 2012 11:40 AM
  • Hi,

    Also, I've seen that the database gets so far out of whack, that the mirroring fails to catch up and there's no alternative but to set it up from scratch.

    What's the motive behind turning off mirroring?  Maybe there's an alternative that can be suggested...



    Thanks,

    Andrew Bainbridge
    SQL Server DBA

    Please click "Propose As Answer" if a post solves your problem, or "Vote As Helpful" if a post has been useful to you

    • Marked as answer by lcerni Thursday, April 12, 2012 6:36 PM
    Wednesday, April 11, 2012 11:43 AM
  • Hello,

    I do not recommend you to pause mirroring during the day because you will see problems with the transaction log file (they will grow)

    No sure if breaking the mirror during the day and recreating it at night is a good idea


    Javier Villegas | @javier_villhttp://sql-javier-villegas.blogspot.com/

    Please click "Propose As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you

    Wednesday, April 11, 2012 12:12 PM