ReplTip – Bulk Updates with Transactional Replication

ReplTip – Bulk Updates with Transactional Replication
by Taiyeb Zakir

We’re seeing several customers performing bulk updates on the Publisher. Consider for example a table with 10 rows and 2 columns C1 and C2 and we run this update.

Update table set C1 = “Something”

This updated 10 rows as one set, but when Log Reader writes to the Distribution database it will be written as 10 individual updates. Now lets say you update a million rows, the Log Reader again writes 1 million entries in the Distribution Database. This will take a long time for the Distribution Agent to apply on the Subscriber. To avoid writing that many entries to the Distribution database you can update the table using a stored procedure and replicate execution of the stored procedure. Now Log Reader will just replicate “Exec MyUpdateProc” statement to run on the Subscriber instead of the individual update statements. This will greatly reduce the size of the Distribution database and will also reduce the latency.

Check this article for more details: http://msdn.microsoft.com/en-us/library/ms152754.aspx .