Create replication definitions for the salesdetail table at each site using the sample scripts.
-- Execute this script at Chicago RSCHI. -- Creates replication definition chi_sales. create replication definition chi_sales_rep with primary at DSCHI.DBCHI
(stor_id char(4), ord_num varchar(20), title_id varchar6), qty smallint, discount float)
primary key (stor_id, ord_num)
searchable columns(stor_id, ord_num, title_id) go
/* end of script */
-- Execute this script at New York RSNY. -- Creates replication definition ny_sales. create replication definition ny_sales_rep with primary at DSNY.DBNY
with all tables named 'salesdetail' (stor_id char(4),
ord_num varchar(20), title_id varchar6), qty smallint, discount float)
primary key (stor_id, ord_num)
searchable columns(stor_id, ord_num, title_id) go
/* end of script */
-- Execute this script at San Francisco RSSF. -- Creates replication definition sf_sales. create replication definition sf_sales_rep with primary at DSSF.DBSF
with all tables named 'salesdetail' (stor_id char(4),
ord_num varchar(20), title_id varchar6), qty smallint, discount float)
primary key (stor_id, ord_num)
searchable columns(stor_id, ord_num, title_id) go
/* end of script */
Subscriptions
Each site has a subscription to the replication definitions at the other two sites. These scripts create the subscriptions:
-- Execute this script at Chicago RSCHI.
-- Creates subscriptions to ny_sales and sf_sales. create subscription ny_sales_sub
for ny_sales_rep
with replicate at DSCHI.DBCHI where stor_id = '5023'
go
create subscription sf_sales_sub for sf_sales_rep
with replicate at DSCHI.DBCHI where stor_id = '8042'
go
/* end of script */
-- Execute this script at New York RSNY.
-- Create subscriptions to chi_sales and sf_sales. create subscription chi_sales_sub
for chi_sales_sub
with replicate at DSNY.DBNY where stor_id = '7067' go
create subscription sf_sales_sub for sf_sales_rep
with replicate at DSNY.DBNY where stor_id = '8042' go
/* end of script */
-- Execute this script at San Francisco RSSF. -- Creates subscriptions to chi_sales and ny_sales. create subscription chi_sales_sub
for chi_sales_rep
with replicate at DSSF.DBSF where stor_id = '7067' go
create subscription ny_sales_sub for ny_sales_rep with replicate at DSSF.DBSF where stor_id = '5023' go /* end of script */
Corporate Rollup
In the corporate rollup model, multiple primary fragments maintained at remote sites are consolidated into a single aggregate replicate table at a central site.
The corporate rollup model has distributed primary fragments and a single, centralized consolidated replicate table. The table at each primary site contains only the data that is primary at that site. No data is replicated to these sites. The corporate rollup table consists of rolled-up data from the primary sites.
The corporate rollup model requires distinct replication definitions at each of the primary sites. The site where the data is consolidated has a subscription for the replication definition at each primary site.
Replication Agents are required at the primary sites but not at the central site, since data will not be replicated from that site.
These tasks must be performed to create a corporate rollup from distributed primary fragments:
• Create the table in each primary database and in the database at the central site. The tables should have the same structure and the same name.
• Create indexes and grant appropriate permissions on the tables.
• In each remote database, allow for replication on the table with the sp_setreptable system procedure.
• Create a replication definition for the table at each remote site.
• At the headquarters site, where the data is to be consolidated, create subscriptions for the replication definitions at the remote sites.
Figure 14: Corporate Rollup Model with Distributed Primary Fragments
This figure illustrates a salesdetail table with a corporate rollup at a headquarters site. The headquarters site receives data from the remote sites via three subscriptions.
Figure 15: Table with Multiple Primary Fragments
Replication Definitions
Create replication definitions for the salesdetail table at each primary site using the sample scripts.
-- Execute this script at Chicago RSCHI. -- Creates replication definition chi_sales. create replication definition chi_sales_rep with primary at DSCHI.DBCHI
with all tables named 'salesdetail' (stor_id char(4),
ord_num varchar(20), title_id varchar6), qty smallint, discount float)
primary key (stor_id, ord_num)
searchable columns(stor_id, ord_num, title_id) go
/* end of script */
-- Execute this script at New York RSNY. -- Creates replication definition ny_sales. create replication definition ny_sales_rep with primary at DSNY.DBNY
with all tables named 'salesdetail' (stor_id char(4),
ord_num varchar(20), title_id varchar6), qty smallint, discount float)
searchable columns(stor_id, ord_num, title_id) go
/* end of script */
-- Execute this script at San Francisco RSSF. -- Creates replication definition sf_sales. create replication definition sf_sales_rep with primary at DSSF.DBSF
with all tables named 'salesdetail' (stor_id char(4),
ord_num varchar(20), title_id varchar6), qty smallint, discount float)
primary key (stor_id, ord_num)
searchable columns(stor_id, ord_num, title_id) go
/* end of script */
Subscriptions
The headquarters site has subscriptions to the replication definitions at each of the three primary sites. The primary sites have no subscriptions.
This script creates the subscriptions in the RSHQ Replication Server:
-- Execute this script at Headquarters RSHQ. -- Creates subscriptions to chi_sales, ny_sales, -- and sf_sales.
create subscription chi_sales_sub for chi_sales_rep
with replicate at DSHQ.DBHQ where stor_id = '7067' go
create subscription ny_sales_sub for ny_sales_rep
with replicate at DSHQ.DBHQ where stor_id = '5023' go
create subscription sf_sales_sub for sf_sales_rep
with replicate at DSHQ.DBHQ where stor_id = '8042' go