The pending table is a design enhancement of applied and request functions that 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 updates that are pending at the remote site before the replication system returns the updates.
When a client application executes the user stored procedure at the remote data server, it:
Causes an associated stored procedure to execute and update data at the primary site
Enters those updates in the local pending table
When the update succeeds at the central database, it is distributed to the 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.
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.
Create the stored procedure that modifies the central data.
Create the applied function replication definition for the applied function.
Create a subscription to the request function replication definition.
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.
Figure 3-12 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.
Figure 3-12: Request functions and a local pending table
This script creates a pending table in the remote database.
-- Execute this script at Sydney data server -- Creates local pending table create table publishers_pend (pub_id char(4) not null, pub_name varchar(40) null, city varchar(20) null, statechar(2) null) go /* end of script */
The script creates the stored procedure upd_publisher_pubs2 at the central (Tokyo) site:
-- Execute this script at Tokoyo data server -- Creates stored procedure create procedure upd_publishers_pubs2 (@pub_id char(4), @pub_name varchar(40), @city varchar(20), @state char(2)) as insert into publishers values (@pub_id, @pub_name, @city, @state) go /* end of script */
The following script creates the upd_publishers_pub2_req stored procedure at the remote (Sydney) site. The insert into clause inserts values into the publishers_pend table.
-- Execute this script at Sydney data server -- Creates stored procedure create procedure upd_publishers_pubs2_req (@pub_id char(4), @pub_name varchar(40), @city varchar(20), @state char(2)) as insert into publishers_pend values (@pub_id, @pub_name, @city, @state) go /* end of script */
This script creates the upd_publishers_pubs2 procedure for the remote (Sydney) site. It updates the publishers table and deletes the corresponding information from the publishers_pend table.
-- Execute this script at Sydney data server -- Creates stored procedure upd_publishers_pubs2 create procedure upd_publishers_pubs2 (@pub_id char(4), @pub_name varchar(40), @city varchar(20), @state char(2)) as update publishers set pub_name = @pub_name, city = @city, state = @state where pub_id = @pub_id delete from publishers_pend where pub_id = @pub_id go /* end of script */
This script creates the applied function replication definition at the central (Tokyo) Replication Server:
-- Execute this script at Tokyo Replication Server -- Creates replication definition create applied function replication definition upd_publishers_pubs2 with primary at TOKYO_DS.pubs2 (@pub_id char(4), @pub_name varchar(40), @city varchar(20), @state char(2)) go /* end of script */
This script creates the request function replication definition at the remote (Sydney) Replication Server:
-- Execute this script at Sydney Replication Server -- Creates replication definition create request function replication definition upd_publishers_pubs2_req with primary at SYDNEY_DS.pubs2 with primary function named upd_publishers_pubs2_req with replicate function named upd_publishers_pubs2 (@pub_id char(4), @pub_name varchar(40), @city varchar(20), @state char(2)) go /* end of script */
This script creates a subscription at the remote Replication Server using the no-materialization method for the applied function replication definition defined at the central Replication Server:
-- Execute this script at Sydney Replication Server -- Creates subscription using no-materialization for upd_publishers_pubs2 create subscription upd_publishers_pubs2_sub for upd_publishers_pubs2 with replicate at SYDNEY_DS.pubs2 without materialization go /* end of script */
This script creates a subscription at the central Replication Server using the no-materialization method for the request function replication definition defined at the remote Replication Server.
-- Execute this script at Tokoyo Replication Server -- Creates subscription using no-materialization for upd_publishers_pubs2_req create subscription upd_publishers_pubs2_req_sub for upd_publishers_pubs2_req with replicate at TOKOYO_DS.pubs2 without materialization go /* end of script */