Re: Help!!! Merge Replication and identity values

• Previous message: Deepak Ramakumar: "Help!!! Merge Replication and identity values"
• In reply to: Deepak Ramakumar: "Help!!! Merge Replication and identity values"
• Messages sorted by: [ date ] [ thread ]

Date: Mon, 14 Jun 2004 22:45:54 -0400

wow! thats a lot of SQL CE databases.

1) to set the identity range on the publisher the correct way to do this is
through the articles property, select the identity range tab. Sounds like
you have already being there. After setting the range on the publisher the
ranges should be parceled out to the Subscribers. As Subscribers come online
they'll get a range assigned to them.

There are instances where the range won't be incremented correctly. For
instance if you have a range size on the publisher of 100 and you update
more than the threshold or range size on the publisher in a batch, the range
adjustment won't be done until the batch is complete. If the batch is more
than 100 records you blow the range and get a constraint error.

In cases like this you have to automatically adjust the identity ranges or
do it manually by adjusting the range table and the corresponding
constraint.

Because of these "limitations" many DBA's elect to use the set it and forget
it approach, where they assign a range to the publisher and subscriber
manually which will not be exceeded in the lifetime of the project/solution.

The dangers of manually making the adjustment is you have to use consistent
values everywhere and you have to adjust the constraint correctly. Other
than that its pretty safe.

The constraint is created when you create the snapshot and adjusted with the
proc sp_MSreseed. This proc is completely undocumented. If you disable the
constraint you may run into problems depending on what is updating your
table. Disableing it for inserts and updates will be harmless if only
replication is making the changes, otherwise you may have problems, if the
identity range is blown and another subscriber/publisher uses it.

-- 
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Deepak Ramakumar" <dramakumar@strongtie.com> wrote in message
news:evu$7CnUEHA.2028@TK2MSFTNGP11.phx.gbl...
> Hi,
>
>
>
> I have a Merge replication set up between a SQL Server and many (around
> 150-200) SQL CE databases. I am using identity as primary keys in 2 of the
> replicated tables which have a identity seed (set to 1) and range set (set
> to 1000 with 10000 as publisher range and 80% threshold). All was going
well
> until there was a database schema change and I had to rebuild the
> publication and re-initialize all subscribers (which was ok).
>
>
>
> But after rebuilding the publication and re-initialization all
subscribers,
> the agent started giving out identity ranges that conflicted with current
> values in the database. As some of you have faced similar situation and
have
> found workarounds. I used a script to manually change the "next seed"
value
> of the MSrepl_identity_range table to set the "next seed" identity value
to
> be the max value of the table....basically used UPDATE
> distribution..MSrepl_identity_range SET next_seed = max value + range...so
> on and so forth. This worked as far as giving each re-initialized
> subscribers a new identity range. I have 2 questions regarding this:
>
>
>
> * How can I set the publishers range? I manually updated the
> MSrepl_identity_range to a higher value than the max value used for
updating
> the distribution..MSrepl_identity_range using the sql script. What effects
> would this have?
>
> * In Check Constraint tab of the replicated table (generating identity
> values at subscriber), there is a check constraint value (value is:
> [Table1_Col_Id] > 255452 and [Table1_Col_Id] < 400000) which is "Enforced
> for Insert and Update" with constraint name like
> "repl_identity_range_pub_1CC33D46_49FA_4A34_9722_7F8D53C0B20A". I had to
> uncheck them for the merge agent to be able to add new rows to the server.
> Where can I get more info on how this constraint value is generated and
how
> is it used? Also what is the harm leaving the enforcement of constraint
> uncheck?
>
>
>
> Can anyone point me to a website where someone has successfully dealt with
> this issue without manually setting the ranges?
>
>
>
> Please help.
>
> Thanks.
>
>
>
>

• Previous message: Deepak Ramakumar: "Help!!! Merge Replication and identity values"
• In reply to: Deepak Ramakumar: "Help!!! Merge Replication and identity values"
• Messages sorted by: [ date ] [ thread ]