Some tips for using Transactional Replication in SQL Server 2016 (Part 1)
Try to keep transactions as short as possible.
Because SQL Server send changes from the Publisher to Subscriber as INSERT, UPDATE,
and DELETE statements you should keep transactions as short as possible to help the
Distribution Agent to transfer transactions through the network. By the way, keeping
transactions as short as possible can be used to prevent deadlocks also.
Consider using transactional replication to memory-optimized tables.
In SQL Server 2016, tables acting as transactional replication subscribers, excluding
peer-to-peer transactional replication, can be configured as memory-optimized tables.
To configure the subscriber database for supporting replication to memory-optimized
tables, you should set the @memory_optimized property to true by using sp_addsubscription
or sp_changesubscription system stored procedure.
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 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.
You can modify the call formats for transactional articles without the reinitialize
of subscriptions.
SQL Server 2016 allows modify the parameters of the stored procedures that used to
propagate changes to Subscribers without the reinitialize of subscriptions.
Consider using peer-to-peer transactional replication.
In comparison with the standard transactional replication that assumes read-only
Subscribers and is hierarchical in structure, in the peer-to-peer transactional
replication all nodes are peers and changes can be made at all nodes.
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.
Consider increasing the -CommitBatchSize parameter of the Distribution Agent.
This parameter specifies the number of transactions to be issued to the Subscriber
before a COMMIT statement is issued. The default value is 100.
Avoid replicating text, ntext and image columns.
These data types require more storage space and processing than other column data types.
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 -ReadBatchSize parameter of the Log Reader Agent.
This parameter specifies the maximum number of transactions read out of the transaction
log of the publishing database. The default value is 500. This option should be used when
a large number of transactions are written to a publishing database, but only a small
subset of those are marked for replication.
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 specifying the -PollingInterval parameter of the Log Reader Agent.
This parameter specifies how often the transaction log of a published database is queried
for transactions to replicate. Decreasing the value of the -PollingInterval parameter
results in lower latency for the delivery of transactions from the publication database
to the distribution database, but increases load on the server from polling more frequently.
Increasing the value of the -PollingInterval parameter results in higher latency for the
delivery of transactions, but decreases load on the server. The default value of the
-PollingInterval parameter is 5 seconds.
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 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.
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.