Publishing Stored Procedure Execution

If you include one or more stored procedures as articles in a snapshot or transactional publication, SQL Server 2000 can replicate the execution of the stored procedures rather than the data changes caused by the execution of those stored procedures. This is useful in replicating the results of maintenance-oriented stored procedures that may affect large amounts of data.

If replicated as a series of data manipulation language (DML) SQL statements, these procedures can require significant amounts of network resources, distribution database space, and server processing time. Replicating the changes as one stored procedure statement can greatly increase the efficiency of your application, but this feature should be used with care.

There are two different ways in which the execution of a stored procedure can be published:

• Procedure execution article. Replicates the procedure execution to all Subscribers of the article. This occurs regardless of whether individual statements in the stored procedure were successful. Furthermore, because changes made to data by the stored procedure can occur within multiple transactions, data at the Subscribers cannot be guaranteed to be consistent with data at the Publisher.
  
  
• Serializable procedure execution article. 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.

Procedure Execution Articles

If a stored procedure execution is replicated, no new data changes or procedure executions from the current connection are replicated until that stored procedure finishes executing. For example, if a stored procedure that modifies data in a published table is executed, and the procedure execution is replicated, the individual DML changes to the published table are not replicated.

Similarly, if a stored procedure that executes another published stored procedure is executed, and the execution is replicated, the EXEC statement of the stored procedure called by the first procedure is not replicated. However, if a published stored procedure modifies data within another database and the underlying table is replicated, those data changes are replicated as DML statements.

By default, the stored procedure definition at the Publisher is propagated to each Subscriber. However, you can also define the stored procedure logic to be different at a Subscriber. This is useful if you want different logic to be executed at the Publisher and Subscriber. For example, consider sp_big_delete, a stored procedure at the Publisher that has two functions: it deletes 1,000,000 rows from the replicated table big_table1 and updates the nonreplicated table big_table2. To reduce the demand on network resources, you should propagate the 1 million row delete as a stored procedure by publishing sp_big_delete and creating subscriptions at the Subscribers. At the Subscriber, you can define sp_big_delete to delete only the 1 million rows and not perform the subsequent update to big_table2.

Each time a published stored procedure is executed at the Publisher, the execution and the parameters passed to it for execution are forwarded to each Subscriber to the publication.

For example, if you execute a stored procedure that contains actions on several different tables, only the execution of that procedure (along with its parameters) is forwarded to each Subscriber. If you publish the underlying tables instead of the stored procedure, each data modification (insert, update, or delete) generated by the procedure is marked for replication and forwarded to each Subscriber. During the execution of a published stored procedure, SQL Server 2000 temporarily suspends marking transactions or commands for replication within that procedure to avoid duplication of effort.

Stored procedure replication both reduces the volume of commands requiring forwarding to Subscribers and increases the performance of your application by executing fewer dynamic SQL statements at each Subscriber.

For example, assume you created a stored procedure:

CREATE PROC give_raise AS
UPDATE EMPLOYEES SET salary = salary * 1.10

This procedure gives each of the 10,000 employees in your company a 10 percent pay increase. When you execute this stored procedure at the Publisher, it updates the salary for each employee. Without stored procedure replication, the update is sent to Subscribers as a large, multistep transaction:

BEGIN TRAN
UPDATE EMPLOYEES SET salary = salary * 1.10 WHERE PK = 'emp 1'
UPDATE EMPLOYEES SET salary = salary * 1.10 WHERE PK = 'emp 2'

And so on for 10,000 updates.

With stored procedure replication, SQL Server 2000 sends only the execution of the stored procedure:

EXEC give_raise

Important  Stored procedure replication is not appropriate to all applications. If an article is filtered horizontally, so that there are different sets of rows at the Publisher than at the Subscriber, executing the same stored procedure at both returns different results. Similarly, if an update is based on a subquery of another, nonreplicated table that has different values at both the Publisher and Subscriber, executing the same stored procedure at both returns different results.

To ensure that the same results are achieved at both the Publisher and Subscriber, the default behavior of SQL Server 2000 is to send the resultant data changes as a series of singleton statements in a transaction.

Serializable Procedure Execution Articles

The following example illustrates why it is recommended that you set up replication of procedures as serializable procedure articles.

BEGIN TRANSACTION T1
SELECT @var = max(col1) FROM tableA                   
UPDATE tableA SET col2 = <value> 
   WHERE col1 = @var 

BEGIN TRANSACTION T2
   WHERE col1 = @var                                         
INSERT tableA VALUES                                                                                                                                          
COMMIT TRANSACTION T2

In the previous example, it is assumed that the SELECT in transaction T1 happens before the INSERT in transaction T2.

If the procedure is not executed within a serializable transaction (for example, with isolation level set to SERIALIZABLE), transaction T2 will be allowed to insert a new row within the range of the SELECT statement in T1 and it will commit before T1. This also means that it will be applied at the Subscriber before T1. When T1 is applied at the Subscriber, the SELECT can potentially return a different value than at the Publisher and can result in a different outcome from the UPDATE.

If the procedure is executed within a serializable transaction, transaction T2 will not be allowed to insert within the range covered by the SELECT statement in T2. It will be blocked until T1 commits ensuring the same results at the Subscriber.

Locks will be held longer when you execute the procedure within a serializable transaction and may result in reduced concurrency.

To replicate a stored procedure when it is executed inside a serializable transaction, in the article properties for the stored procedure to be published, click the Other tab, and then select Only when it is executed inside a serializable transaction.

Using Transact-SQL system stored procedures, you can indicate that the stored procedure is to be replicated when it is executed inside a serializable transaction by setting the @type parameter of sp_addarticle to a value of serializable proc exec.