- Code Module Replication Setup
- Replicating Schema Changes
- Replicating Indexed Views as Tables
- Replicating Execution of Stored Procedures
- Replicating Execution of Stored Procedures within a Serializable Transaction
- Summary
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Replicating Execution of Stored Procedures
You can replicate the execution of stored procedures, which is very useful if you want to make bulk changes to the existing data and if you’re certain that data values on the publisher and subscriber are identical. What happens if your UPDATE statement affects 1,000 rows of a replicated table? By default, SQL Server translates a single UPDATE command into 1,000 executions of the replication stored procedure. This works well because each call of the replication stored procedure affects a single row, and you don’t experience many locking/contention issues on the subscriber.
But what if you have a stored procedure that makes batch updates to millions of rows in multiple tables? Your distribution database will grow exponentially, and replication latency could become unacceptable. SQL Server reads replicated commands from the msrepl_commands table in the distribution database before delivering these commands to the subscriber; the distribution cleanup job reads the same table and deletes any transactions that have already been delivered. If msrepl_commands contains many millions of rows, reading and deleting data from this table will become very slow. In addition, replicating bulk changes using table articles will place a considerable burden on your network.
Replicating the execution of stored procedures provides a viable alternative because it executes the same stored procedure on the publisher and subscriber, thereby minimizing the network traffic and number of commands in the distribution database. If I need to make bulk changes to 50 million rows and I know that publisher and subscriber(s) have the same data, it would be more efficient to replicate the execution of a stored procedure that implements these changes.
Another alternative for replicating bulk changes to a single table is to replicate the table by executing the same UPDATE statement on publisher and subscriber. (Refer to the first article in this series to learn how.) Replicating stored procedure execution is still a better choice, however, if you’re making bulk changes to multiple tables.
For example, suppose that I have a stored procedure that updates certain rows in the factFinance table in the AdventureWorksDW sample database based on the supplied parameters, like this:
CREATE PROC update_factFinance ( @PercentChange NUMERIC (3,2), @OrganizationKey TINYINT, @TimeKey INT) AS /* Change the amount for given organization key */ UPDATE factFinance SET amount = amount * @PercentChange WHERE OrganizationKey = @OrganizationKey AND TimeKey = @TimeKey
I’ll create a publication that replicates the execution of this stored procedure. Every time I execute this procedure on the publisher, the Distribution agent will deliver a command similar to the following to the subscriber:
{call "dbo"."update_factFinance " (1.10, 3, 32)}
Keep in mind that replication will simply deliver this command; replication won’t check whether the command affects the same rows on the publisher and subscriber. So to maintain data consistency on publisher and subscriber, you need to ensure that the same data values exist on both servers prior to executing the replicated stored procedure.