Replicated stored procedures that Replication Server delivers from a replicate database to a primary database are called request stored procedures. You use a request stored procedure to deliver a transaction from a replicate database back to the primary database.
For example, a client application at a remote location may need to make changes to primary data. In this case, the application at the remote location executes a request stored procedure locally to change the primary data. Replication Server delivers this request stored procedure to the primary database by executing, in the replicate database, a stored procedure that has the same name as the stored procedure in the primary database. The stored procedure in the primary database updates the primary data that the transaction changes.
Replication Server executes the replicated stored procedure in the primary database as the user who executed the stored procedure in the replicate database. This ensures that only authorized users may change primary data.
In an application, Replication Server may replicate some or all of the data that is changed in the primary database. The changes are propagated to replicate databases managed by Replication Servers with subscriptions for the related data, either as data rows (insert, delete, or update operation) or as stored procedures. Using this mechanism, the effect of a transaction quickly arrives at both the primary and replicate databases.
WARNING! Do not execute a request stored procedure in a primary database. This can lead to looping behavior, in which replicate Replication Servers cause the same procedure to execute in the primary database.
Using request stored procedures ensures that all updates are made at the primary database, preserving the Replication Server basic primary copy data model while keeping the replication system invulnerable to network failures and excess traffic. Even when there is primary database failure, or network failure from the replicate database to the primary database, Replication Server remains fault tolerant. It queues any undelivered request stored procedure invocations until the failed components come back online. When the components are again in service, Replication Server completes delivery.
By using the Replication Server guaranteed request stored procedure delivery feature, you can obtain all the benefits of having a single, definitive copy of your data that includes all the latest changes. At the same time, Replication Server provides the availability and performance benefits of de-coupling applications at replicate databases from the primary database.
Refer to the Replication Server Design Guide for more information on replication system design issues relating to asynchronous procedure delivery.
