A Slow Network Is Causing Problems
Replication distributes data over network connections in the form of snapshots and incremental changes. Any issues that affect network performance, such as latency, throughput, and reliability, affect replication.
User Action
There are replication settings that can help mitigate network performance issues, but replication relies on the underlying network connections made by Microsoft Windows and the Microsoft SQL Server 2005 connectivity components. If the network connection is highly latent (such as a satellite link), consider altering the transmission control protocol (TCP) settings of the operating system at both the Subscriber and Publisher computers. For more information about Windows network parameters, see this Microsoft website.
In addition to setting network options:
- For transactional replication, consider setting a lower value for the Distribution Agent parameter -CommitBatchSize. A lower value can decrease throughput, but it can result in less failures over an unreliable network and a smaller number of transactions to rollback and reapply if a failure occurs. This parameter can be specified in an agent profile or on the agent command line.
- For merge replication, consider setting lower values for the following Merge Agent parameters: -DownloadGenerationsPerBatch and -UploadGenerationsPerBatch, -SrcThreads and -DestThreads. Lower values for -DownloadGenerationsPerBatch and -UploadGenerationsPerBatch can decrease throughput, but can result in a smaller number of retries over an unreliable network. These parameters are specified in the agent profile titled slow link and can also be specified on the agent command line. In addition to setting these parameters, consider using Web synchronization, which typically uses fewer network roundtrips. For more information, see Web Synchronization for Merge Replication.
If the network connection is highly latent but is reliable, consider using the default values for -DownloadGenerationsPerBatch and -UploadGenerationsPerBatch, and specifying higher values for -LoginTimeOut and -QueryTimeOut.
For more information about specifying replication agent parameters, see:
- How to: Work with Replication Agent Profiles (SQL Server Management Studio)
- How to: View and Modify Replication Agent Command Prompt Parameters (SQL Server Management Studio)
- How to: Work with Replication Agent Profiles (Replication Transact-SQL Programming)
- Programming Replication Agent Executables.