Updating a publication v7

You can update existing publications in the following ways:

• Add tables to the publication
• Remove tables from the publication
• Update filter rules on publication tables

Add tables to a publication

For a single-master replication system, you can add tables to a publication, even while existing subscriptions are associated with the publication. Similarly, for a multi-master replication system, you can add tables to a publication while there are more primary nodes in the replication system.

1. Make sure the publication server whose node is the parent of the publication you want to change is running and registered in the Replication Server console you're using. See Registering a publication server to learn how to start and register a publication server.

2. (For SMR only): Select the Publication node of the publication to which you want to add tables.

   (For MMR only): Select the Publication node under the Publication Database node representing the primary definition node.

3. Select Publication > Update Publication > Add Tables.

4. Fill in the following fields in the Add Tables tab of the Add Tables dialog box:

   • Add. Select the boxes next to the table names from the Available Tables list to add to the publication. If the publication is a snapshot-only publication, then views appear in the Available Tables list as well. The Available Tables list contains only tables and views that aren't already members of other publications under the same Publication Database node. You can also select Use Wildcard Selection to use wildcard pattern matching for selecting tables to add to the publication.
   • Select All. Select this option if you want to include all tables and views in the Available Tables list in the publication.
   • Use Wildcard Selection. Select to use the wildcard selector to choose tables for the publication. See Selecting tables with the wildcard selector for information on the wildcard selector.

5. If you want to filter the rows of the publication tables or views, select the Table Filters tab. Define filter rules by entering a unique, descriptive filter name and an appropriate SQL WHERE clause in the Filter dialog box to select the rows you want to replicate.

   For a single-master replication system, see Adding a publication for information on defining table filters on a publication table. For a multi-master replication system, see Adding a publication.

6. (For SMR only): Select Add Tables. When Publication Updated Successfully appears, select OK.

   (For MMR only): Select Add Tables. The Data Sync Check dialog box appears warning you that synchronization replication is performed before the table is added. To proceed, select Yes. When Publication Updated Successfully appears, select OK.

7. The replication tree appears with the newly added table under the Publication node. Select the Refresh icon. The newly added table appears under the Subscription nodes of a single-master replication system or the added primary nodes of a multi-master replication system.

8. (For MMR only): If you want to modify or see the default conflict resolution options assigned to the newly added table, follow the instructions in Updating the conflict resolution options.

9. If you defined table filters on the newly added table and you want to use these filters on any subscriptions or primary nodes, you must enable the filters on the table in the desired subscriptions or primary nodes.

   For a single-master replication system, see Enabling and disabling table filters on a subscription.

   For a multi-master replication system, see Enabling and disabling table filters on a primary node.

Remove tables from a publication

You can remove one or more tables from a publication but only if the following condition is true:

• The tables to remove aren't parent tables referenced by foreign key constraints of child tables that aren't selected for removal as well.

In this entity relationship diagram, the emp table has a foreign key constraint referencing the dept table, and the jobhist table has two foreign key constraints. One constraint references the emp table and the other references the dept table.

If all three tables are in the publication, then you can remove the following combinations of tables:

• The jobhist table only
• Both the jobhist table and the emp table

1. Make sure the publication server whose node is the parent of the publication you want to change is running and registered in the Replication Server console you're using. See Registering a publication server to learn how to start and register a publication server.

2. (For SMR only): Select the Publication node of the publication from which you want to remove tables.

   (For MMR only): Select the Publication node under the Publication Database node representing the primary definition node.

3. Select Publication > Update Publication > Remove Tables.

4. Use the Remove Tables dialog box as follows:

   • Remove. Select the boxes next to the table names from the Available Tables to remove from the publication. If the publication is a snapshot-only publication, then views appear in the Available Tables list as well. You can also select Use Wildcard Selection to use wildcard pattern matching for selecting tables to remove from the publication.
   • Use Wildcard Selection. Select to use the wildcard selector to choose tables to remove from the publication. See Selecting tables with the wildcard Selector for more information.

5. Select Remove and then select Yes in response to the confirmation.

6. When Tables Removed Successfully appears, select OK.

The replication tree appears without the removed table under the Publication node.

• On this page
• Add tables to a publication
• Remove tables from a publication