In this 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 is a “rollup” of the data at 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 3-5 illustrates the flow of data for a corporate rollup application model.
Figure 3-5: Corporate rollup model with distributed primary fragments
Figure 3-6 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 3-6: Table with multiple primary fragments
