Database Mirroring State

  • chris.mcgowan

    SSCarpal Tunnel

    Points: 4269

    Comments posted to this topic are about the item Database Mirroring State

    ------------------------
    I am the master of my fate:
    I am the captain of my soul.
    ------------------------
    Blog: http://sqlserver365.blogspot.co.uk/
    Twitter: @ckwmcgowan
    ------------------------
  • SQLRNNR

    SSC Guru

    Points: 281243

    Nice question. Thanks for putting it together.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • CoolCodeShare

    SSCrazy

    Points: 2584

    Your question is quite unclear.

    1. What do you mean by pausing?

    2. Read from this section from BOL:

    If database mirroring is removed completely, the mirror database is in a recovery state and must be restored in order to become functional. The behavior of the recovered database with respect to replication depends on whether the KEEP_REPLICATION option is specified. This option forces the restore operation to preserve replication settings when restoring a published database to a server other than that on which the backup was created. Use the KEEP_REPLICATION option only when the other publication database is unavailable. The option is not supported if the other publication database is still intact and replicating. For more information about KEEP_REPLICATION.

    Please clarify!

  • tommyh

    SSCertifiable

    Points: 6252

    There is a "slight" difference between "pausing database mirroring" and "If the mirror is unavailable".

    Better link ?

    http://msdn.microsoft.com/en-us/library/ms190664.aspx

    /T

  • M&M

    SSC-Insane

    Points: 21699

    Chris, The question could have been more elaborate. As there are different operating modes in database mirroring, it is quite tough to choose the right answer. Anyways, got to know something regarding the mirroring\replication combination. Thanks.

    M&M

  • kapfundestanley

    SSCertifiable

    Points: 5630

    I did know that transactional replication depends on the state of the mirror.

    “When I hear somebody sigh, ‘Life is hard,’ I am always tempted to ask, ‘Compared to what?’” - Sydney Harris

  • Yokers

    SSC Eights!

    Points: 818

    Good stuff Chris, nice question.

  • chris.mcgowan

    SSCarpal Tunnel

    Points: 4269

    Sudhir Dwivedi (9/20/2011)


    Your question is quite unclear.

    1. What do you mean by pausing?

    2. Read from this section from BOL:

    If database mirroring is removed completely, the mirror database is in a recovery state and must be restored in order to become functional. The behavior of the recovered database with respect to replication depends on whether the KEEP_REPLICATION option is specified. This option forces the restore operation to preserve replication settings when restoring a published database to a server other than that on which the backup was created. Use the KEEP_REPLICATION option only when the other publication database is unavailable. The option is not supported if the other publication database is still intact and replicating. For more information about KEEP_REPLICATION.

    Please clarify!

    What I mean by pausing is if you run the below on against a mirrored database.

    ALTER DATABASE DatabaseName SET PARTNER SUSPEND;

    Granted I should have used the word suspend instead of pause.

    As the mirror partnership is not synchronised and running in High-safety mode without automatic failover - See the below from BOL from the link i provided with the answer.

    All committed transactions are guaranteed to be hardened to disk on the mirror. The Log Reader Agent replicates only those transactions that are hardened on the mirror. If the mirror is unavailable, the principal disallows further activity in the database; therefore the Log Reader Agent has no transactions to replicate.

    It is this that the question is refering to. This is however assuming that you haven't enabled trace flag 1448 😉

    http://support.microsoft.com/kb/937041

    Thanks

    Chris

    ------------------------
    I am the master of my fate:
    I am the captain of my soul.
    ------------------------
    Blog: http://sqlserver365.blogspot.co.uk/
    Twitter: @ckwmcgowan
    ------------------------
  • chris.mcgowan

    SSCarpal Tunnel

    Points: 4269

    SQLRNNR (9/20/2011)


    Nice question. Thanks for putting it together.

    Thanks

    ------------------------
    I am the master of my fate:
    I am the captain of my soul.
    ------------------------
    Blog: http://sqlserver365.blogspot.co.uk/
    Twitter: @ckwmcgowan
    ------------------------
  • chris.mcgowan

    SSCarpal Tunnel

    Points: 4269

    tommyh (9/20/2011)


    There is a "slight" difference between "pausing database mirroring" and "If the mirror is unavailable".

    Better link ?

    http://msdn.microsoft.com/en-us/library/ms190664.aspx

    /T

    I agree, but the effects with regard to transactional replication are the same if it is "paused" or "Unavailable". Good link though added to my favourites.

    Thanks

    Chris

    ------------------------
    I am the master of my fate:
    I am the captain of my soul.
    ------------------------
    Blog: http://sqlserver365.blogspot.co.uk/
    Twitter: @ckwmcgowan
    ------------------------
  • Iulian -207023

    SSCertifiable

    Points: 7509

    Thank you for the question

    I have got the answer from msdn which says:

    "Some of these factors, such as a very long-running transaction or a paused database mirroring session, can cause the transaction log to fill. "

    here: http://msdn.microsoft.com/en-us/library/ms345414.aspx

    Regards,

    Iulian

  • anthony.green

    SSC Guru

    Points: 112401

    Good question Chris

    Ant

  • michael.kaufmann

    SSCrazy

    Points: 2816

    chris.mcgowan (9/21/2011)


    What I mean by pausing is if you run the below on against a mirrored database.

    ALTER DATABASE DatabaseName SET PARTNER SUSPEND;

    Granted I should have used the word suspend instead of pause.

    As the mirror partnership is not synchronised and running in High-safety mode without automatic failover - See the below from BOL from the link i provided with the answer.

    All committed transactions are guaranteed to be hardened to disk on the mirror. The Log Reader Agent replicates only those transactions that are hardened on the mirror. If the mirror is unavailable, the principal disallows further activity in the database; therefore the Log Reader Agent has no transactions to replicate.

    Since the whole thing of QotD is about learning, I'm still not clear with the explanation given:

    1. Where in the QotD is stated that the database is running in High-safety mode without automatic failover?

    The link provided with the explanation lists this option as the last one.

    2. If the principal disallows further activity in the database, it is my understanding that there aren't any transactions. Why is it then that the transaction replication is affected? How is it affected?

    Thanks in advance for your clarification.

    Regards,

    Michael

  • rfr.ferrari

    SSCertifiable

    Points: 6879

    good question!


    [font="Times New Roman"]rfr.ferrari[/font]
    DBA - SQL Server 2008
    MCITP | MCTS

    remember is live or suffer twice!
    the period you fastest growing is the most difficult period of your life!
  • TomThomson

    SSC Guru

    Points: 104772

    Good question.

    The explanation would have been better if it said that replication would be delayed if mirroring was paused in High Performance Mode or in High Safety Mode without automatic failover (since transactions will not be hardened on the mirror while mirroring is paused), and stated also what happens if mirroring is paused in High Performance mode with automatic failover (which I believe is that replication continues as normal but with reduced replication latency - but I can't find a reference to back that up).

    Tom

Viewing 15 posts - 1 through 15 (of 25 total)

You must be logged in to reply to this topic. Login to reply