Publisher Identity Ranges

Question

Publisher Identity Ranges

Chee

SSC Veteran

Points: 295
More actions
January 23, 2007 at 9:44 pm

#194017

Hi,
I am using Merge replication, in SQL Server 2005 SP1, and have two key problems related to Identity Range Management.
1) For an article's properties I set the "Subscriber range size" to 10000 and the publisher range size to 10 million. But the publisher range has no effect. Looking at the web and trying this myself the value in "subscriber range size" also applies to the publisher. Why is that? Can't I specifically set the publisher range? From what I understand this was possible in SQL 2000
2) In terms of renewal of ranges I understand for subscribers if they exceed their threshold and then sync they will be allocated a new range. However for the publisher this should happen automatically. This was the case with my database however recently it stopped renewing? Any ideas why? I know I can manually refresh it using the system stored proc sp_adjustpublisheridentityrange. However what exactly is "Automatically" renewing it in the first place?
Would greatly appreciate any help on this
cheers!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

MohammedU SSCoach Points: 19644 More actions · Answer 1

From BOL:

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.

http://msdn2.microsoft.com/en-us/library/ms152543.aspx

MohammedU
Microsoft SQL Server MVP

Chee SSC Veteran Points: 295 More actions · Answer 2

Hi,

Thanks for your reply, however I'm more concerned with the publisher side i.e I have an app connecting directly to a sql host and receive this error due to reasons in my previous post:

Message: The insert failed. It conflicted with an identity range check constraint in database xxx replicated table xxx column 'ID'. If the identity column is automatically managed by replication, update the range as follows: for the Publisher, execute sp_adjustpublisheridentityrange; for the Subscriber, run the Distribution Agent or the Merge Agent.

The subscribers identity ranges are fine as yes from BOL they should be assigned new ranges when they sync

Anymore help would be great.

Thanks!

MohammedU SSCoach Points: 19644 More actions · Answer 3

Check if the contrainsts and ranges are correct through SSMS on both servers..

MohammedU
Microsoft SQL Server MVP

Chee SSC Veteran Points: 295 More actions · Answer 4

yes I have done a sp_help on the offending tables to check the constraints and obviously I get the error message when the app is trying to insert values beyond the constraint range.

My main question though is why is this happening??, as in the documentation if I have automatically manage identity range set to "Automatic" the publisher should automatically get new identity ranges if it is close to exhausting it. This was working but now its not. Any ideas why or how exactly it automatically renews ranges would be much appreciated.

Alternative I know if I set my publisher range to a ridicuously high value (set and forget approach) I can avoid this problem. But is there a way that I can "exclusively" set the publisher range? If so where do I do this?

Jeff Gray SSChampion Points: 10667 More actions · Answer 5

Hi, check the publisher identity range at the distributor. I had to manually update those to get a larger range. distribution.dbo.MSrepl_identity_range. I tried using the "Change Merge Article" sproc, but that did not do it for me.

Also, this was SQL2000 SP3 with anonymous merge subscribers. I'm not running merge replication on 2005 yet, so I'm not sure how much of it carries over to 2005.

jeff