The pending table allows clients at a remote site to update central data and see the updates at the remote site before they are returned from the central site. Use this model to implement local update applications.
In this strategy, a client application at a remote site executes a user stored procedure that updates data at the central site using a request function. Changes to the central data are replicated to the remote site via an applied function. A local pending table lets clients at the remote site see pending updates at the remote site before the replication system returns the updates.
When the update succeeds at the central database, it is distributed to remote sites, including the site where the transaction originated. At the remote site, a stored procedure updates the replicated table and deletes the corresponding updates from the pending table.
To use applied functions, request functions, and a local pending table, you must complete these tasks.
At the Remote Site:
-
Create a pending table in the remote database. Grant appropriate permissions.
-
Create a user stored procedure in the remote database that initiates the request function and inserts data updates into the pending table.
-
Mark the user stored procedure for replicated function delivery using sp_setrepproc.
-
Grant procedure permissions to the appropriate user.
-
Create a user stored procedure in the remote database that updates the remote table and deletes the corresponding update from the pending table. Grant appropriate permissions to the maintenance user.
-
Create the request function replication definition for the request function.
-
Create a subscription to the applied function replication definition created at the central site.
At the Primary Site:
In this example, a client application at the remote (Sydney) site executes a stored procedure upd_publishers_pubs2_req, which inserts values in the publishers_pend table and causes an associated stored procedure, upd_publishers_pubs2, to execute at the central (Tokyo) site. Execution of upd_publishers_pubs2 at the central site causes the stored procedure upd_publishers_pubs to execute at the remote site, which updates the publishers table and deletes the corresponding information from the publishers_pend table.
This figure illustrates the data flow when you use applied functions, request functions, and a local pending table. The gray arrows show the flow of the request function delivery. The black arrows show the flow of the applied function delivery.
