|
|
You're in the
sql server replication
group:Help!!! Merge Replication and identity values
sql server replication:
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.
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 [quoted text, click to view] "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. > > > >
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. [quoted text, click to view] Hilary Cotter" <hilaryk@att.net> wrote in message =
news:u$aeWOoUEHA.3420@TK2MSFTNGP12.phx.gbl... > wow! thats a lot of SQL CE databases. >=20 > 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. >=20 > 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. >=20 > 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. >=20 > 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. >=20 > 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. >=20 > 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. >=20 > --=20 > Hilary Cotter > Looking for a book on SQL Server replication? > http://www.nwsu.com/0974973602.html >=20 >=20 > "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 =3D 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. > > > > > > > > >=20
Don't see what you're looking for? Try a search.
|
April 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |