Some tips for using Merge Replication in SQL Server 2016 (Part 1)
Avoid replicating text, ntext and image columns.
These data types require more storage space and processing than other column data types.
Consider setting the -OutputVerboseLevel parameter of the Merge Agent to 0.
This parameter specifies whether the output should be verbose. There are three available values:
0 – only error messages are printed
1 – all of the progress report messages are printed
2 – all error messages and progress report messages are printed
The default value of the -OutputVerboseLevel parameter is 2. You can increase performance when
only error messages are printed. So, during the testing period you can use the default values
of this parameter, but when you move your databases to production, consider setting the
-OutputVerboseLevel parameter to 0.
Create an index on each of the columns that is used in the filter’s WHERE clause.
If you do not use indexes on columns used in filters the SQL Server must perform a table scan,
which can results in performance degradation.
Consider specifying that Subscribers can initiate snapshot generation during the
initial synchronization.
In SQL Server 2016 merge publications with parameterized filters can be difficult to manage
with hundreds or thousands of Subscribers. In this case, you can specify that Subscribers can
initiate snapshot generation during the initial synchronization.
Specify the “max server memory” option.
This option is used to set a maximum amount of memory allocated to SQL Server. You can specify
this option to avoid having the operating system page to disc for memory. You can also set this
option when SQL Server works on the same computer with other applications. In this case, the
“max server memory” option is used to prevent SQL Server to use all memory available.
Consider using the sp_redirect_publisher, sp_get_redirected_publisher,
sp_validate_redirected_publisher and sp_validate_replica_hosts_as_publishers
stored procedures.
These stored procedures provide replication support for AlwaysOn Availability Groups.
So, you can use them to get advantages from the AlwaysOn Availability Groups.
Use precomputed partitions for filtered merge publications.
When the precomputed partitions are used, then when a Subscriber synchronizes with the Publisher,
it can immediately start to download changes relevant to its partition without having to go
through the partition evaluation process. This can significantly improve merge replication
performance when a publication has a large number of changes, Subscribers, or articles in
the publication.
Consider using vertical and horizontal filtering to restrict the amount of data that need
to be replicated.
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.
Use static filters instead of dynamic filters, whenever possible.
Because SQL Server requires more overhead to process dynamic filters than static filters, you
should use static filters instead of dynamic filters, whenever possible.
Consider setting the -ParallelUploadDownload parameter of the Merge Agent to 1.
This parameter specifies whether the Merge Agent should process in parallel the changes uploaded
to the Publisher and those downloaded to the Subscriber. There are two available values of the
-ParallelUploadDownload parameter of the Merge Agent: 0 and 1. When the -ParallelUploadDownload
parameter is set to 1, then parallel processing is enabled, otherwise, the parallel processing
is disable.
Do not configure the distribution database to expand or shrink automatically.
Microsoft recommends setting a fixed size for the distribution database. Setting a database
to automatically grow results in some performance degradation, therefore you should set a
reasonable initial size of the distribution database.
Consider using the -UseInprocLoader parameter of the Merge Agent.
When this parameter is set the Merge Agent uses the BULK INSERT command when applying snapshot
files to the Subscriber. This improves the performance of the initial snapshot, but this parameter
is not compatible with the XML data type and cannot be used with character mode snapshots. If
you do not replicate XML data and use native bcp mode snapshots, consider using the
-UseInprocLoader parameter of the Merge Agent.
Place the distribution component of replication on its own dedicated server.
This topology is used for performance reasons when the level of replication activity increases
or the server resources become constrained. It reduces Publisher loading, but it increases
overall network traffic. This topology requires separate Microsoft SQL Server installations,
one for the Publisher and one for the Distributor.
Consider using the web synchronization for merge replication.
SQL Server 2016 supports the web synchronization for merge replication that provides the ability
to replicate data using the HTTPS protocol. You can use this feature if you need to synchronize
data from mobile users over the internet or synchronize data between Microsoft SQL Server
databases across a corporate firewall.
Try to use the varchar(max), nvarchar(max), varbinary(max) columns instead of the text,
ntext, or image columns.
By using so, you can improve the total replication performance because modifying and transferring
varchar(max), nvarchar(max), varbinary(max) columns much more efficient than text, ntext, and
image columns.
Consider locating both the publisher and the distributor on the same physical server.
You can place the publisher and the distributor on the same physical server when the publisher
server can perform the distribution tasks without any performance problems. By the way, using
a separate SQL Server for the Distributor requires separate SQL Server 2016 server license.
When adding merge filter, consider setting the join_unique_key parameter to 1.
If this parameter is set to 1 then the relationship between the child and parent tables in a join
filter is one to one or one to many. Keep in mind, that you should set this parameter to 1 only
if you have a constraint on the joining column that guarantees uniqueness. If the relationship
between the child and parent tables is many to many and you set the join_unique_key parameter
to 1, then non-convergence of data occurs.
Consider increasing the -SrcThreads parameter of the Merge Agent.
This parameter specifies the number of source threads that the Merge Agent uses to enumerate
changes from the Subscriber during upload and the Publisher during download. The default value
of this parameter is 3. 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.
Do not forgive to script your replication configuration.
Because scripting the replication configuration is a key part of any disaster recovery plan
for a replication topology, you should script your replication configuration from the replication
wizards or from the Replication folder in Microsoft SQL Server Management Studio.