Replicating a Partitioned Table

If appropriate for your requirements, you can publish only a subset of the data from a table. You can create an article that:

• Replicates only selected columns of the base table. Subscription servers receive only that vertically partitioned subset of the data. The replicated columns must include the declared primary key column.
• Replicates only selected rows of the base table. Subscription servers receive only that horizontally partitioned subset of the data. The following illustration shows the replication of a horizontally partitioned table.

Of course, an article can be simultaneously partitioned both vertically and horizontally.

In this example we publish a table and partition its article both vertically and horizontally. The server TERRIER publishes the pubs..employee table. It is vertically partitioned so that only the emp_id (primary key), fname, and lname columns are replicated. It is partitioned horizontally on the job_lvl value so that only rows for employees with a job level greater than 99 will be replicated. The server AIREDALE subscribes to this article.

    To replicate a partitioned table

1. Publish the pubs..employee table.

   

   • From the Server Manager window, select TERRIER, and then from the toolbar, choose the Manage Publications button. The Manage Publications dialog box appears.
   • Select the pubs database, and then choose the New button. The Edit Publications dialog box appears.
   • In the Publication Title box, type:

     filterpub
     

   • In the Description box, type:

     Publishing a horizontally and vertically filtered table.
     

   • From the Database Tables list, select the employee tables, and then choose the adjacent Add button. The table is added to the Articles in Publication column.
2. Set up vertical and horizontal partitions.
   • Choose the Edit button. The Manage Article dialog box appears, with the Filter tab selected.
   • Partition the table vertically by clearing the Replicate check boxes for all the columns that will not be replicated. In this example, only the check boxes for emp_id, fname, and lname should remain selected.

     Note that the primary key column (in this case, emp_id) must be replicated.

   • Partition the table horizontally by typing this in the Restriction Clause box:

     job_lvl > 99
     

   • Choose OK. The Edit Publications dialog box returns.
   • Choose Add. The Manage Publications dialog box returns.
   • Choose Close.
3. Subscribe AIREDALE to the filterpub publication from TERRIER.

   

   • From the Server Manager window, choose AIREDALE, and then from the toolbar, choose the Manage Subscriptions button. The Manage Subscriptions dialog box appears.
   • Open the published database tree for TERRIER (choose the "" box next to the server name), and then open the publications list for the pubs database (choose the "" box next to the database).
   • Select the filterpub publication, and then choose Subscribe. The Subscription Options dialog box appears.
   • From the list in the Destination Database box, select the inaire database.
   • Choose OK. The Manage Subscription dialog box returns.
   • Choose Close.

This replication example is now set up. If the default synchronization setting for the publication filterpub was not changed, synchronization will occur within five minutes. Synchronization will copy the schema and data from the published tables to the destination database. After that, replication of changes to table data will occur as changes are made.

Only the specified columns and rows will be synchronized and replicated.