SQL Server Performance

Typical latency in transactional replication

Discussion in 'SQL Server 2005 Replication' started by ddoyle, Jul 31, 2007.

  1. ddoyle New Member

    Hi,
    I have been working on a project to evaluate transactional replication in SQL Server 2005 to find out if it will be suitable to provide real time reporting without impacting the primary database server. Thus far I have not seen latency better than several seconds replicating a change to a table or by utilizing the "tracer tokens" troubleshooting functionality. It usually takes a couple seconds from the publisher to the distributor and a couple more from the distributor to the subscriber. Is this to be expected or have I configured something improperly? Thanks!
  2. satya Moderator

    Welcome to the forum!
    The latency should be elapsed time between a transaction being committed at the Publisher and the corresponding transaction being committed at the Subscriber. if this is in seconds then the network between publisher and subscriber is good to go.
    How big is a transaction within your application in this replication?
    have you tested for any larger transactions in this case?
    Further information on Latency and MS151178 fyi.
  3. ddoyle New Member

    Hi Satya,
    Thanks for the timely reply. I guess my question is actually whether it is possible to achieve sub-second (milliseconds) latency for transactional replication for two servers on the same subnet sending just a single "tracer token" transaction through. I currently have it configured with the publisher on one server and the distributor/subscriber on the second. Thanks!
    Dan
  4. satya Moderator

    I believe that is dependant on the bandwidth of network between these 2 servers, I'm not aware of any special swtich to perform such tweak.
  5. ddoyle New Member

    Thanks again for getting back. The servers are on the same subnet with a 100mbps link and less than 1ms latency on ping. I guess my question is more general though and not for this specific configuration. Is it possible under any circumstances to achieve subsecond latency? Do you have any recommendations for configuration of the agents or the topology of the PDS model to achieve this? Thanks in advance for any information you may have.
  6. ndinakar Member

    I would say 4-5 seconds should be expected even for the best hardware around. Also, I dont think there is any solution with that kind of sub-second latency, realistically.
  7. satya Moderator

    Achieving the performance is purely based upon the transaction size (batch) in any case, also having best hardware resources would help a bit. But as explained not seen such information of achieving milliseconds performance. You might talk to your local MS support for any offerings.
  8. ddoyle New Member

    Thanks to you both, you were very helpful
  9. MohammedU New Member

    I have not seen sub-seconds latency any where...It is hard to achieve becuase it depends on many factors...unless you are replicating on the same server to different database...
    If your latency is 1-5 seconds then I can say it is very good...

Share This Page