INF: Handling Subscriber Schema Changes with Replication (190691)

MORE INFORMATION

SQL Server provides flexible, near-real-time replication to other SQL Servers or ODBC subscribers. It also provides an automatic way of creating the tables at the subscribers with identical schema. You may find it useful to change the schema at the subscriber for various reasons: some ODBC drivers and/or data sources do not support long names, some drivers and/or data sources specify smaller precision for certain datatypes, subscriber applications require schema changes, and so forth. You can either change the schema definition file (.sch) for the article or manually create the tables at the subscriber. In either case, depending on the type of change, some special handling is required. Microsoft generally recommends that the subscriber schema be identical to that of the publisher and if any failure or conflicts arise because of the schema changes, reconciliation should be done by the system administrator. Use the following guidelines for handling some special cases where subscriber schema changes are necessary.

Column Names

If the column names will be different at the subscriber, usually nothing needs to be done if the ordering is maintained and the primary key column names are the same. If they will be different, use custom stored procedures to handle the name changes, using the changed primary key names for the INSERT, UPDATE, and DELETE statements. For information about how to set up replication to use custom stored procedures, see the "Adding Stored Procedures for Insert, Update, and Delete" topic in SQL Server Books Online. (Query using the title, including the parentheses.) Regarding synchronization, if the name change is the only change, no special handling is required.

Vertical or Horizontal Partition

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

Additional Columns, Different Ordering of Columns at Subscriber

If the subscriber table will have additional columns than the ones specified in the vertical partition or columns in a different order, you need to manually synchronize the tables using a format file or run "bcp out" at the publisher using a view defined in the order of the columns at the subscriber. Moreover, the "Use column names in SQL Statements" option should be checked when defining the article. Otherwise, INSERT statements may fail because there are additional columns or the columns are improperly ordered. Update or Delete operations do not apply because they operate based on column names and primary key columns.

Column Nullability

Generally it is recommended that you not change the nullability of columns at the subscriber. If any column in a subscribed table will have different nullability, special handling must be in place for sync as well as for statements. If the publisher allows NULL and the subscriber does not, you also need to handle any NULL data that may already be stored in the published table. In this case, automatic sync cannot be used because the BCP native mode output has different formats for nullable and non-nullable columns; BCP character-mode option is actually tailor-made for replication to ODBC subscribers and hence has some data modifications for datetime columns, and so forth. Therefore, you have to use manual sync and manually run "bcp out" on the data in character-mode out of the table (or view, if vertical 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 special handling to account for these records; defining a default on the column will suffice because "bcp in" will apply the defaults when NULL data is encountered. Also, if NULL data is inserted at the publisher, you will need to handle this in a custom stored procedure or use the default, as above.

Identity

For the steps on how to set up replication with identity property on the column at the subscriber, see the following article in the Microsoft Knowledge Base: