Master/Detail Relationships Implementation

You can use applied functions to replicate only selected data to remote sites, thus reducing 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.

Sample Tables Used in Master/Detail Relationship
Figure 3-14 shows the table structure in the master publishers tables and the titles table, which is the detail table, when replicating only selected data using applied functions. The publishers table at the primary site contains records for all states. Only the rows in the publishers and titles tables related to California are replicated to the replicate site.

Figure 3-14 shows the table structure in the master publishers tables and the titles table, which is the detail table, when replicating only selected data using applied functions. The publishers table at the primary site contains records for all states. Only the rows in the publishers and titles tables related to California are replicated to the replicate site.

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.

Perform these steps at the primary and the replicate sites to enable selective substitution at the replicate Replication Server.

At the Primary and Replicate Sites:

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.

At the Primary Site:

Mark each upper-level stored procedure for replication, using sp_setrepproc.
Create a function replication definition for each upper-level stored procedure.

At the Replicate Site:

Create subscriptions to the function replication definitions.

Stored Procedures with Insert Clauses
Create the ins_publishers and ins_titles insert stored procedures and the upper-level stored procedure ins_pub_title using the sample scripts.
Stored Procedures with Delete Clauses
Create the del_publishers and del_titles stored procedures and the upper-level stored procedure del_pub_title using the sample scripts.
Stored Procedures with Update Clauses
Create the upd_publishers and upd_titles stored procedures and the upper-level stored procedure upd_pub_title that controls the execution of upd_publishers and upd_titles using the sample scripts. The update procedures differ at the primary and replicate sites.
Function Replication Definitions
Create applied function replication definitions on the primary Replication Server for ins_pub_title, del_pub_title, and upd_pub_title using the sample scripts.
Subscriptions
Creates the subscriptions at the replicate Replication Server using the no-materialization method. Use this method when you do not need to load data at the replicate site.

Parent topic: Model Variations and Strategies