In addition to allowing you to replicate the data in tables, Microsoft® SQL Server™ allows you to replicate stored procedures in one of two ways. If you include one or more stored procedures as articles in a snapshot publication, SQL Server replicates the entire stored procedure from the Publisher to the Subscriber. If you include one or more stored procedures as articles in a transactional publication, SQL Server replicates the execution of stored procedures rather than the data changes caused by the execution of those stored procedures. This is especially 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 a single EXEC statement greatly increases the efficiency of your application.
There are two types of procedure execution articles: those for procedure executions and those for serializable procedure executions. Although the two types of articles are similar, the distinction between the two is very important.
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 may occur within multiple transactions, data at the Subscribers cannot be guaranteed to be consistent with data at the Publisher.
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 guarantees that data at the Subscriber is consistent with data at the Publisher.
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 execution. 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 which 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 just delete the 1 million rows and not do the subsequent update to big_table2.
Each time a published stored procedure is executed at the Publisher, its execution and the parameters passed to it for execution get 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 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 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 1,000 such singleton updates.
With stored procedure replication, SQL Server sends just the execution of the stored procedure:
EXEC give_raise
Important Stored procedure replication must be used with care and is not appropriate to all applications. If an article is partitioned horizontally so that there are different sets of rows at the Publisher than at the Subscriber, then executing the same stored procedure at both yields 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, then executing the same stored procedure at both yields different results.
To ensure that the same results are achieved at both the Publisher and Subscriber, the default behavior of SQL Server is to send the resultant data changes as a series of singleton statements in a transaction. However, if you have a good working knowledge of your data and stored procedures, you can safely replicate the execution of stored procedures.
| Defining an Article |