SQL Server Performance

Mirroring performance

Discussion in 'SQL Server 2005 Database Mirroring' started by hugoti, Nov 4, 2007.

  1. hugoti New Member

    I have a SQL 2005 cluster running Standard edition that has been configured to mirror to a single 2005 Std edition server at seperate physical location. It has been configured for High Safety without automatic failover. My main concern is the peformance of the "current send rate" of the principal log to the mirror log. Is it running around 5-10KB/sec which is very slow. Our current rate of new transaction is less than that so that is not an issue however sometimes when I do database maintenance, the log can get quite big and hence it will take a long time (sometimes a whole day!) to synchronise the logs. Is there a way I can increase the "current send rate" or is it limited by the bandwidth. I have a 4Mb DSL WAN link between the 2 sites so I suspect the mirroring is "throttled" somewhere. Please advise. Thanks.
  2. techbabu303 New Member

    Hi ,
    If by high safety you mean "HIGH PROTECTION OPERATION MODE" mode, the distance between the principal and mirror database increases , there is performance impact. Alos most tips do not recommend using this mode.
    In case you servers are geographically dispersed then "High Performance Operation Mode" is recommended but this increases latency.
    Please check Staya,s blon on mirroring performance in www.sqlserver-qa.net
    Regards
    Sat
  3. hugoti New Member

    The nature of our business requires that we do not have any loss of data (it contains financial data) and thus we need to minimise any data loss in the event of failover. Therefore we have configured it as High Safety without automatic failover (synchronous). We have a procedure in plan for manual failover which is effective for us. My main concern is that while it was able to keep up with the realtime transactions of our customers - the maintenance tasks generated huge transaction logs which could takes days to replicate. I realised the index rebuild task did this and have since turn it off. I'm backing up the transaction logs on a nightly basis as part of daily backups but I doubt this will solve our problem.
    My question is considering the 2 servers are in 2 seperate physical locations, what is the recommend mirroring model given the above information? Our setup seems to take forever to replicate the huge logs (about 10Kb/sec on a 4Mb link which doesn't seem right to me - already checked the link and it's fine). Please advise.
  4. satya Moderator

    As I can understand the transactions are very high in your case, have you tested using HIGH PERFORMANCE mode. Quoting from BOL notes:
    the database mirroring session operates asynchronously. Asynchronous operation supports only one operating mode—high-performance mode. This mode enhances performance at the expense of high availability. High-performance mode uses just the principal server and the mirror server. Problems on the mirror server never impact the principal server. On the loss of the principal server, the mirror database is marked DISCONNECTED but is available as a warm standby.
    In high-performance mode, as soon as the principal server sends the log for a transaction to the mirror server, the principal server sends a confirmation to the client, without waiting for an acknowledgement from the mirror server. Transactions commit without waiting for the mirror server to write the log to disk. Asynchronous operation permits the principal server to run with minimum transaction latency.
    High-performance mode can be useful in a disaster-recovery scenario in which the principal and mirror servers are separated by a significant distance and where you do not want small errors to impact the principal server.
    Have you checked my presentation http://sqlserver-qa.net/blogs/bc/ar...rroring-by-ssqa-in-sql-server-open-world.aspx
  5. satya Moderator

    How frequently you are performing backup of transaction log?

Share This Page