Assigning Identity Ranges
Merge replication and transactional replication use different methods for assigning ranges; these methods are described in this section.
There are two types of ranges to take into account when replicating identity columns: the ranges assigned to the Publisher and Subscribers, and the range of the data type in the column. The following table shows the ranges available for the data types typically used in identity columns. The range is used across all nodes in a topology. For example, if you use smallint starting at 1 with an increment of 1, the maximum number of inserts is 32,767 for the Publisher and all Subscribers. The actual number of inserts depends on whether there are gaps in the values used and whether a threshold value is used. For more information about thresholds, see the following sections "Merge Replication" and "Transactional Replication with Queued Updating Subscriptions".
If the Publisher exhausts its identity range after an insert, it can automatically assign a new range if the insert was performed by a member of the db_owner fixed database role. If the insert was performed by a user not in that role, the Log Reader Agent, Merge Agent, or a user who is a member of the db_owner role must run sp_adjustpublisheridentityrange (Transact-SQL). For transactional publications, the Log Reader Agent must be running to automatically allocate a new range (the default is for the agent to run continuously).
|
Data type
|
Range
|
|---|
|
tinyint
|
Not supported for automatic management
|
|
smallint
|
-2^15 (-32,768) to 2^15-1 (32,767)
|
|
int
|
-2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647)
|
|
bigint
|
-2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807)
|
|
decimal and numeric
|
-10^38+1 through 10^38-1
|
Merge Replication
Identity ranges are managed by the Publisher and propagated to Subscribers by the Merge Agent (in a republishing hierarchy, ranges are managed by the root Publisher and the republishers). The identity values are assigned from a pool at the Publisher. When you add an article with an identity column to a publication in the New Publication Wizard or by using sp_addmergearticle (Transact-SQL), you specify values for:
-
The @identity_range parameter, which controls the identity range size initially allocated both to the Publisher and to Subscribers with client subscriptions.
.gif) Note: |
|---|
|
For Subscribers running previous versions of SQL Server, this parameter (rather than the @pub_identity_range parameter) also controls the identity range size at republishing Subscribers.
|
-
The @pub_identity_range parameter, which controls the identity range size for republishing allocated to Subscribers with server subscriptions (required for republishing data). All Subscribers with server subscriptions receive a range for republishing, even if they don't actually republish data.
-
The @threshold parameter, which is used to determine when a new range of identities is required for a subscription to SQL Server Compact 3.5 SP1 or a previous version of SQL Server.
For example, you could specify 10000 for @identity_range and 500000 for @pub_identity_range. The Publisher and all Subscribers running SQL Server 2005 or a later version, including the Subscriber with the server subscription, are assigned a primary range of 10000. The Subscriber with the server subscription is also assigned a primary range of 500000, which can be used by Subscribers that synchronize with the republishing Subscriber (you must also specify @identity_range, @pub_identity_range, and @threshold for the articles in the publication at the republishing Subscriber).
Each Subscriber running SQL Server 2005 or a later version also receives a secondary identity range. The secondary range is equal in size to the primary range; when the primary range is exhausted, the secondary range is used, and the Merge Agent assigns a new range to the Subscriber. The new range becomes the secondary range, and the process continues as the Subscriber uses identity values.
Subscribers running SQL Server Compact 3.5 SP1 or previous versions of SQL Server are assigned only the primary range; assignment of new ranges is controlled by the @threshold parameter. Additionally, a republishing Subscriber has only the range specified in the @identity_range parameter; it must use this range for local changes and for changes at Subscribers that synchronize with the republishing Subscriber. For example, you could specify 10000 for @pub_identity_range, 500000 for @identity_range and 80 percent for @threshold. After 8000 inserts at a Subscriber (80 percent of 10000), the Publisher is assigned a new range. When a new range is assigned, there will be a gap in the identity range values in the table. Specifying a higher threshold results in smaller gaps, but the system is less fault-tolerant: if the Merge Agent cannot run for some reason, a Subscriber could more easily run out of identities.
Transactional Replication with Queued Updating Subscriptions
Identity ranges are managed by the Distributor and propagated to Subscribers by the Distribution Agent. The identity values are assigned from a pool at the Distributor. The pool size is based on the size of the data type and the increment used for the identity column. When you add an article with an identity column to a publication in the New Publication Wizard or by using sp_addarticle (Transact-SQL), you specify values for:
-
The @identity_range parameter, which controls the identity range size initially allocated to all Subscribers.
-
The @pub_identity_range parameter, which controls the identity range size allocated to the Publisher.
-
The @threshold parameter, which is used to determine when a new range of identities is required for a subscription.
For example, you could specify 10000 for @pub_identity_range, 1000 for @identity_range (assuming fewer updates at the Subscriber), and 80 percent for @threshold. After 800 inserts at a Subscriber (80 percent of 1000), a Subscriber is assigned a new range. After 8000 inserts at the Publisher, the Publisher is assigned a new range. When a new range is assigned, there will be a gap in the identity range values in the table. Specifying a higher threshold results in smaller gaps, but the system is less fault-tolerant: if the Distribution Agent cannot run for some reason, a Subscriber could more easily run out of identities.