When publishing the EXECUTION of a Stored Procedure wrap in Transaction
Microsoft SQL Server Escalation Services
When Replication the EXECUTION of a stored procedure to a subscriber select the “serialized transaction” and and wrap the SP execution within a transactions.
This ensures the changes are replicated as a single stored procedure execution and not as individual row-level updates.
“The serializable option is recommended because it replicates the procedure execution only if the procedure is executed within the context of a serializable transaction. If the stored procedure is executed from outside a serializable transaction, changes to data in published tables are replicated as a series of DML statements. This behavior contributes to making data at the Subscriber consistent with data at the Publisher. This is especially useful for batch operations, such as large cleanup operations.”
For example, let’s say we’re running a script to call stored procedure [sp_fixaccount] passing an [accountnum] and the stored procedure is expected to update 26,601 rows for each account. Our objective is to execute the CALLING of the stored procedure on both the Publisher and the Subscriber, not the 26,601 updates.
To accomplish this wrap the [sp_fixaccount] in a transaction.
BEGIN loop logic BEGIN TRAN EXEC sp_fixaccount [accountnum] COMMIT TRAN END loop logic
Using Replication Monitor you’ll see a single TRANSACTION (4:12:17 PM) being replicated instead of the previous 26,601 individual row updates (4:09:56 PM). This will significantly reduce the Transactional Replication overhead.