Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Replication Settings
Various replication settings can speed up snapshot application; these options are particularly useful for speeding up large snapshots:
- MaxBcpThreads controls how many threads are dedicated to the bcp process. A good range for this setting is 5–7 on multiprocessor subscribers.
- UseInprocLoader uses the BULKINSERT TSQL command as opposed to the bcp process. On a multiprocessor system, this setting can result in a much faster snapshot application.
- DownloadGenerationsPerBatch and DownloadWriteChangesPerBatch are useful if changes are made to the publisher pool in the publisher after the snapshot is generated. For example, the snapshot may be generated at 12:00 noon and deployed at 9:00 p.m. During those nine hours, a lot of changes may have occurred on the publisher, and those changes have to be transferred to the subscriber as part of the initial snapshot. Regular synchronizations might run every 15 minutes, but this first sync will contain nine hours' worth of pooled changes. It might be the biggest sync your subscriber ever has to process. After the snapshot is applied, these commands are read from the publisher and applied on the subscriber. If more than one row from a table has to be applied on the subscriber, the merge replication process batches all of these changes into a single stored procedure call to apply these changes to the subscriber.
By default, 100 changes are applied at a time. You can increase the parameter settings to up to 1,000, allowing up to 1,000 changes to be applied to the subscriber tables. This setting can dramatically improve snapshot application times if a lot of changes have been applied on the publisher before the snapshot was applied on the subscriber.
One final note: One of the most common questions I face is how to enable the setting Optimize Synchronization, as illustrated in Figure 3.
)
Figure 3 Changing the setting to optimize synchronization in SQL Server.
This setting is for SQL 2000-compatible publications, and is no longer applicable for SQL 2005 and 2008 publications. It's designed for backward compatibility, so that you can still run SQL 2000 replication scripts on SQL 2005 servers without the scripts breaking.