Applied stored procedures

Replicated stored procedures that Replication Server delivers from a primary database to a replicate database are called applied stored procedures.

You use applied stored procedure delivery to replicate transactions first performed on primary data to replicate databases. Data changes are applied at a primary database and then distributed at a later time to replicate databases that subscribe to replication definitions for the data. Replication Server executes the replicated stored procedure in the replicate database as the maintenance user, which is consistent with normal data replication.

You can use applied stored procedures to realize important performance benefits. For example, if your organization has a large amount of row changes, you can create an applied stored procedure which changes many rows, rather than replicating the rows individually. You can also use applied stored procedures to replicate data set changes which are difficult to express using normal subscriptions. Refer to the Replication Server Design Guide for more information.

You set up applied stored procedures by making the first statement in the stored procedure update a table. You must also make sure that the destination databases have subscriptions to the before and after images of that updated row. The applied stored procedure must update only one row in a replicated table. Replication Server uses the first row updated by the stored procedure to determine where to send the user-defined function for the procedure.