Merge Replication Indentity Semi-Automatic Range allocation

I have a publisher and 2 subscribers on the network and the table I am merge
replicating has an identity field, with NOT FOR REPLICATION set, to allow me
to use identiy ranges which it does very well, however I have 1 problem and 1
question.
Problem 1
When I reach my threshold (80%) it stops that server from creating new
records and an error is produced until the merge agent has run and the best
that can be is once a minute. How can I configure the system to not wait for
the next time the merge agent runs, as this system will be creating 100's of
transactions a minute.
Question 1
If the threshold is set to 80% and I have a range of 1000, then when it gets
to 800 it stops and sets the next id number to the first in the next block
meaning that the remaining 200 are redundant.
I would have expected that when the threshold is reached that until the new
numbers are received then the server would use the range after the threshold
until it receives the next range otherwise why not always set the threshold
to 100% as it just errors when it hits the threshold anyway? Looking at BOL
it says for transactional then the agent runs continously, but with merge no
such statement, and as its merge based the subscribers could be offline for
some time, so shouldn't there be a more controlled process when they reach
their threshold.
I think I am missing an option on the publication maybe
thanks in advance.
Neil.
Thanks for the kb's Paul but the threshold value seems to be completely
pointless to me
In KB 322910 it says
Before you implement ranged identity management with merge replication, you
must first consider how many inserts will be performed by users and how
frequently they will be merging their changes. The primary goal when defining
the identity options for an article is to make the ranges large enough that
the Subscriber will not run out of values before the next merge.
Which suggests to me that the subscribers should get a new block of identity
values when they merge, however in my testing, a new range is only produced
when the subscriber has reached the threshold level and an error is produced
and they are not allowed to create any new records until the merge which does
mean that the threshold value is pointless and should be set to 99% or 100%
I hope this makes sense.
Neil.
"Paul Ibison" wrote:

> Neil,
> there are some problems with what you are trying to do
> that are documented:
> http://support.microsoft.com/default.aspx?scid=kb;en-
> us;304706&Product=sql2k
> http://support.microsoft.com/?kbid=310540
> I would suggest one of 2 options:
> (a) set the range size so large that it will never need
> to reseed
> or
> (b) use manual range management and a simple algorithm eg
> in the simplest case odd and even values for the case
> where there is just a publisher and subscriber.
> Regards,
> Paul Ibison
> (The ONLY sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Neil,
if you are regularly synchronizing, a new range will be
requested as a part of the synchronization process. This
is before the check constraint that controls the upper
boundary reports an error when a subscriber who performs
an insert reports an error. So although there will be
unused identity values, this should lead to a smooth
running of the subscriber.
HTH,
Paul Ibison
(The ONLY sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||I think your problem is because you are over running your threshold in the
batch. The way it is designed is that you pick a range which is
representative of the largest batch which could occur on your
publisher/subscriber and then adjust it upwards so that the threshold lies
within this batch.
Here are some examples
1) update 10,000 rows in a batch, max range is 10,000 threshold is 80% -
results range exceeded transaction rolled back
2) update 10,000 rows in a batch, max range is 20,000 threshold is 50% -
threshold exceeded, range adjusted on publisher and subscriber
3) update 10,000 rows in a batch, max range is 20,000 threshold is 80% -
threshold not exceeded, with first batch. range not adjusted on publisher.
Second batch runs, range exceeded, transaction rolled back.
4) update 10,000 rows in a batch, max range is 100,000, threshold is 80%,
threshold not exceeded until the 8th run, range adjusted on publisher and
subscriber, AND the publisher can still accept to more 10,000 batches before
blowing up.
The way most dba implement this is the set it and forget it philosophy. They
look at their publisher and subscriber and they parcel out the ranges that
they believe will not be exceeded during the lifetime of their replication
solution, and they don't have to worry about it.
One more point when you run into problems with automatic identity range
management you can use the following proc to fix things.
sp_mScheckidentityrange with the @.checkonly parameter having a value of 1
Some people have complained to me that the @.checkonly parameter is ignored
and the range is not updated. I havent' been able to repro this yet, but you
can always sp_MSadjustmergeidentity directly.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Neil" <Neil@.discussions.microsoft.com> wrote in message
news:7A350532-D333-4436-9035-87A376B935BC@.microsoft.com...
> Thanks for the kb's Paul but the threshold value seems to be completely
> pointless to me
> In KB 322910 it says
> Before you implement ranged identity management with merge replication,
you
> must first consider how many inserts will be performed by users and how
> frequently they will be merging their changes. The primary goal when
defining
> the identity options for an article is to make the ranges large enough
that
> the Subscriber will not run out of values before the next merge.
> Which suggests to me that the subscribers should get a new block of
identity
> values when they merge, however in my testing, a new range is only
produced
> when the subscriber has reached the threshold level and an error is
produced
> and they are not allowed to create any new records until the merge which
does
> mean that the threshold value is pointless and should be set to 99% or
100%[vbcol=seagreen]
> I hope this makes sense.
> Neil.
> "Paul Ibison" wrote: