Applied Functions

You can use applied functions to replicate stored procedure invocations to remote sites with replicate data.

Using applied functions to replicate primary data, lets you:

Reduce network traffic over the WAN
Increase throughput and decrease latency because applied functions execute more rapidly
Enable a more modular system design

In the following example, a client application at the primary (Tokyo) site executes a user stored procedure, upd_publishers_pubs2, which makes changes to the publishers table in the primary database. Execution of upd_publishers_pubs2 invokes function replication, which causes the corresponding stored procedure, also named upd_publishers_pubs2, to execute on the replicate data server.

At the Primary Site:

Create the user stored procedure in the primary database.
Mark the user stored procedure for replicated function delivery using sp_setrepproc.
Grant the appropriate procedure permissions to the appropriate user.
At the primary Replication Server, create the function replication definition for the stored procedure with parameters and datatypes that match those of the stored procedure. You can specify only the parameters you want to replicate.

At the Replicate Site:

Create a stored procedure in the replicate database with the same parameters (or a subset of those parameters) and datatypes as those created in the primary database. Grant appropriate permissions to the procedure to the maintenance user.
Create a subscription to the function replication definition in the replicate Replication Server.

Basic Primary Copy Model Using Applied Functions
Figure 3-2 shows an example of the basic primary model using applied functions. A client application at the primary site executes a stored procedure which makes changes to the publishers table in the primary database. Execution of the stored procedure causes the corresponding stored procedure at the replicate site to execute on the replicate data server and change the replicate data.

Figure 3-2 shows an example of the basic primary model using applied functions. A client application at the primary site executes a stored procedure which makes changes to the publishers table in the primary database. Execution of the stored procedure causes the corresponding stored procedure at the replicate site to execute on the replicate data server and change the replicate data.

Stored Procedures

This script creates stored procedures for the publishers table at the primary and replicate sites.

-- Execute this script at Tokyo and Sydney data servers
 -- 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
 go
 /* end of script */

Function Replication Definition

This script creates an applied function replication definition for the publishers table at the primary Replication Server. The replication definition uses the same parameters and datatypes as the stored procedure in the primary database.

-- Execute this script at Tokyo Replication Server
-- Creates replication definition
_upd_publishers_pubs2_repdef
   create applied function replication definition
   upd_publishers_pubs2_repdef
with primary at TOKYO_DS.pubs2
with all functions named upd_publishers_pubs2
(@pub_id char(4),
 @pub_name varchar(40),
 @city varchar(20),
 @state char(2))
go
/* end of script */

Subscriptions

You can create a subscription for a function replication definition in one of two ways:

Use the create subscription command and the no-materialization method.

Use this method if primary data is already loaded at the replicate, and updates are not in progress.
Use the define subscription, activate subscription, and validate subscription commands and the bulk materialization method.

Using the No-Materialization Method

This script creates a subscription at the replicate Replication Server using the no-materialization method for the replication definition defined at the primary Replication Server.

-- Execute this script at Sydney Replication Server
-- Creates subscription using no-materialization
-- for upd_publishers_pubs2_repdef
create subscription upd_publishers_pubs2_sub
   for upd_publishers_pubs2_repdef
with replicate at SYDNEY_DS.pubs2
without materialization
go
/* end of script */

Using Bulk Materialization

This script defines, activates, and validates a subscription at the replicate Replication Server for the replication definition defined at the primary Replication Server.

-- Execute this script at Sydney Replication Server
-- Creates subscription using bulk materialization
-- for upd_publishers_pubs2_repdef
define subscription upd_publishers_pubs2_sub
   for upd_publishers_pubs2_repdef
with replicate at SYDNEY_DS.pubs2
go

activate subscription upd_publishers_pubs2_sub
   for upd_publishers_pubs2_repdef
with replicate at SYDNEY_DS.pubs2
go
/* Load data. If updates are in progress,use activate
subscription with the “with suspension” clause and
resume connection after the load. */

validate subscription upd_publishers_pubs2_sub
   for upd_publishers_pubs2_repdef
with replicate at SYDNEY_DS.pubs2
go
/* end of script */