Validating a publication v7

After you create a publication, don't directly change the definitions of the tables belonging to the publication. Doing so can cause a failure during the replication process. Examples of table definitions that you must not change include:

• Adding or removing columns to a table
• Renaming columns
• Changing the data types of columns
• Changing the lengths of columns
• Changing a not-nullable column to nullable or a nullable column to not-nullable
• Adding or removing uniqueness constraints
• Adding or removing check constraints

In a single-master replication system, Replication Server doesn't propagate table definition changes to the subscription tables after you create the subscription tables. Rows that might be allowed in a modified publication table might not be allowed in the subscription table you didn't change. This condition causes an error during replication.

Similarly, in a multi-master replication system, table definition changes aren't propagated from one primary node to another. The exception is when you add a new primary node and you choose to replicate the schema definition from the primary definition node.

In addition, for synchronization replication with the trigger-based method, triggers are generated on the publication tables that use certain attributes of these tables. If you change the table definition, the trigger might not function properly.

If you don't use the DDL change replication feature, then take the following general steps if you make table definition changes.

In a single-master replication system, if you made changes to the definitions of one or more publication tables, the resolution to the problem must be handled on a case-by-case basis as it depends upon the type of changes that were made. In the worst-case scenario, you must remove and re-create the subscription and publication as follows:

• Remove the subscription that is associated with the publication. See Removing a subscription.
• Remove the subscription tables from the subscription database. Use SQL DROP TABLE statements in the database system.
• Remove the publication. See Removing a publication.
• Add the publication again. See Adding a publication.
• Add the subscription again. See Adding a subscription.

In a multi-master replication system, if you made changes to the definitions of one or more publication tables on one or more primary nodes, the resolution to the problem involves:

• Making sure the table definitions are updated on all primary nodes so that they are identical, or updating the table definition on the primary definition node so it can be replicated to the other primary nodes.
• Re-creating the publication database definitions of the primary nodes.

The general steps are the following:

• Remove the publication database definitions of all primary nodes except for the primary definition node. See Removing a publication database.
• Remove the publication. See Removing a publication.
• Remove the publication database definition of the primary definition node. See Removing a publication database. At this point, all of the triggers, shadow tables, and metadata are removed from the primary nodes.
• With respect to the publication table definitions, you can either:
  • Update the table definitions on all primary nodes so that they are identical.
  • Assume the table definitions on the primary definition node are up to date, and delete the out-of-date table definitions on all other primary nodes.
• Add the publication database definition for the primary definition node again. See Adding the primary definition node.
• Add the publication again. See Adding a publication.
• Add more primary nodes. See Creating more primary nodes. When adding a primary node, clear the Replicate Publication Schema check box if you already created the table definitions on all primary nodes. Select the Replicate Publication Schema check box if you want to propagate the table definitions from the primary definition node to all other primary nodes. A snapshot reloads the primary node tables from the primary definition node.

Validate a single publication

Replication Server provides a way to verify that certain characteristics of publication tables haven't changed since you created the publication.

This validation operation and those in Validating all publications can check for the following types of table modifications:

• Adding columns to a table
• Removing columns from a table
• Renaming columns

To validate a single publication:

1. Make sure the publication server whose node is the parent of the publication you want to validate 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: Select the Publication node of the publication you want to validate.

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

3. Select Publication > Validate Publication.

4. When All Schema of Published Tables in Publication 'publication_name' Are Up-To-Date appears, select OK.

If errors occur, determine which tables were changed and the changes that were made to the table definitions. Resolve these issues on a case-by-case basis as discussed earlier.

Validate all publications

You can validate all publications under a single Publication Database node in one operation.

1. Make sure the publication server whose node is the parent of the publications you want to validate 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: Select the Publication Database node under which you want to validate all publications.

   For MMR: Select the Publication Database node representing the primary definition node.

3. Select Publication > Validate All Publications.

4. If there were no modified tables, select OK.

   If there were modified tables, a list of publications that contain the modified tables appears. Determine which tables changed and the changes made to the table definitions. Resolve these issues on a case-by-case basis as discussed earlier.

• On this page
• Validate a single publication
• Validate all publications