Help! Merge Replication and identity values

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_7 F8D53C0B20A". 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.
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_7 F8D53C0B20A". 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.
>
>
|||Hilary,
Thanks a lot for your response. Googling on sp_MSreseed gives only one result by Fiach Reid who details the stored procedure itself. I will not be doing any batch inserts so I guess sql should handle the identity ranges automatically, but if it doesn't then I will start seeing conflicts on the server again and may be by that time I could have gathered more info on sp_MSreseed.
Thanks,
Deepak.
Hilary Cotter" <hilaryk@.att.net> wrote in message news:u$aeWOoUEHA.3420@.TK2MSFTNGP12.phx.gbl...

> 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...
> well
> subscribers,
> have
> value
> to
> updating
> how
>