Export (0) Print
Expand All

Queued Updating and Identity Ranges

SQL Server 2000

Queued Updating and Identity Ranges

Normally with snapshot replication and transactional replication (read only or using immediate updating), if the publishing table contains a column with the identity data type, the identity property is not propagated to the Subscriber.

The identity property is used to provide next number values for data automatically (for example, for columns such as Customer ID or Order ID). When using immediate updating, the Publisher determines this value, and as part of the 2PC transaction initiated by the Subscriber, it is synchronized between Publisher and Subscriber.

With queued updating and immediate updating with queued updating as a failover, identity values must be assigned at the Subscriber because the Subscriber may be offline and updates at the Subscriber may be sent to a queue. In this case, the Publisher will not be able to assign identity values immediately. Therefore, when the initial snapshot is applied at the Subscriber, the identity property is propagated as well.

To avoid different Subscribers assigning the same identity values, you can define identity ranges for each Subscriber. When you define identity ranges, a Subscriber is allowed to assign values only from a specific range.

You can manage identity values using automatic identity ranges (SQL Server 2000 replication handles assigning identity ranges for you) or you can set identity ranges manually using a check constraint and the NOT FOR REPLICATION option on the IDENTITY property of a Transact-SQL CREATE TABLE statement.

For more information about handling identity values in replication, see Replication Data Considerations.

If you are using the attachable subscription database feature and the subscription database has subscriptions to publications that allow queued updating with auto identity range articles, you will need to run the distribution agents to obtain new identity ranges on the Subscriber after attaching the subscription database. For more information, see Attachable Subscription Databases.

See Also

Identity Ranges with Immediate Updating and Queued Updating

© 2015 Microsoft