Some tips for using Merge Replication in SQL Server 2016 (Part 2)
Avoid publishing unnecessary data.
Try to restrict the amount of published data. This can results in good performance
benefits, because SQL Server will publish only the amount of data required. This can
reduce network traffic and boost the overall replication performance.
Set the “Maximize Throughput for Network Applications” option.
This can increase SQL Server performance, because Windows will allocate more RAM to
SQL Server than to its file cache.
If you do not need continuous replication, do not configure the Merge Agent to run continuously.
Try to schedule the Merge Agent to run at regular intervals instead of running continuously.
Try to schedule it during CPU idle time and slow production periods. By using it, you can
decrease the total SQL Server overhead.
Specify the native synchronization mode of the initial synchronization of Subscribers
to the publication.
There are two synchronization modes: native and character. When the native synchronization
mode is used SQL Server produces native-mode bulk copy program output of all tables, when
the character synchronization mode is used SQL Server produces character-mode bulk copy
program output. You must use the character synchronization mode with non-SQL Server Subscribers.
Otherwise, use native synchronization mode because native mode provides better performance.
Consider specifying the processing order of merge articles.
SQL Server 2016 allows override the default order of article processing for merge
publications. You can specify the processing order of merge articles, if you define
referential integrity through triggers and those triggers must fire in a certain order.
Specify the -Validate parameter of the Merge Agent.
This parameter specifies the type of validation should be done at the end of the merge
session. There are four available values:
0 – No validation.
1 – Rowcount-only validation.
2 – Rowcount and checksum validation.
3 – Rowcount and binary checksum validation.
The default value of the -Validate parameter is 0. During the testing period you can use the
value of 3, but when you move your databases to production, consider setting the -Validate
parameter to 0.
If you need to replicate very large BLOB columns, specify a value of TRUE for
the @stream_blob_columns parameter of the sp_addmergearticle stored procedure.
Try to avoid the BLOB columns replication, whenever possible, but if it is necessary,
you can significantly improve merge replication performance by providing better memory
usage with the @stream_blob_columns parameter of the sp_addmergearticle stored procedure.
Avoid using join filters with five or more tables.
Because join filters with five or more tables can significantly degrade merge replication
performance, you should avoid join filter for the small lookup tables, or denormalize the
database design instead of using join filters with five or more tables.
Specify the “min server memory” option.
This option is used to set a minimum amount of memory allocated to SQL Server. You can also
set this option when SQL Server works on the same computer with other applications. In this
case, the “min server memory” option is used to allow SQL Server works when other applications
pretend to use all available memory.
Consider increasing the -DestThreads parameter of the Merge Agent.
This parameter specifies the number of destination threads that the Merge Agent uses to apply
changes at the Publisher during upload and the Subscriber during download. The default value of
this parameter is 4. By increasing this value you can boost the merge download/upload performance.
Note. Do not set this parameter too high, it can results in some performance degradation, because
SQL Server will have to spend extra time managing the extra threads.
Increase the -MaxBcpThreads parameter of the Merge Agent.
This parameter specifies the number of bulk copy operations that can be performed in parallel.
If you increase the value of the -MaxBcpThreads parameter, the bulk copy operations can run
faster, because they will be performed in parallel.
Note. Do not set this parameter too high, it can results in some performance degradation, because
SQL Server will have to spend extra time managing the extra threads.
Check the article-level statistics of the merge replication.
SQL Server 2016 supports the article-level statistics, which give detailed information about
the time a merge phase will take to complete, how much time was spent processing a given article,
and so on. You can check the article-level statistics in the Synchronization History window in
Replication Monitor.
Use Replication Management Objects (RMO) to configure, manage and script merge replication.
SQL Server 2016 supports RMO .NET library that allows developers to perform the common
replication tasks, such as configuring, managing, and scripting replication. Because
SQL-DMO does not support new features in SQL Server 2016, you should use RMO instead.
Consider increasing the -UploadGenerationsPerBatch parameter of the Merge Agent.
This parameter specifies the number of generations to be processed in a single batch while
uploading changes from the Subscriber to the Publisher. Consider increasing the value of the
-UploadGenerationsPerBatch parameter of the Merge Agent for systems that experience many
modified rows or frequent updates.
Include a publication database in AlwaysOn Availability Group.
In merge replication, a publication database can be part of an availability group.
In this case, the publisher instances must share a common distributor.
Note. In an AlwaysOn Availability Group an AlwaysOn secondary cannot be a publisher.
Consider increasing the -DownloadGenerationsPerBatch parameter of the Merge Agent.
This parameter specifies the number of generations to be processed in a single batch while
downloading changes from the Publisher to the Subscriber. Consider increasing the value of
the -DownloadGenerationsPerBatch parameter of the Merge Agent for systems that experience
many modified rows or frequent updates.
You can improve performance in applying the initial snapshot by compressing
the snapshot files.
If you have a central publisher with remote distributor topology (when the distribution
component of replication resides on its own dedicated server) and Publisher connected with
the Distributor over slow LAN or WAN, you can decrease network traffic by compressing the
snapshot files.
Consider increasing the -PollingInterval parameter of the Merge Agent.
This parameter indicates how often, in seconds, the Publisher or Subscriber is queried for
data changes. The default value of this parameter is 60 seconds. By increasing the value
of the -PollingInterval parameter you can reduce the total Publisher or Subscriber loading.
Place the published database log and distribution database log on the separate disk drives.
Because logging is more write-intensive, it is important that the disk arrays containing the
SQL Server log files have sufficient disk I/O performance.