All about “Identity Range Management”
José Moreira Neto
SQL Server Escalation Services
Automatic Identity Range Management
Microsoft SQL Server automatically generates sequential numbers for new rows inserted in the table containing the identity column. Identity columns are normally included as part of Primary Keys, therefore the need to avoid duplicates. To use identity columns in a replication topology that has updates at more than one node, each node in the replication topology must use a different range of identity values, so that duplicates do not occur.
Replication can automatically manage identity ranges for Transactional and Merge Publications, For example, the Publisher could be assigned the range 1-100, Subscriber A the range 101-200, and Subscriber B the range 201-300. Only user inserts, but not replication agent inserts cause the identity column value to be incremented.
You should carefully select the best data type for you unique row identifiers. Subscribers must synchronize with the Publisher to receive new ranges. Because Subscribers are assigned identity ranges automatically, it is possible for any Subscriber to exhaust the entire supply of identity ranges if it repeatedly requests new ranges. 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, whereas a int or bigint would provide a much larger range. Another alternative is to avoid altogether the use of numeric sequential identity for row uniqueness across replicas and move to essentially unique data types like GUIDs.
Merge Replication and Identities
For Merge Replication scenarios the identity values are assigned from a pool at the Publisher and propagated to Subscribers by the Merge Agent. Ranges are controlled by the parameters @identity_range, @pub_identity_range and @threshold of sp_addmergearticle system stored procedure.
@identity_range parameter controls the identity range size initially allocated both to the Publisher and to Subscribers.
@pub_identity_range parameter controls the identity range size for republishing allocated to Subscribers with server subscriptions
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. A subscriber assigned an identity range of 10000 and threshold of 80 will request a new range after 8000 inserts at the subscriber. Ranges are assigned when the merge agent runs, 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.
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
Note: SQL Compact Desktop or SQL Compact Mobile subscribers only use range_begin and range_end.
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 on the Publisher was performed by a user not in that role the Merge Agent, or a user who is a member of the db_owner role must run sp_adjustpublisheridentityrange (Transact-SQL).
You can see the existing ranges selecting from the table MSmerge_identity_range_allocations in the distribution database. Review this table to learn how often new ranges are being allocated. If you discover frequent reallocations, consider increase the ranges assigned to subscribers. This may help prevent a subscriber from running out of identity values between synchronizations.
For Merge Replication this information also exists in the table MSmerge_identity_range on the publisher/subscriber databases.
Note that for both tables above, we have two active ranges available for each subscriber (each with its Primary and Secondary limits). One for rows inserted at the publisher is_pub_range=1, and another for lines inserted at the subscriber is_pub_range=0.
Identity Ranges allocated to Merge Republishers control the identity ranges assigned to the subscribers to that specific Republisher. For example:
Root Publication: @pub_identity_range = 1000, @identity_range = 100
Re-Publisher Publication: @pub_identity_range = 500, @identity_range = 50
Results Identity Range
Root Subscribers 100
RePublisher INSERT 100 (direct inserts to Republisher)
RePublisher RANGES 1000 (total range for all its subscribers)
RePublisher SUBSCRIBER 50 (for each subscriber)
To see the Ranges allocated:
SELECT * FROM [distribution].[dbo].[MSmerge_identity_range_allocations]
SELECT [publisher_db] ,[publication],[subscriber_db],[is_pub_range],[range_begin],[range_end],next_range_begin],[next_range_end]
–Paste in New Query Window or Notepad to see results formatted correctly
publisher_db publication subscriber_db is_pub_range range_begin range_end next_range_begin next_range_end
—————————— —————————— —————————— ———— —————————— —————————— —————————— ——————————
Pub RootPublication Pub 0 1 101 101 201
Pub RootPublication PubSub1 0 201 301 301 401
Pub RootPublication RePub1 0 401 501 501 601
Pub RootPublication RePub1 1 601 1601 1601 2601
Pub RootPublication PubSub2 0 2601 2701 2701 2801
Pub RootPublication RePub2 0 2801 2901 2901 3001
Pub RootPublication RePub2 1 3001 4001 4001 5001
RePub1 RePub1Publication RePub1Sub1 0 601 651 651 701
RePub1 RePub1Publication RePub1Sub2 0 701 751 751 801
(9 row(s) affected)
Transactional Replication and Identities
Concepts are the same as Merge Replication, but 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.
Same considerations as for Merge Replication applies, choosing the correct data types will contribute to the overall success. Sp_addarticle system stored procedure parameters @identity_range ,@pub_identity_range and @threshold are used in the same fashion and with the same side effects like Specifying a higher @threshold parameter results in smaller gaps, but the system is less fault-tolerant. Note that as well as Merge Replication the subscriber agent needs to execute to provide new ranges.
As with Merge Replication, 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, or a user who is a member of the db_owner role must run sp_adjustpublisheridentityrange (Transact-SQL) on the Publisher and the Distribution Agent on the Subscriber.
Common Identity Range Problems
1) Account that makes inserts to the publisher or subscribed tables is not db_owner.
SQL 2005/8 subscribers have merge replication triggers that can adjust an exhausted primary range onto the secondary auxiliary range if the account making inserts is a member of db_owner role. Publishers can also refresh the range (create new Primary and Secondary Ranges) if the account connected is a member of db_owner role. However, if the account making inserts into the tables does not belong to the db_owner role, the triggers cannot make such adjustments. In this case, the Merge Agent needs to execute in order to make those adjustments before the range exhausts to avoid new inserts to fail or a member of db_owner must manually run the system stored procedure sp_adjustpublisheridentityrange (Transact-SQL).
2) A publisher or subscriber consumed all available range before the agents had the opportunity to create new ranges.
On the Subscriber the inserts will fail as the ranges are protected by a CHECK CONSTRAINT. The table trigger will move the Identity to the next range but not fix the CHECK CONSTRAINT. In this case running the Merge Agent on the subscriber fixes the issue.
On the Publisher, if the account is a member of db_owner, the trigger will fix the range and check constraint. Alternatively on the Publisher you can run sp_adjustpublisheridentityrange or any Merge Agent.
3) A subscriber run out of possible ranges.
This is a very serious situation, where the data type chosen for any identity fields exhaust its range of possible values. One may chose a data type that would suffice for a single instance of the database but when the database is replicated and this same range needs to be distributed the problem arise.
Be cautious when choosing the right data type for your tables. Estimate the number of rows being inserted at all subscribers then plan for growth.
|smallint||-2^15 (-32,768) to 2^15-1 (32,767)|
-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|
Alternative to Identity Ranges
Alternatively, you could avoid Identity Range Management problems altogether by using GUIDs as table Primary Key. This may reduce storage requirements as Merge Replication automatically adds Rowguid when table is published. If one already exists and is flagged as ROWGUIDCOL property in table design, Merge will use that column instead of adding a new Rowguid column.
Merge replication adds the column rowguid to every table, unless the table already has a column of data type uniqueidentifier with the ROWGUIDCOL property set (in which case this column is used). If the table is dropped from the publication, the rowguid column is removed; if an existing column was used for tracking, the column is not removed.