You can use applied functions to replicate only selected data to remote sites. Using applied functions in this way reduces network traffic.
To implement master/detail relationships, use applied functions to support selective subscription to the master/detail tables. In this example,
The publishers and titles tables exist in the pubs2 database at the primary and replicate sites.
NY_DS is the primary site data server and SF_DS is the replicate site data server.
Figure 3-13 describes the publishers (master) and titles (detail) tables at the primary and replicate sites:
Figure 3-13: Sample tables used in master/detail relationship
The primary site contains all records, but the replicate site is interested only in records related to the state of California (CA). Only a selection of publishers and titles records need to be replicated, based on the state column. However, only the publishers table contains a state column.
Adding a state column to the titles table adds redundancy to the system. A second, more efficient solution ties updates to master and detail tables through stored procedures and then replicates the stored procedures using applied functions. The logic to maintain selective subscription is contained in the stored procedures.
For example, if, at the primary site, a publisher’s state is changed from NY to CA, a record for that publisher must be inserted at the replicate site. Having replicate rows inserted or deleted as a result of updates that cause rows in a subscription to change is called subscription migration.
To ensure proper subscription migration, subscriptions are needed for a set of “upper-level” stored procedures that control the stored procedures that actually perform the updates. It is the invocation for the upper-level stored procedure that is replicated from the primary site to the replicate site.
To handle changes in the state column, the replicate site must subscribe to updates when either the new state or the old state is CA.
The sections below list the activities you must perform to enable selective substitution at the replicate Replication Server.
Create stored procedures that insert records into the publishers and titles tables and an upper-level stored procedure that controls the execution of both insert procedures.
Create stored procedures that delete records from the publishers and titles tables and an upper-level stored procedure that controls the execution of both delete procedures.
Create stored procedures that update records in the publishers and titles tables and an upper-level stored procedure that controls the execution of both update procedures.
Grant appropriate permissions on all upper-level stored procedures.
Mark each upper-level stored procedure for replication, using sp_setrepproc.
Create a function replication definition for each upper-level stored procedure.
Create subscriptions to the function replication definitions.
