ARTICLES

After setting up transactional replication, SQL Server captures all changes that were made
in the articles and stores INSERT, UPDATE, and DELETE statements in the distribution database.
These changes then sent to subscribers from the distribution database and applied in the same
order. Transactional replication is best used when the replicated data changes frequently or
when the size of replicated data is large and is not necessary to support autonomous changes
the replicated data on the Publisher and on the Subscriber. In this article, you can find some
tips to boost the SQL Server 2005 transactional replication performance.

Consider initializing a transactional subscription from a backup.
SQL Server 2005 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.

You can modify the call formats for transactional articles without the
reinitialize of subscriptions.
Now SQL Server 2005 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.
One of the new SQL Server 2005 replication features is a 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.

Use Replication Management Objects (RMO) to configure, manage and script
transactional replication.
SQL Server 2005 supports new 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 2005, you should use RMO instead.

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.

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 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.

If you need continuous replication, run replication agents continuously
instead of on very frequent schedules.
Setting the replication agents to run continuously improves replication performance, because
the agent does not have to start and stop.

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 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.

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.

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 setting the -OutputVerboseLevel parameter of the Distribution 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 is 2 for the Distribution Agent. 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.

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.