INF: Handling Subscriber Schema Changes with Replication

This article was previously published under Q190691
This article has been archived. It is offered "as is" and will no longer be updated.
Microsoft SQL Server 6.5 replication provides the option to replicate totables that have different schema at the subscriber. This option isintended to be used with subscribers that require a different tablestructure from the publisher and can be exploited to use advanced supportfor specific datatypes or handling constraints. This article outlines thedifferent methods that can be used to handle specific subscriber schemachanges.
More information
SQL Server provides flexible, near-real-time replication to other SQLServers or ODBC subscribers. It also provides an automatic way of creatingthe tables at the subscribers with identical schema. You may find it usefulto change the schema at the subscriber for various reasons: some ODBCdrivers and/or data sources do not support long names, some drivers and/ordata sources specify smaller precision for certain datatypes, subscriberapplications require schema changes, and so forth. You can either changethe schema definition file (.sch) for the article or manually create thetables at the subscriber. In either case, depending on the type of change,some special handling is required. Microsoft generally recommends that thesubscriber schema be identical to that of the publisher and if any failureor conflicts arise because of the schema changes, reconciliation should bedone by the system administrator. Use the following guidelines for handlingsome special cases where subscriber schema changes are necessary.

Column Names

If the column names will be different at the subscriber, usually nothingneeds to be done if the ordering is maintained and the primary key columnnames are the same. If they will be different, use custom stored proceduresto handle the name changes, using the changed primary key names for theINSERT, UPDATE, and DELETE statements. For information about how to set upreplication to use custom stored procedures, see the "Adding StoredProcedures for Insert, Update, and Delete" topic in SQL Server BooksOnline. (Query using the title, including the parentheses.) Regardingsynchronization, if the name change is the only change, no special handlingis required.

Vertical or Horizontal Partition

You can use automatic synchronization if the subscriber table only has theset of columns in the vertical partition. In this case, no special handlingis required. Horizontal partitioning does not affect the schema of thereplicated table.

Additional Columns, Different Ordering of Columns at Subscriber

If the subscriber table will have additional columns than the onesspecified in the vertical partition or columns in a different order, youneed to manually synchronize the tables using a format file or run "bcpout" at the publisher using a view defined in the order of the columns atthe subscriber. Moreover, the "Use column names in SQL Statements" optionshould be checked when defining the article. Otherwise, INSERT statementsmay fail because there are additional columns or the columns are improperlyordered. Update or Delete operations do not apply because they operatebased on column names and primary key columns.

Column Nullability

Generally it is recommended that you not change the nullability of columnsat the subscriber. If any column in a subscribed table will have differentnullability, special handling must be in place for sync as well as forstatements. If the publisher allows NULL and the subscriber does not, youalso need to handle any NULL data that may already be stored in thepublished table. In this case, automatic sync cannot be used because theBCP native mode output has different formats for nullable and non-nullablecolumns; BCP character-mode option is actually tailor-made for replicationto ODBC subscribers and hence has some data modifications for datetimecolumns, and so forth. Therefore, you have to use manual sync and manuallyrun "bcp out" on the data in character-mode out of the table (or view, ifvertical partition) and then run "bcp in" on the data at the subscriber.

If you have any NULL data in the published table, you will need specialhandling to account for these records; defining a default on the columnwill suffice because "bcp in" will apply the defaults when NULL data isencountered. Also, if NULL data is inserted at the publisher, you will needto handle this in a custom stored procedure or use the default, as above.


For the steps on how to set up replication with identity property on thecolumn at the subscriber, see the following article in the MicrosoftKnowledge Base:
190690 : INF: How to Set Up Replication on Tables with an Identity Column
prodsql definition different modified catalog

Article ID: 190691 - Last Review: 11/01/2013 22:47:00 - Revision: 4.0

  • Microsoft SQL Server 6.5 Standard Edition
  • kbnosurvey kbarchive kbinfo KB190691