The pending table is a design enhancement of applied and request functions that allows clients at a remote site to update primary data and see the updates at the replicate site before they are returned from the primary 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 primary site using a request function. Changes to the primary 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 replicate site before the replication system returns the updates.
When a client application executes the user stored procedure at the replicate 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 primary database, it is distributed to the remote sites, including the site where the transaction originated. At the replicate 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 replicate database. Grant appropriate permissions.
Create a user stored procedure in the replicate 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 replicate database that updates the replicated table and deletes the corresponding update from the pending table. Grant appropriate permissions to the maintenance user.
Create a subscription to the function replication definition.
Create the stored procedure that modifies the primary data.
Create the function replication definition for the applied function.
Create the function replication definition for the request function.
In this example, a client application at the replicate (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 primary (Tokyo) site. Execution of upd_publishers_pubs2 at the primary site causes the stored procedure upd_publishers_pubs to execute at the replicate site, which updates the publishers table and deletes the corresponding information from the publishers_pend table.
Figure 3-13 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-13: Request functions and a local pending table
This script creates a pending table in the replicate 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, state char(2) null) go /* end of script */
The script creates the stored procedure upd_publisher_pubs2at the primary site.
The following script modifies the upd_publishers_pub2_reqstored procedure at the replicate site. The insert into clause tells the replicate Replication Server to insert 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 modifies the upd_publishers_pubs2 procedure for the replicate 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 */
The script “Function replication definition” creates the function replication definition upd_publishers_pubs2_req—for the request function—at the primary site.
The script “Function replication definition” creates the function replication definition upd_publishers_pubs2—for the applied function—at the primary site.
The script “Subscriptions” creates the subscription upd_publishers_pubs2_sub—for the function replication definition upd_publisher_pubs2—at the replicate site.
