locked
Hw the transactions are transfered RRS feed

  • Question

  • Hi,

    Can some one help me with the following in sql  mirroring?

    As per

    http://msdn.microsoft.com/en-IN/library/ms189852.aspx

    Unlike replication, which works at the logical level, database mirroring works at the level of the physical log record.

    How the transaction is actually moved to Mirror instance and commited at both Mirror and Pricipal Server(In High Safety Mode) . What is role of trasaction log file here? Is it read some where like log shipping.

    Thanks

    Aslam

    Tuesday, August 26, 2014 11:22 AM

Answers

  • Hi,

    I have not found better article than below about database mirroring you queries are in below heading. Search for below heading in link

    Table 9: A Safety FULL (Synchronous transfer) example sequence of events.

    http://technet.microsoft.com/en-gb/library/cc917680.aspx


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Articles

    • Marked as answer by Raayena Wednesday, August 27, 2014 6:01 AM
    Tuesday, August 26, 2014 12:06 PM
  • I really do wish they would update that page. Granted it's attempting to put a simple explanation on something a little more complex.

    How the transaction is actually moved to Mirror instance and commited at both Mirror and Pricipal Server(In High Safety Mode) .

    That's an interesting and good question. There are many things that are going on in the background and many other things that most wouldn't think about when dealing with the log operations.

    First, let me specify that a log record is an individual unit or item. Log blocks are made up of log records which could (and most likely will on a busy system) have intermingled records from other transactions. Log blocks stop on certain boundaries, one of which is a commit and another is a specific size of the log block. When something such as a commit happens, the log block is ended and some information is written.

    Mirroring works by sending log blocks (even though the article says log records) to the mirror server from the principal. When the log block is completed and flushed from the memory buffer a few different things happen, one of which is noticing that the database is involved in mirroring and needs to have the log block sent to the mirror. Glossing over some other items, the mirror server receives the log block and sends its reply.

    What is role of trasaction log file here?

    The same, even if mirroring weren't involved. The difference happens around (I don't have source access so this is my best guess) the log buffer flush to disk. The transaction log is not "re-read" as it is in replication.

    Is it read some where like log shipping.

    Mostly No. There are situations where a negotiation between the principal and the mirror could result in some of the already hardened log blocks on the principal need to be sent to a mirror. For example, bringing a new mirror online while there is still activity in the principal database, but these are more the exception than the rule. In general, no it's not read like log shipping (which requires a TLog backup to be sent) but it does act as a sort of "continuous" log shipping though through different mechanisms.

    I hope this helps.


    Sean Gallardy | Blog | Microsoft Certified Master

    • Marked as answer by Raayena Wednesday, August 27, 2014 6:01 AM
    Tuesday, August 26, 2014 1:29 PM
    Answerer

All replies

  • Tuesday, August 26, 2014 11:27 AM
  • Thanks .

    I am going through it . Hopefully I will find something on logical and physical level as mentioned in my comment.

    "which works at the logical level, database mirroring works at the level of the physical log record."

    Tuesday, August 26, 2014 11:34 AM
  • Hi Mohammad, Confusing at first galnce, but later looking at community additions, I foudn the following. And I am sure Sean would pitch in here: But you can look at the community additions:

    "Redoing is accomplished by sending a stream of active transaction log records to the mirror server, which applies log records to the mirror database, in sequence, as quickly as possible. Unlike replication, which works at the logical level, database mirroring works at the level of the physical log record."

    This is incorrect as mirroring sends Log Blocks (which are made up of log records) and not individual Log Records.

    "Database mirroring involves redoing every insert, update, and delete operation that occurs on the principal database onto the mirror database as quickly as possible.

    Mirroring also sends more than just Insert, Update, and Delete records as DDL information, etc, is also sent.


    • Edited by SQLZealots Tuesday, August 26, 2014 11:52 AM
    Tuesday, August 26, 2014 11:50 AM
  • Hi,

    I have not found better article than below about database mirroring you queries are in below heading. Search for below heading in link

    Table 9: A Safety FULL (Synchronous transfer) example sequence of events.

    http://technet.microsoft.com/en-gb/library/cc917680.aspx


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Articles

    • Marked as answer by Raayena Wednesday, August 27, 2014 6:01 AM
    Tuesday, August 26, 2014 12:06 PM
  • Thanks Shanky_621.

    I will read this and see if that helps me.

    Thanks a lot.

    Tuesday, August 26, 2014 12:57 PM
  • I hope I understand your question right:

    Tu put it simple, in replication you define publications which consist of articles. An article is a table or a subset of a table. The log reader then collects transaction data concerning these articles from the transaction log an puts them into the distribution database for later distribution to the subscribers.

    When you establish database mirroring, the entire database will be mirrored. Transactions from the principal are sent directly to the endpoint of the mirror server. If the mirror is not available, transactions will be kept in the principals transaction log until the mirror is back online.


    Bodo Michael Danitz - MCT, MCITP - free consultant - performance specialist - www.sql-server.de

    Tuesday, August 26, 2014 1:11 PM
  • I really do wish they would update that page. Granted it's attempting to put a simple explanation on something a little more complex.

    How the transaction is actually moved to Mirror instance and commited at both Mirror and Pricipal Server(In High Safety Mode) .

    That's an interesting and good question. There are many things that are going on in the background and many other things that most wouldn't think about when dealing with the log operations.

    First, let me specify that a log record is an individual unit or item. Log blocks are made up of log records which could (and most likely will on a busy system) have intermingled records from other transactions. Log blocks stop on certain boundaries, one of which is a commit and another is a specific size of the log block. When something such as a commit happens, the log block is ended and some information is written.

    Mirroring works by sending log blocks (even though the article says log records) to the mirror server from the principal. When the log block is completed and flushed from the memory buffer a few different things happen, one of which is noticing that the database is involved in mirroring and needs to have the log block sent to the mirror. Glossing over some other items, the mirror server receives the log block and sends its reply.

    What is role of trasaction log file here?

    The same, even if mirroring weren't involved. The difference happens around (I don't have source access so this is my best guess) the log buffer flush to disk. The transaction log is not "re-read" as it is in replication.

    Is it read some where like log shipping.

    Mostly No. There are situations where a negotiation between the principal and the mirror could result in some of the already hardened log blocks on the principal need to be sent to a mirror. For example, bringing a new mirror online while there is still activity in the principal database, but these are more the exception than the rule. In general, no it's not read like log shipping (which requires a TLog backup to be sent) but it does act as a sort of "continuous" log shipping though through different mechanisms.

    I hope this helps.


    Sean Gallardy | Blog | Microsoft Certified Master

    • Marked as answer by Raayena Wednesday, August 27, 2014 6:01 AM
    Tuesday, August 26, 2014 1:29 PM
    Answerer
  • Thanks Sean.

    Your comments and link from Shanky_621 clears my queries.

    Wednesday, August 27, 2014 6:27 AM