Export (0) Print
Expand All
Expand Minimize
0 out of 1 rated this helpful - Rate this topic

How to: Manage Identity Columns (Replication Transact-SQL Programming)

When Subscriber inserts are replicated back to the Publisher, identity columns must be managed to avoid assignment of the same identity value at both the Subscriber and Publisher. Replication can manage identity ranges automatically or you can choose to manually handle identity range management. You can use replication stored procedures to specify identity range management options when an article is created. For information on the identity range management options provided by replication, see Replicating Identity Columns.

ms146907.note(en-US,SQL.90).gifNote:
When publishing a table in more than one publication, you must specify the same identity range management options for both publications. For more information, see "Publishing Tables in More Than One Publication" in Publishing Data and Database Objects.

  1. At the Publisher on the publication database, execute sp_addarticle. If the source table being published has one or more identity columns, specify a value of auto for @identityrangemanagementoption, the range of identity values assigned to the Publisher for @pub_identity_range, the range of identity values assigned to each Subscribers for @identity_range, and the percent of total identity values used before a new identity range is assigned for @threshold. For more information about defining articles, see How to: Define an Article (Replication Transact-SQL Programming).

    ms146907.note(en-US,SQL.90).gifNote:
    Ensure that the data type of the identity column is large enough to support the total range of identities being assigned to all Subscribers.

  1. At the Publisher on the publication database, execute sp_addarticle. Specify a value of manual for @identityrangemanagementoption. For more information about defining articles, see How to: Define an Article (Replication Transact-SQL Programming).

  2. Assign ranges to identity article columns at the Subscriber to avoid generating conflicts for updating Subscribers. For more information, see the section on assigning ranges for manual identity range management in the topic Replicating Identity Columns.

  1. At the Publisher on the publication database, execute sp_addmergearticle. If the source table being published has one or more identity columns, specify a value of auto for @identityrangemanagementoption, the range of identity values assigned to a server subscription for @pub_identity_range, the range of identity values assigned to the Publisher and each client subscription for @identity_range, and the percent of total identity values used before a new identity range is assigned for @threshold. For more information on when new identity ranges are assigned, see Assigning Identity Ranges in the topic Replicating Identity Columns. For more information about defining articles, see How to: Define an Article (Replication Transact-SQL Programming).

    ms146907.note(en-US,SQL.90).gifNote:
    Ensure that the data type of the identity column is large enough to support the total range of identities being assigned to all Subscribers, particularly for Subscribers with server subscriptions.

  1. At the Publisher on the publication database, execute sp_addmergearticle. Specify one of the following values for @identityrangemanagementoption:

    • manual - Identity ranges must be assigned manually for updating Subscribers.
    • none - Identity columns at the Publisher will not be defined as identity columns at the Subscriber.

    For more information about defining articles, see How to: Define an Article (Replication Transact-SQL Programming).

  2. Assign ranges to identity article columns at the Subscriber to avoid generating conflicts for updating Subscribers.

  1. At the Publisher on the publication database, execute sp_helparticle and note the value of identityrangemanagementoption in the result set. If this value is 0, automatic identity range management is not enabled.

  2. If the value of identityrangemanagementoption in the result set is 1, change the settings as follows:

    • To change the assigned identity ranges, execute sp_changearticle at the Publisher on the publication database. Specify a value of identity_range or pub_identity_range for @property and the new range value for @value.
    • To change the threshold at which new ranges are assigned, execute sp_changearticle at the Publisher on the publication database. Specify a value of threshold for @property and the new threshold value for @value.
  1. At the Publisher on the publication database, execute sp_helpmergearticle and note the value of identity_support in the result set. If this value is 0, automatic identity range management is not enabled.

  2. If the value of identity_support in the result set is 1, change the settings as follows:

    • To change the assigned identity ranges, execute sp_changemergearticle at the Publisher on the publication database. Specify a value of identity_range or pub_identity_range for @property and the new range value for @value.
    • To change the threshold at which new ranges are assigned, execute sp_changemergearticle at the Publisher on the publication database. Specify a value of threshold for @property and the new threshold value for @value. For more information on when new identity ranges are assigned, see Assigning Identity Ranges in the topic Replicating Identity Columns.
    • To disable automatic identity range management, execute sp_changemergearticle at the Publisher on the publication database. Specify a value of identityrangemanagementoption for @property and either manual or none for @value.
Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.