SQL Server

Some tips for using Transactional Replication in SQL Server 2016 (Part 2)

Some tips for using Transactional Replication in SQL Server 2016 (Part 2)

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.

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.

Include a publication database in AlwaysOn Availability Group.
In transactional 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 using the SWITCH PARTITION clause of the ALTER TABLE statement to move
data between partitions.

In SQL Server 2016, transactional replication allows you to execute SWITCH PARTITION
clause of the ALTER TABLE statement on the publication database.

Replicate the execution of stored procedures when a large number of rows are affected.

For example, instead of replicating a very large number of INSERT, UPDATE, and DELETE
statements, you can create stored procedure, which will contain all these statements,
and replicate to subscriber only the execution of this stored procedure. This can reduce
network traffic and boost the overall replication performance.

Consider initializing a transactional subscription from a backup.
SQL Server 2016 allows any backup taken after the creation of a transactional publication
to be restored at the Subscriber instead of using a snapshot to initialize the subscription.
Initializing a transactional subscription from a backup can be useful when the published
database contains large volumes of data. In this case, initializing a transactional
subscription from a backup can result in good performance benefits.

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 specifying the -PollingInterval parameter of the Distribution Agent.
This parameter specifies how often, in seconds, the distribution database is queried
for replicated transactions. Decreasing the value of the -PollingInterval parameter
results in lower latency for the delivery of transactions from the distribution database
to the subscription 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.

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 transactional replication to Azure SQL Database.
SQL Server 2016 introduces supporting snapshot and one-way transactional replication
to Azure SQL Database, peer-to-peer transactional replication and merge replication are
not supported.

You can minimize the performance affect of history logging by selecting 1 for the
-HistoryVerboseLevel parameter of the Distribution Agent and Log Reader Agent.

This property specifies the amount of history logged during distribution operation (for the
Distribution Agent) or during a log reader operation (for the Log Reader Agent).

Consider increasing the -CommitBatchThreshold parameter of the Distribution Agent.
This parameter specifies the number of replication commands to be issued to the
Subscriber before a COMMIT statement is issued. The default value is 1000.

If you do not need continuous replication, avoid configure the Distribution Agent
to run continuously.
Try to schedule the Distribution 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.

Consider specifying the -MaxCmdsInTran parameter of the Log Reader Agent.
This parameter specifies the maximum number of statements that Log Reader Agent
will group into a transaction before writing to the distribution database. For example,
using this parameter allows the Log Reader Agent to divide large transactions into
several smaller transactions. Specifying the -MaxCmdsInTran parameter for the
Log Reader Agent can reduce contention at the Distributor and reduce latency between
the Publisher and Subscriber.

Increase the -MaxBcpThreads parameter of the Distribution 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. Increase
this parameter to 2 and continue monitoring.

Consider using the -UseInprocLoader parameter of the Distribution Agent.

When this parameter is set the Distribution 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 Distribution Agent.