locked
Mirroring - Performance issues with High safety synchronous mode with sql standard edition RRS feed

  • Question

  • Hi,

    In standard edition mirroring, when synchronous mode is only possible option:

    1: Does that mean every DML will be double commit and will take twice the time than no mirroring?

    2: how to gain/improve performance and avoid "DBM_ waits"

    Thanks.


    For quick review of new features, try virtual labs: http://msdn.microsoft.com/en-us/aa570323

    Wednesday, September 25, 2013 11:38 PM

Answers

All replies

  • 1. http://technet.microsoft.com/en-us/library/ms179344(v=sql.105).aspx/*

    Synchronous operation is maintained in the following manner:

    1. On receiving a transaction from a client, the principal server writes the log for the transaction to the transaction log.

    2. The principal server writes the transaction to the database and, concurrently, sends the log record to the mirror server. The principal server waits for an acknowledgement from the mirror server before confirming either of the following to the client: a transaction commit or a rollback.

    3. The mirror server hardens the log to disk and returns an acknowledgement to the principal server.

    4. On receiving the acknowledgement from the mirror server, the principal server sends a confirmation message to the client.

    */

    2. http://technet.microsoft.com/en-us/library/cc917681.aspx


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, September 26, 2013 6:46 AM
  • Hi,

    In standard edition mirroring, when synchronous mode is only possible option:

    1: Does that mean every DML will be double commit and will take twice the time than no mirroring?



    hardening happens in parallel so its not exactly twice.

    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter
    Author: SQL Server 2012 AlwaysOn - Paperback, Kindle

    Thursday, September 26, 2013 6:48 AM
  • Hi,

    In standard edition mirroring, when synchronous mode is only possible option:

    1: Does that mean every DML will be double commit and will take twice the time than no mirroring?



    hardening happens in parallel so its not exactly twice.

    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter
    Author: SQL Server 2012 AlwaysOn - Paperback, Kindle


    Hello Balmukund,

    IMHO in synchronous commit ,hardening is not always parallell transaction log is written to disk and at same time sent to mirror server and then principal waits conformation from  mirror that record is hardened.After recieving confirmation and gives COMMIT while mirror continues its REDO operation.


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Thursday, September 26, 2013 7:40 AM