Adjust or tweak transactional replication within a high speed network environment

Question: Is there anyway we can adjust the transactional replication speed according to network capacity, like speedy replication with big data packets for fast networks and replication with smaller data packets for slow networks?

Answer: There isn't any such option is available within SQL Server to state. For all sorts of performance and with the Replication setup there are important factors you need to consider are:

• Server and network hardware,
• Database design,
• Distributor configuration,
• Publication design and options,
• Snapshot options & database maintenance.

After the replication is configured, it is recommend that you develop a performance baseline, which allows you to determine how replication behaves with a workload that is typical for your applications and topology. Using SYSMON (PERFORM) is a best option to get a benchmark of baseline for performance by collecting during low traffic and high traffic periods.

Also latency and throughput (hardware) are also most relevant to transactional replication, because systems built on transactional replication generally require low latency and high throughput. So having available resources with high-spec such as memory & disks would help a lot to attain the performance for high-end subsystem. This would also include the changes you are performing within the replication or to the tables that are involved in replication.

As in previous versions of SQL Server, changes made by the Merge Agent were performed on a row-by-row basis. In SQL Server 2005, changes are batched to improve performance; therefore, more than one row can be inserted, updated, or deleted within a single statement. If any published tables in the publication or subscription databases have triggers, ensure that the triggers can handle multi-row inserts, updates, and deletes. There is much information available from SQL 2005 BOL in this case to explain in and out of using triggers within replication.