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.

 

select

      publication,

      subscriber,

      subscriber_db,

      range_begin,

      range_end,

      next_range_begin,

      next_range_end,

      max_used 

from

      MSmerge_identity_range_allocations

 

 

For Merge Replication this information also exists in the table MSmerge_identity_range on the publisher/subscriber databases.

 

select

      artid,

      subid,

      range_begin,

      range_end,

      next_range_begin,

      next_range_end

from

      MSmerge_identity_range

 

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.

Merge Republishers

Identity Ranges allocated to Merge Republishers control the identity ranges assigned to the subscribers to that specific Republisher.  For example:

 

 

Identity Ranges
--------------------
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]

 

Example:

 

SELECT [publisher_db] ,[publication],[subscriber_db],[is_pub_range],[range_begin],[range_end],next_range_begin],[next_range_end]
FROM [distribution].[dbo].[MSmerge_identity_range_allocations]

 

--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)
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

 

 

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.

 

image

 

From SQL BOL: Frequently Asked Questions for Replication Administrators

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.

 

ReplTalk Start Here Blog Index