Question about Log Mechanics and Mirroring RRS feed

  • Question


    I'm trying to understand some unexpected behavior and gain a better grasp of what's happening mechanically under the covers of database mirroring.


    We have a database that is mirrored, it runs the majority of the day in high safety mode.   During the business day, this database receives only small delta updates.  In the early AM, however, it receives a very large number of writes during a batch load process that occurs only once daily.   We found that when running in synchronous mode, the batch job no longer runs for 7 or 8 minutes, it takes upwards to two hours to complete.


    For this reason, we've been testing switching the mode to high performance prior to the batch load, and then back again to high safety afterwards.  This has allowed the batch import to run in it's normal duration, and produces about 4gig of log file that generally takes about 1.5 hours to send to the mirror.


    Once the job ends, we set the mirroring back to full safety and the small delta process which runs every 5 minutes resumes.   Even though the large bulk transactions that occurred during the asynchronous settings is incomplete, mirroring seems to synchronously commit these small deltas after the job load.  The question is, how?


    To elaborate further, here's how we are thinking about this.


    If we consider what happened in the tran log on the principal, A represents the point where the bulk job begins, and mirroring is switched to asynchronous mode.   S represents the point in the log where the job ends, synchronous mode is resumed, and the ticks in the logs are transactions that result from this periodic delta process.


                     A                                                                           S

    Principal   _|_|_|_|_|_|_|_|_|_|_|_|_|_|_|_|_|_|_|_|_|_|_|_|_|_|_|_|_|_|_|_|_|_|_|_|_|_|_|_|_|_|_|_|

                     ^  bulk loading job                                                   ^  ^ delta transactions^


    On the tran log of the mirror, however, at the time the bulk job ends and mirroring is returned to synchronous mode, it's log would look like this:



                     A                                                                            S

    Mirror        _|_|_|_|_|_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _|_ _ _ _ _ _ _ _ _ _ _ _

                                 ^ transactions still in flight, not yet at mirror ^


    During the 1.5 hours it takes for mirroring to catch up, what exactly is happening to our delta transactions that occur after the bulk job ended?


                     A                                                                            S

    Mirror        _|_|_|_|_|_ _ _ _ _ _ _ _ _ _? _ _ _ _ _ _ _ _ _ _ _ _ _ _ |_|_|_|_|_|_|_|_|_|_|_|

                                 ^ transactions still in flight, not yet at mirror ^    ^delta trans sent synchronously



    How does the mirror database handle or make sense of transactions that the principal thinks have been committed synchronously when theres still a big gap in the tran log on the mirror?  Is there some mechanism that is allowing this to happen and still maintain data integrity on the mirror that we aren't understanding, or are we potentially flirting with disaster by attempting this toggling of the safety between full and off?


    I haven't been able to find any information that addresses how the technology works at this depth.  Any help or explanation is appreciated.






    Tuesday, September 30, 2008 6:13 PM

All replies

  • I like the ASCII diagrams!

    The simple answer is that while you've set the database to synchronous (high safety) mode, you won't actually be "safe" until both databases are synchronised.  This means that your transactions will not be committed at the mirror until the mirror has caught up.  You're essentially still in high-performance/asynchronous mode until you have synchronised.  Only then will the transactions start to be committed on both servers at the same time.

    Wednesday, October 1, 2008 4:06 AM