Replication over a slow network can be a real problem. There are however some actions you can perform to improve replication performance over a slow network:
- Make sure the retention of the replication is low (usually 72 hours or less will be fine).- Decrease the value for the Distribution Agent parameter -CommitBatchSize. o CommitBatchSize is the number of transactions to be issued to the Subscriber before a COMMIT statement is issued. o The default value is 100. o You can decrease that up to a value of 1, if the network latency is really high.
- Increase the value for the Distribution Agent parameter –QueryTimeout. o QueryTimeout is the number of seconds before the query times out. o The default value is 300 seconds. o For high latency you could increase this to 3600 (1 hour), though this will clearly depend on your environment.
- Increase the value for the Distribution Agent parameter –LoginTimeOut. o LoginTimeOut is the number of seconds before the login times out. o The default value is 15 seconds.
- Also worth to verify that you are not affected by the Scalable Networking Package: o If you have installed Windows 2003 Service Pack 2, in either the publisher, distributor or subscriber, then also install this public hotfix: An update to turn off default SNP features is available for Windows Server 2003-based and Small Business Server 2003-based computers http://support.microsoft.com/kb/948496/en-us
Or make sure that your Network card drivers are compatible with the SNP.
In order to investigate latency in replication you may uise these articles:
Measuring Latency and Validating Connections for Transactional Replicationhttp://technet.microsoft.com/en-us/library/ms151178.aspx
How to: Quiesce a Replication Topology http://technet.microsoft.com/en-us/library/ms147385.aspx
Monitoring Performance with Replication Monitorhttp://technet.microsoft.com/en-us/library/ms152768.aspx
For more information you may review these links:
Considerations for All Types of Replicationhttp://technet.microsoft.com/en-us/library/ms152479.aspx
Transactional Replication Performance Tuning and Optimizationhttp://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/tranrepl.mspx
A Slow Network Is Causing Problemshttp://technet.microsoft.com/en-us/library/ms151858.aspx
Replication Distribution Agent Utilityhttp://msdn2.microsoft.com/en-us/library/aa224345.aspx
SQL Server 2005 - Replication Enhancementshttp://msdn2.microsoft.com/en-us/library/ms170959.aspx
Replication Scalability and Performance Enhancementshttp://msdn2.microsoft.com/en-us/library/ms170983.aspx