Table Replication Definitions

Table replication definitions allow you to replicate data from a primary source as read-only copies.

You can create one or many replication definitions for a primary table although a particular replicate table can subscribe to only one of them. See Multiple Replication Definitions for an example using multiple replication definitions.

You also can collect replication definitions in a publication and subscribe to all of them at one time with a publication subscription. See Publications for an example using publications.

For each table you want to replicate according to the basic primary copy model, you need to:

Set up routes and connections between Replication Servers.
Create the table you want to replicate in the primary database.
Create the table (or tables) to which you want to replicate in destination databases.
Create indexes and grant appropriate permissions on the tables.

At the Primary Site:

Mark the primary table for replication using the sp_setreptable system procedure.
Create one (or more) replication definitions for the table at the primary Replication Server.

At the Replicate Sites:

Create subscriptions for the table replication definitions at each replicate Replication Server.

See the Replication Server Administration Guide Volume 1 for details on setting up the basic primary copy model.

In this figure, a client application at the primary (Tokyo) site makes changes to the publishers table in the primary database. At the replicate (Sydney) site, the publishers table subscribes to the primary publishers table—for those rows where pub_id is equal to or greater than 1000.

Basic Primary Copy Model Using Table Replication Definitions
Figure 3-1 shows an example of the basic primary model using table replication definition. A client application at the primary site makes changes to the publishers table in the primary database. At the replicate site, the publishers table subscribes to the primary publishers table.

Marking the Table for Replication

This script marks the publishers table for replication.

-- Execute this script at Tokyo data server
 -- Marks publishers for replication
 sp_setreptable publishers, 'true' 
 go
 /* end of script */

Replication Definition

This script creates a table replication definition for the publishers table at the primary Replication Server.

-- Execute this script at Tokyo Replication Server
-- Creates replication definition pubs_rep
create replication definition pubs_rep
with primary at TOKYO_DS.pubs2
with all tables named 'publishers'
(pub_id char(4),
 pub_name varchar(40),
 city varchar(20),
 state varchar(2))
primary key (pub_id)
go
/* end of script */

Subscription

This script creates a subscription for the replication definition defined at the primary Replication Server.

-- Execute this script at Sydney Replication Server
 -- Creates subscription pubs_sub
 Create subscription pubs_sub
 for pubs_rep 
 with replicate at SYDNEY_DS.pubs2
 where pub_id >= 1000
 go
 /* end of script */