Identity Ranges with Immediate Updating and Queued Updating

For publications that allow immediate updating but not queued updating (in snapshot replication and transactional replication), the Publisher controls identity values. You cannot assign identity ranges with this type of replication because the replication agents do not assign an IDENTITY property to the column on the Subscriber. Create the IDENTITY property at the Publisher only, and have the Subscriber use the base numeric data type (for example, int) with a default value of 0. These actions are taken automatically if the Distribution Agent initializes the schema and data (that is, if the synchronization type of the subscription is set to automatic). The next identity value is always generated at the Publisher and assigned to the row inserted at the Subscriber.

With queued updating, identity values must be assigned by the Subscriber because newly inserted rows at the Subscriber may be sent to a queue rather than directly to the Publisher. Because the data is sent asynchronously, there is no mechanism for the Publisher to assign an identity value immediately to a newly inserted row at the Subscriber, as there is for the immediate updating case.

For publications that allow immediate updating with queued updating as a failover option, assign identity ranges to Subscribers either automatically or manually. Inserted rows at a Subscriber will generate identity values from the assigned local identity range. The new Subscriber row will be sent to the queue, where it will be picked up by the Queue Reader Agent and applied to the Publisher with the correct (not reseeded) identity value.