Identity values is checnges by initial snapshot

Hi, it's me again
We have merge replication setup between three servers, a push to one server
and a pull to the other server. There are many tables which use identity
fields. We are not using identity ranges. All were created with the not for
replication option on the identity declaration. I noticed that after the
initially snapshot is applied, that the first identity value available at
the subscriber is not the seed value, but one greater than the largest value
in the snapshot for that table. To get all the subscribers to generate
identities independent of the publisher, I placed DBCC CheckIdent( table,
ReSeed, 1 ) statements for each table in the post snapshot script. The
system appears to hang trying to reseed the identity field. When I tried to
check the value in Query Analyzer with: DBCC CheckIdent( table, NoReSeed ),
the command ran for over fifteen minutes before I killed it. The identity
values are independent after the snapshot is applied, but can I, how can I
reseed the identity for each subscriber.
Tia,
Paul
Hello Paul,
I have similar implementation that I install at my customers. I have devised
some extensive tools to manage all the identity stuff and work well. One of
them is to create a job that executes the DBCC command. This may apply to
you.
Hope this helps,
Raj
|||> To get all the subscribers to generate
> identities independent of the publisher, I placed DBCC CheckIdent( table,
> ReSeed, 1 ) statements for each table in the post snapshot script.
I do not understand what you were trying to do.. What exactly you expect
from such an approach ? If you have merge replication, and you would change
(somehow) identity seed in table on the subscriber database to 1 (as you
have tried), then inserting a new value into this table would cause a
generation of value 1 in identity column. But that is nonsence, because you
might already have such value. It is absolutely normal that after applying
snapshot seed is changed to "max value + 1".
If you do not want to use SQL server identity range handling, then I would
suggest you to change identity increment to a number of databases involved
in merge replication - if you have publisher and two subscribers, identity
increment should be at least three. Then after aplying snapshot you should
change identity seed to following values :
on publisher = Max value + 1
on publisher = Max value + 2
on publisher = Max value + 3
Of course instead of "Max value" you could use any value bigger than "Max
value".
With such an approach all further inserts into those databases will have
identity values which do not intersect - so any collisions in this area are
almost impossible
Regards,
Kestutis Adomavicius
Consultant
UAB "Baltic Software Solutions"
"PaulW" <MSNewsGroup@.Digi-Sol.com> wrote in message
news:Oebqgo%23GEHA.240@.TK2MSFTNGP12.phx.gbl...
> Hi, it's me again
>
> We have merge replication setup between three servers, a push to one
server
> and a pull to the other server. There are many tables which use identity
> fields. We are not using identity ranges. All were created with the not
for
> replication option on the identity declaration. I noticed that after the
> initially snapshot is applied, that the first identity value available at
> the subscriber is not the seed value, but one greater than the largest
value
> in the snapshot for that table. To get all the subscribers to generate
> identities independent of the publisher, I placed DBCC CheckIdent( table,
> ReSeed, 1 ) statements for each table in the post snapshot script. The
> system appears to hang trying to reseed the identity field. When I tried
to
> check the value in Query Analyzer with: DBCC CheckIdent( table,
NoReSeed ),
> the command ran for over fifteen minutes before I killed it. The identity
> values are independent after the snapshot is applied, but can I, how can I
> reseed the identity for each subscriber.
>
> Tia,
> Paul
>
>
|||Actually Kestutis, you will end up with repeated identities any way unless
you set ranges - which Paul specifically said that he is not doing. This
also makes sense if the identity is matched with some form of server or
location value to make up a unique value.
Ron L
"Kestutis Adomavicius" <kicker@.nospam-mail.lt> wrote in message
news:Ot6qAgLHEHA.3584@.TK2MSFTNGP09.phx.gbl...
table,
> I do not understand what you were trying to do.. What exactly you expect
> from such an approach ? If you have merge replication, and you would
change
> (somehow) identity seed in table on the subscriber database to 1 (as you
> have tried), then inserting a new value into this table would cause a
> generation of value 1 in identity column. But that is nonsence, because
you
> might already have such value. It is absolutely normal that after applying
> snapshot seed is changed to "max value + 1".
> If you do not want to use SQL server identity range handling, then I would
> suggest you to change identity increment to a number of databases involved
> in merge replication - if you have publisher and two subscribers, identity
> increment should be at least three. Then after aplying snapshot you should
> change identity seed to following values :
> on publisher = Max value + 1
> on publisher = Max value + 2
> on publisher = Max value + 3
> Of course instead of "Max value" you could use any value bigger than "Max
> value".
> With such an approach all further inserts into those databases will have
> identity values which do not intersect - so any collisions in this area
are
> almost impossible
> --
> Regards,
> Kestutis Adomavicius
> Consultant
> UAB "Baltic Software Solutions"
> "PaulW" <MSNewsGroup@.Digi-Sol.com> wrote in message
> news:Oebqgo%23GEHA.240@.TK2MSFTNGP12.phx.gbl...
> server
> for
at
> value
table,
> to
> NoReSeed ),
identity
I
>
|||There is a way of avoiding repeated identities, by controlling the increment
rather than the seed:
a.. Machine 1 has a seed of 1 and an increment of 2 (odd, positive
numbers)
b.. Machine 2 has a seed of 2 and an increment of 2 (even, positive
numbers)
For 4 machines:
a.. Machine 1 has a seed of 1 and an increment of 2 (odd, positive
numbers)
b.. Machine 2 has a seed of 2 and an increment of 2 (even, positive
numbers)
c.. Machine 3 has a seed of -1 and an increment of -2 (odd, negative
numbers)
d.. Machine 4 has a seed of -2 and an increment of -2 (even, negative
numbers)
This is Michael Hotek's 'invention' -
http://www.mssqlserver.com/replicati..._identity.asp.
On the site above he has the algorithm for a larger number of machines. If
this is used, you can allocate a massive range and then effectively forget
about it.
Regards,
Paul Ibison
|||Paul
The fact here is that we want them repeated. We want Server1 to have
sequence numbers 1 - ..., Server2 to have sequence numvers 1 - ..., etc.
Ron L
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23DhdlwMHEHA.3528@.TK2MSFTNGP09.phx.gbl...
> There is a way of avoiding repeated identities, by controlling the
increment
> rather than the seed:
> a.. Machine 1 has a seed of 1 and an increment of 2 (odd, positive
> numbers)
> b.. Machine 2 has a seed of 2 and an increment of 2 (even, positive
> numbers)
> For 4 machines:
> a.. Machine 1 has a seed of 1 and an increment of 2 (odd, positive
> numbers)
> b.. Machine 2 has a seed of 2 and an increment of 2 (even, positive
> numbers)
> c.. Machine 3 has a seed of -1 and an increment of -2 (odd, negative
> numbers)
> d.. Machine 4 has a seed of -2 and an increment of -2 (even, negative
> numbers)
> This is Michael Hotek's 'invention' -
> http://www.mssqlserver.com/replicati..._identity.asp.
> On the site above he has the algorithm for a larger number of machines. If
> this is used, you can allocate a massive range and then effectively forget
> about it.
> Regards,
> Paul Ibison
>
|||No, you probably missed the point.. Identity increment is set to a number of
databases participating in transaction and on every database identity
increment is set to a number increased by 1. So identities on different
databases would be following:
Participating databases = Identity increment = 3
DB1 (publisher) - 1, 4, 7, 10, ...
DB2 (subscriber) - 2, 5, 8, 11, ...
DB3 (subscriber) - 4, 6, 9, 12, ...
If for some reason such approach is not suitable for you it does not mean
that it will not work, because it WORKS
Regards,
Kestutis Adomavicius
Consultant
UAB "Baltic Software Solutions"
"Ron Lounsbury" <rlounsbury@.bogusAddress.com> wrote in message
news:eoTXGFMHEHA.2432@.TK2MSFTNGP11.phx.gbl...
> Actually Kestutis, you will end up with repeated identities any way unless
> you set ranges - which Paul specifically said that he is not doing. This
> also makes sense if the identity is matched with some form of server or
> location value to make up a unique value.
> Ron L
>
> "Kestutis Adomavicius" <kicker@.nospam-mail.lt> wrote in message
> news:Ot6qAgLHEHA.3584@.TK2MSFTNGP09.phx.gbl...
> table,
> change
> you
applying
would
involved
identity
should
"Max
> are
identity
not
the
> at
> table,
tried
> identity
can
> I
>
|||What is the point of having identity column (usually primary keys) values
repeated ..?
If this column is not a primary key, then I think you will have to create
not identity column and increment it manually (on insert trigger) on every
database..
Regards,
Kestutis Adomavicius
Consultant
UAB "Baltic Software Solutions"
"Ron Lounsbury" <rlounsbury@.bogusAddress.com> wrote in message
news:eSX$rTNHEHA.3564@.TK2MSFTNGP09.phx.gbl...
> Paul
> The fact here is that we want them repeated. We want Server1 to have
> sequence numbers 1 - ..., Server2 to have sequence numvers 1 - ..., etc.
> Ron L
> "Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
> news:%23DhdlwMHEHA.3528@.TK2MSFTNGP09.phx.gbl...
> increment
If
forget
>
|||Statement "Identity increment is set to a number of databases participating
in transaction and on every database identity increment is set to a number
increased by 1." should be "Identity increment is set to a number of
databases participating in transaction and on every database identity seed
is set to a number increased by 1."
Sorry for mistype
Regards,
Kestutis Adomavicius
Consultant
UAB "Baltic Software Solutions"
"Kestutis Adomavicius" <kicker@.nospam-mail.lt> wrote in message
news:eYHzmyTHEHA.1944@.TK2MSFTNGP11.phx.gbl...
> No, you probably missed the point.. Identity increment is set to a number
of
> databases participating in transaction and on every database identity
> increment is set to a number increased by 1. So identities on different
> databases would be following:
> Participating databases = Identity increment = 3
> DB1 (publisher) - 1, 4, 7, 10, ...
> DB2 (subscriber) - 2, 5, 8, 11, ...
> DB3 (subscriber) - 4, 6, 9, 12, ...
> If for some reason such approach is not suitable for you it does not mean
> that it will not work, because it WORKS
> --
> Regards,
> Kestutis Adomavicius
> Consultant
> UAB "Baltic Software Solutions"
>
|||I have discovered what was happening. There is a bug in SQL Server where a
process "appears" to be non-yielding on scheduler. This is why the post
snapshot script hung. The same script works on a different machine. There is
a fix for the problem, but you have to call Microsoft, an incident. If they
decide it is the bug, they have a fix and credit you the incident. This is
the second hot fix I I needed since installing SP3A.
Thanks,
Paul
"Raj Moloye" <rkmoloye@.hotmail.com> wrote in message
news:eSieV1AHEHA.1432@.TK2MSFTNGP12.phx.gbl...
> Hello Paul,
> I have similar implementation that I install at my customers. I have
devised
> some extensive tools to manage all the identity stuff and work well. One
of
> them is to create a job that executes the DBCC command. This may apply to
> you.
> Hope this helps,
> Raj
>

Identity Seed gone bad in merge replication

Sever: sql 2000
Replication: Merge

Issue:
I am having an issue with my audit table, This table is filled by Triggers on various tables through the database. All triggers are defied with "not for replication"

I have allocated 500k ranges, with 80% threshold to the publisher and subscriber databases for this table. The table only holds 225,000 records.

From time to time I get the following error "The identity range managed by replication is full and must be updated by a replication agent. The INSERT conflict occurred in database 'PublicationName', table 'AuditHistory', column 'AuditID'. Sp_adjustpublisheridentityrange can be called to get a new identity range."

When I looked into the issue yesterday I noticed that the identity range being used by replication was 334300001 -> 334799999, however the maximum value in the table was 334300096, meaning that only 95 records were inserted, which means it is no where near the 80% threshold.

Somehow the identity seed on the AuditHistory table had been changed to 334800104, which is outside the allowable range.

My question is what could cause the identity seed to get set to such a high number??

any thoughts would be great!Identity values are used up even if the insert is rolled back. I doubt you had 490,000 rollbacks, though.|||I agree, we also use hibernate in the weblayer if that makes any difference!

Identity reseed error with merge

Are you using automatic range management? If so, the
process of synchronization will reseed if you achieve a
set % of available values. I'd advise against relying on
this behaving correctly in all circumstances and force
the system to never reseed by allocating a huge range to
each subscriber. An integer datatype can span -2billion
to 2billion or so, so there is not really any need to
ever have such a small range that reseeding occurs (IMHO).
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Thanks but these is not the problem. Perhaps I didn't express correctly.
I know the merge replication automatically reseed when it reaches the
imposed limit, but what happened to me is that this seed has returned to
an anterior value, so with the IDs I get concurrence troubles
replicating. The replication and range automatic asignation works
correctly, but I don't know what I did to make the seed backward to a
previous value. The seed didn't reached its limit.
I changed manually the values of the seed in
Distribution.MSrepl_identity_range, but I want to know in what
circurstances those values are being reinitialized to a previous value.
Thanks
Juan
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||are they reseeded on the publisher, subscriber or both?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Juan a" <juan@.mailinator.com> wrote in message
news:uNotHeU$EHA.4004@.tk2msftngp13.phx.gbl...
>
> Thanks but these is not the problem. Perhaps I didn't express correctly.
> I know the merge replication automatically reseed when it reaches the
> imposed limit, but what happened to me is that this seed has returned to
> an anterior value, so with the IDs I get concurrence troubles
> replicating. The replication and range automatic asignation works
> correctly, but I don't know what I did to make the seed backward to a
> previous value. The seed didn't reached its limit.
> I changed manually the values of the seed in
> Distribution.MSrepl_identity_range, but I want to know in what
> circurstances those values are being reinitialized to a previous value.
> Thanks
> Juan
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
|||They are reinitialized on publisher, but because of automatic range
administration, when the SQLCe synchronize it gets the range asigned
from the publisher, an old range that was used before, and that already
have registers in publisher.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

Identity Ranges

I'm using Merge replication on a database that was designed using integer identity columns for primary keys. When I create a publisher it's great because Sql Server will create rowguid columns for me on most of the tables; actually all but one table.

The problem comes when I try and use identity ranges for the subscribers. As a test I set up a range on one table, it only allowed for 10 in the range with an 80% threshold. I wanted to see what would happen when say my publisher db inserts 11 new rows. Well, I found out that after the 8th new row it wouldn't insert anymore as the range was exceeded and it gave me an error message saying
The identity range managed by replication is full and must be updated by a replication agent.

The question is; which agent needs to run in order for the new range to be assigned to the publisher? I have seen some people talk about an sp_ that can be run, but in a production environment I wont this to be automatic.

My altenative to ranged identies is using guid uniqueidentifiers. See my other post on this!!

regards

GrahamI assume this is SQL Server 2000.

To answer your questions:
Maybe the table on which merge is not adding the rowguid col is because the table already has a rowguid column?

And since you have 80% threashold, it is failing after the 8th row and I assume you have pub_idrange and range values each set to 10.
You can get by this situation by increasing the numbers, so that the probablity of them running out of numbers is less. Say like 10,000 or 100,000.

And the agent to be run when the id range is full is the merge agent. Running the merge agent will refresh the ranges on publisher and subscriber.
On the publisher, you can also run the sp_adjustpublisheridentityrange to refresh the range and that way you dont need to run the merge agent.

But typically in a production scenario, it is recommended that:
1. You have a decent sized value for the publisher and subscriber id range values
2. Merge agent to run frequently. That way the ranges will be refreshed (if needed) when merge agent runs.

identity ranges

We restored out database from a backup. we are using merge replication with
ranged identity supor t on some tables. The restore screwed up the
identities. I found an earlier post on how to fix this. Her's what I've
done.
I ran dbcc checkident(tablenaem) and it returns:
Checking identity information: current identity value '161801', current
column value '176509'.
I then reseed it with
dbcc checkident('Material',reseed)
it then returns
Checking identity information: current identity value '176509', current
column value '176509'.
I then run
sp_help 'material'
the constraint is as follows:
([MaterialID] > 161800 and [MaterialID] < 161900)
I then alter my constraint and rerun
sp_help 'material'
it returns
([MaterialID] > 176509 and [MaterialID] < 176709)
I then run
dbcc checkident('Material')
it returns
Checking identity information: current identity value '176509', current
column value '176509'.
all looks good so far
I then go to msrepl_identity_range
it contains
55879466 176700 100 20 2147483647 80 176700
which is correct.
at this point if I create a publication and add the material table as an
article and look at identity range next starting value is 161900 which is
incorrect.
If I create a snapshot and merge, the
dbcc checkident(tablenaem)
returns the original result
'161801', current column value '176509'.
I've narrowed it down to the case that If I recreate all of the above minus
the merge and run
sp_adjustpublisheridentityrange 'Winship_AWSM'
the identity s reset just as with the merge.
What am I missing?
There must be one more place I need to change the seed value. Since the
publication wizard still showed theold value even when all of the storepd
procedures and tables showed the correct info.
Thanks
did you modify MSrepl_identity_range on the Publisher in the distribution
database?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"mgarner1980" <mgarner@.kbsi.com> wrote in message
news:uGNT01ZgFHA.3940@.tk2msftngp13.phx.gbl...
> We restored out database from a backup. we are using merge replication
with
> ranged identity supor t on some tables. The restore screwed up the
> identities. I found an earlier post on how to fix this. Her's what I've
> done.
> I ran dbcc checkident(tablenaem) and it returns:
> Checking identity information: current identity value '161801', current
> column value '176509'.
> I then reseed it with
> dbcc checkident('Material',reseed)
> it then returns
> Checking identity information: current identity value '176509', current
> column value '176509'.
> I then run
> sp_help 'material'
> the constraint is as follows:
> ([MaterialID] > 161800 and [MaterialID] < 161900)
> I then alter my constraint and rerun
> sp_help 'material'
> it returns
> ([MaterialID] > 176509 and [MaterialID] < 176709)
> I then run
> dbcc checkident('Material')
> it returns
> Checking identity information: current identity value '176509', current
> column value '176509'.
> all looks good so far
> I then go to msrepl_identity_range
> it contains
> 55879466 176700 100 20 2147483647 80 176700
> which is correct.
> at this point if I create a publication and add the material table as an
> article and look at identity range next starting value is 161900 which
is
> incorrect.
> If I create a snapshot and merge, the
> dbcc checkident(tablenaem)
> returns the original result
> '161801', current column value '176509'.
> I've narrowed it down to the case that If I recreate all of the above
minus
> the merge and run
> sp_adjustpublisheridentityrange 'Winship_AWSM'
> the identity s reset just as with the merge.
> What am I missing?
> There must be one more place I need to change the seed value. Since the
> publication wizard still showed theold value even when all of the storepd
> procedures and tables showed the correct info.
> Thanks
>
>
|||yes, the publisher is the distributor as well. the merge is between
sqlserverce (pocketpc's) and sqlserver2000.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:ue5Cy6ZgFHA.1412@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> did you modify MSrepl_identity_range on the Publisher in the distribution
> database?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "mgarner1980" <mgarner@.kbsi.com> wrote in message
> news:uGNT01ZgFHA.3940@.tk2msftngp13.phx.gbl...
> with
an[vbcol=seagreen]
> is
> minus
storepd
>
|||I'm sorry...I misunderstood your question. I did not change itin the
distribution database, I changed it in the database to be replicated. I've
now updated the distribution database and it's working.
Thanks!
"mgarner1980" <mgarner@.kbsi.com> wrote in message
news:uXqDYJagFHA.3940@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> yes, the publisher is the distributor as well. the merge is between
> sqlserverce (pocketpc's) and sqlserver2000.
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:ue5Cy6ZgFHA.1412@.TK2MSFTNGP09.phx.gbl...
distribution[vbcol=seagreen]
I've[vbcol=seagreen]
current[vbcol=seagreen]
current[vbcol=seagreen]
current[vbcol=seagreen]
> an
which[vbcol=seagreen]
the
> storepd
>

Identity ranges

Using merge replication, I've decided to change over to identity ranges
(versus uniqueidentifiers). At the publisher, I'm setting a range on the
tables that would be consistent with the records expected not to be exceeded
in the database. Since these values are for an entire state of consumers, I
set the publisher range to 500,000 for that consumer table.
Three questions:
1. Does the publisher range sound excessive and would I be better off using
the auto-range to up that value or should I err on the high side initially?
2. On the subscriber side (all PPC), the normal range of records that would
be pushed would probably be 3000-5000 and the actual changes made would
probably only amount to about 500 per day per PPC. What would be a safe
range on the subscriber side?
3. Does it really matter how WIDE the range is set?
You really need to consider how long the PPC can go without a sync. Can
they stay disconnected for 5 days, 10, longer? Do some calculating and make
sure that not only the range is wide enough, but that the threshold is low
enough so they receive a new range to cover the next worst case senario.
One thing you can guarantee is that the user are not forced to sync
everyday...they probably will not.
"Earl" wrote:

> Using merge replication, I've decided to change over to identity ranges
> (versus uniqueidentifiers). At the publisher, I'm setting a range on the
> tables that would be consistent with the records expected not to be exceeded
> in the database. Since these values are for an entire state of consumers, I
> set the publisher range to 500,000 for that consumer table.
> Three questions:
> 1. Does the publisher range sound excessive and would I be better off using
> the auto-range to up that value or should I err on the high side initially?
> 2. On the subscriber side (all PPC), the normal range of records that would
> be pushed would probably be 3000-5000 and the actual changes made would
> probably only amount to about 500 per day per PPC. What would be a safe
> range on the subscriber side?
> 3. Does it really matter how WIDE the range is set?
>
>
|||Thanks Brian, those are good thoughts. A couple of followup questions: What
would the criteria be to ensure that the "range is wide enough"? I didn't
really understand about the threshold being "low enough".
"Brian Reuter" <BrianReuter@.discussions.microsoft.com> wrote in message
news:885F4F00-0793-478F-B363-7FCCF97C97C9@.microsoft.com...[vbcol=seagreen]
> You really need to consider how long the PPC can go without a sync. Can
> they stay disconnected for 5 days, 10, longer? Do some calculating and
> make
> sure that not only the range is wide enough, but that the threshold is low
> enough so they receive a new range to cover the next worst case senario.
> One thing you can guarantee is that the user are not forced to sync
> everyday...they probably will not.
>
>
> "Earl" wrote:
|||IMHO best way is to give range of 10.000.000 or even hundred million
and forget about ranges, tresholds etc. forever.
Max identity is something like 9223372036854775807, so why to be
worried about ranges.
Is there any drawback with this approach?
Pagus
On Fri, 12 Nov 2004 10:13:23 -0500, "Earl"
<brikshoe@.newsgroups.nospam> wrote:
[vbcol=seagreen]
>Thanks Brian, those are good thoughts. A couple of followup questions: What
>would the criteria be to ensure that the "range is wide enough"? I didn't
>really understand about the threshold being "low enough".
>"Brian Reuter" <BrianReuter@.discussions.microsoft.com> wrote in message
>news:885F4F00-0793-478F-B363-7FCCF97C97C9@.microsoft.com...
|||FAO Earl - Pagus is talking about BigInts (8 bytes) and
not Ints (4 bytes), where the range is +/-2billion or so.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||I was thinking that range wouldn't work :=)
I want that replication book, can I get that as an ebook too (I didnt' see
anything on the site)?
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:5e3f01c4c999$7e3be410$a601280a@.phx.gbl...
> FAO Earl - Pagus is talking about BigInts (8 bytes) and
> not Ints (4 bytes), where the range is +/-2billion or so.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Earl,
the book is now available - I've read a draft and it looks v.good.
Hilary mentioned something about internet updates, but I don't recall
hearing about it as an ebook. Hopefully he'll see this thread and reply to
you.
Rgds,
Paul Ibison, SQL Server MVP
|||No, ebook. The merge volume may be released as an ebook, but its future is
yet undecided.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:Ot8sppbyEHA.2568@.TK2MSFTNGP11.phx.gbl...
> Earl,
> the book is now available - I've read a draft and it looks v.good.
> Hilary mentioned something about internet updates, but I don't recall
> hearing about it as an ebook. Hopefully he'll see this thread and reply to
> you.
> Rgds,
> Paul Ibison, SQL Server MVP
>

Identity range when rows already exist

I've created a merge publication with automatic range management. Insert
fails because the ranges assigned have already been used. How do I specify
that I want the new identity ranges to start above those which have already
been used?
I created this publication by backing up my production database and
restoring it to my test database. Then I created the publication on my test
database by manually editing the auto-generated script for creating the
publication on the production database. I don't know if this is the reason
things don't work out as I want them to.
I think you would be best to drop this publication and its subscriptions and
recreate from start.
If you are a masochist you can do the following.
Look in your distributor for a table called MSrepl_identity_range. The
highest range is the range which is deployed to one of your subscribers. You
can bump this value up to give yourself a cushion.
For instance if the highest range is 10000, bump it up to 20000, which will
be the next value assigned.
Now go to your problem subscriber and fix the table there. Use dbcc
checkident('tablename') to determine what the current range is, and then
reseed to the value you found on your publisher's distribution database
MSrepl_identity_range table.
Now issue a sp_help 'problemTableName' to get the name of the check
constraint used to restrict the range of possible values acceptable for this
table. script out the check constraint and recreate it with a set of values
which matches the range you assigned with the checkident reseed statement.
If you are really feeling like punishing yourself you might want to read
http://www.simple-talk.com/2005/07/05/replication/
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Daniel" <daXniel_kriXstensXen_@.hotmail.com (remove the Xs)> wrote in
message news:92B2698A-DE7C-485C-9381-78295E49A335@.microsoft.com...
> I've created a merge publication with automatic range management. Insert
> fails because the ranges assigned have already been used. How do I specify
> that I want the new identity ranges to start above those which have
already
> been used?
> I created this publication by backing up my production database and
> restoring it to my test database. Then I created the publication on my
test
> database by manually editing the auto-generated script for creating the
> publication on the production database. I don't know if this is the reason
> things don't work out as I want them to.
|||"Hilary Cotter" wrote:

> I think you would be best to drop this publication and its subscriptions and
> recreate from start.
I already did that. Perhaps the problem was that I created the publication
using the script generated by EM. For each merge article it did:
exec sp_addmergearticle ... @.article = [tableName] ...
go
To solve the problem, for all merge articles for which I use automatic range
management I added:
declare @.NewID int
Select @.newID = max(ID)+1 FROM [tableName]
DBCC CHECKIDENT([tableName],RESEED,@.newID)
exec sp_addmergearticle @.article = [tableName] ...
go
That is, I reseed the identity for each table before adding it to the
publication. It seems to work.

>If you are really feeling like punishing yourself you might want to read
> http://www.simple-talk.com/2005/07/05/replication/
Thanks I did that. You got all these great articles scattered all over the
net. But your book about merge replication is due any week now, right? It
would be nice to have the information gathered in one place

Identity Range Management

Good morning All,
In the context of a merge replica:
Does this range serve as surrogate key (Primary Key) but after the sync, the
server overlooks it when inserting new rows in the published table and
generate a sequential one?
Here's what I am trying to achieve:
My disconnected users, have to issue unique File# to their customers.
Once a file number is given to a customer, the customer will use this file
number to reference his/her case for ever.
I want this number to be the unique identifier of his/her record in my table.
If I use this range model, then that would solve the problem provided that
the server will use this number as the primary key for the table where
customer cases are stored.
I read a few articles about the range model but it never read that this
number is kept or used as a primary key in a table.
What am I missing here?
Also
How can we reduce the large skipped numbers not used between sync?
Thanks
YOW
Yes. So the identity column which may or may not be the primary key or may
be one of the columns involved in the primary key could appear on two nodes
simultaneously if Automatic Identity Range Management is not working
correctly. This will lead to a conflict when the merge agent runs and can
cause one of the inserts to be rolled back and replaced by the winning
insert.
If it is working correctly or if there are other columns in the primary key,
or if the identity value is the only key in a primary key or unique index
and you are perfectly partitioned it should work.
Automatic identity range management promotes efficient use of the identity
ranges. If you notice that the ranges are not being used efficiently on the
publisher lower the publisher range. If it is not being used efficiently on
all subscribers, lower that range. If it is not being used efficiently on
one or the subscribers, but is on the other you are out of luck.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Lottoman2000 NEWBE" <Lottoman2000NEWBE@.discussions.microsoft.com> wrote in
message news:C2F1728A-0241-412E-AF9D-CAB3BE2CB04C@.microsoft.com...
> Good morning All,
> In the context of a merge replica:
> Does this range serve as surrogate key (Primary Key) but after the sync,
the
> server overlooks it when inserting new rows in the published table and
> generate a sequential one?
> Here's what I am trying to achieve:
> My disconnected users, have to issue unique File# to their customers.
> Once a file number is given to a customer, the customer will use this file
> number to reference his/her case for ever.
> I want this number to be the unique identifier of his/her record in my
table.
> If I use this range model, then that would solve the problem provided that
> the server will use this number as the primary key for the table where
> customer cases are stored.
> I read a few articles about the range model but it never read that this
> number is kept or used as a primary key in a table.
> What am I missing here?
> Also
> How can we reduce the large skipped numbers not used between sync?
> Thanks
> YOW
|||What do you think if i go for ROWGUID instead?
"Hilary Cotter" wrote:

> Yes. So the identity column which may or may not be the primary key or may
> be one of the columns involved in the primary key could appear on two nodes
> simultaneously if Automatic Identity Range Management is not working
> correctly. This will lead to a conflict when the merge agent runs and can
> cause one of the inserts to be rolled back and replaced by the winning
> insert.
> If it is working correctly or if there are other columns in the primary key,
> or if the identity value is the only key in a primary key or unique index
> and you are perfectly partitioned it should work.
> Automatic identity range management promotes efficient use of the identity
> ranges. If you notice that the ranges are not being used efficiently on the
> publisher lower the publisher range. If it is not being used efficiently on
> all subscribers, lower that range. If it is not being used efficiently on
> one or the subscribers, but is on the other you are out of luck.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Lottoman2000 NEWBE" <Lottoman2000NEWBE@.discussions.microsoft.com> wrote in
> message news:C2F1728A-0241-412E-AF9D-CAB3BE2CB04C@.microsoft.com...
> the
> table.
>
>
|||They tend not to make good PK's. Have a look at this for more info on why
not.
http://www.aspfaq.com/show.asp?id=2504
Its in the bottom section.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Lottoman2000 NEWBE" <Lottoman2000NEWBE@.discussions.microsoft.com> wrote in
message news:8587BCD9-84AB-4719-BFFA-8A7BD607A847@.microsoft.com...[vbcol=seagreen]
> What do you think if i go for ROWGUID instead?
> "Hilary Cotter" wrote:
may[vbcol=seagreen]
nodes[vbcol=seagreen]
can[vbcol=seagreen]
key,[vbcol=seagreen]
index[vbcol=seagreen]
identity[vbcol=seagreen]
the[vbcol=seagreen]
on[vbcol=seagreen]
on[vbcol=seagreen]
in[vbcol=seagreen]
sync,[vbcol=seagreen]
file[vbcol=seagreen]
that[vbcol=seagreen]
this[vbcol=seagreen]
|||From your first answer:
if Automatic Identity Range Management is not working correctly. This will
lead to a conflict when the merge agent runs and can cause one of the
inserts to be rolled back and replaced by the winning insert"
What could make Automated IRM not work properly?
Thanks
"Hilary Cotter" wrote:

> They tend not to make good PK's. Have a look at this for more info on why
> not.
> http://www.aspfaq.com/show.asp?id=2504
> Its in the bottom section.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Lottoman2000 NEWBE" <Lottoman2000NEWBE@.discussions.microsoft.com> wrote in
> message news:8587BCD9-84AB-4719-BFFA-8A7BD607A847@.microsoft.com...
> may
> nodes
> can
> key,
> index
> identity
> the
> on
> on
> in
> sync,
> file
> that
> this
>
>
|||Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Lottoman2000 NEWBE" <Lottoman2000NEWBE@.discussions.microsoft.com> wrote in
message news:37219273-4565-47B0-AB13-1D01132A0658@.microsoft.com...
> From your first answer:
> if Automatic Identity Range Management is not working correctly. This
will[vbcol=seagreen]
> lead to a conflict when the merge agent runs and can cause one of the
> inserts to be rolled back and replaced by the winning insert"
> What could make Automated IRM not work properly?
>
> Thanks
> "Hilary Cotter" wrote:
why[vbcol=seagreen]
in[vbcol=seagreen]
or[vbcol=seagreen]
two[vbcol=seagreen]
and[vbcol=seagreen]
winning[vbcol=seagreen]
primary[vbcol=seagreen]
on[vbcol=seagreen]
efficiently[vbcol=seagreen]
efficiently[vbcol=seagreen]
wrote[vbcol=seagreen]
and[vbcol=seagreen]
customers.[vbcol=seagreen]
this[vbcol=seagreen]
in my[vbcol=seagreen]
provided[vbcol=seagreen]
where[vbcol=seagreen]
|||I'd like to say bugs, but I am not really convinced that there are bugs with
this. Properly sized it does seem to work well - or at least it has worked
well for us on several installations.
If you don't size your data type or your ranges correctly or run the agent
in continuous mode, it might not update the ranges in time. This seems to be
the biggest problem with it.
It also seems that if you are monkeying around with the metadata that the
merge agent will not detect that an article is under automatic range
management when it runs and then check the local and remote ranges. It
normally does this check when it first runs. I have seen cases where the
detection proc never runs, but when I try to repro it on a clean database I
am unable to do so. So I think its something i have messed up along the way.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Lottoman2000 NEWBE" <Lottoman2000NEWBE@.discussions.microsoft.com> wrote in
message news:37219273-4565-47B0-AB13-1D01132A0658@.microsoft.com...
> From your first answer:
> if Automatic Identity Range Management is not working correctly. This
will[vbcol=seagreen]
> lead to a conflict when the merge agent runs and can cause one of the
> inserts to be rolled back and replaced by the winning insert"
> What could make Automated IRM not work properly?
>
> Thanks
> "Hilary Cotter" wrote:
why[vbcol=seagreen]
in[vbcol=seagreen]
or[vbcol=seagreen]
two[vbcol=seagreen]
and[vbcol=seagreen]
winning[vbcol=seagreen]
primary[vbcol=seagreen]
on[vbcol=seagreen]
efficiently[vbcol=seagreen]
efficiently[vbcol=seagreen]
wrote[vbcol=seagreen]
and[vbcol=seagreen]
customers.[vbcol=seagreen]
this[vbcol=seagreen]
in my[vbcol=seagreen]
provided[vbcol=seagreen]
where[vbcol=seagreen]

identity range check constraint

SQL 2005 merge replication.
I took the defaults on an article for publication - auto identity
management and the default ranges. If I attempt to insert >2000 rows
(the size of the 2 ranges assigned), I recieve the following error:
The insert failed. It conflicted with an identity range check
constraint in database 'DHD_73', replicated table
'dbo.tblAlaska_Facility_Manager', column 'facilityID'. 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.
Does this mean I cannot do an insert of more than 2000 rows at a time
to this particular article?
TIA,
john g.
Sorry, I forgot to add, I am doing the inserts on the publisher
jg
jgmein...@.gmail.com wrote:
> SQL 2005 merge replication.
> I took the defaults on an article for publication - auto identity
> management and the default ranges. If I attempt to insert >2000 rows
> (the size of the 2 ranges assigned), I recieve the following error:
> The insert failed. It conflicted with an identity range check
> constraint in database 'DHD_73', replicated table
> 'dbo.tblAlaska_Facility_Manager', column 'facilityID'. 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.
> Does this mean I cannot do an insert of more than 2000 rows at a time
> to this particular article?
> TIA,
> john g.
|||You can batch up the inserts, or increase the size of the assigned rabge,
otherwise you're stuck.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Check the constraint to see what the range is. You can insert up to this
value and then run a sync. This should update the range.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<jgmeinder@.gmail.com> wrote in message
news:1167937361.253647.109410@.51g2000cwl.googlegro ups.com...
> SQL 2005 merge replication.
> I took the defaults on an article for publication - auto identity
> management and the default ranges. If I attempt to insert >2000 rows
> (the size of the 2 ranges assigned), I recieve the following error:
> The insert failed. It conflicted with an identity range check
> constraint in database 'DHD_73', replicated table
> 'dbo.tblAlaska_Facility_Manager', column 'facilityID'. 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.
> Does this mean I cannot do an insert of more than 2000 rows at a time
> to this particular article?
> TIA,
> john g.
>

Identity Primary Field in Merge Replication

Hello I currently have a merge replication set up with 4 subscribers. A primary field for one of my tables is set to a integer indetity.

What Ive noticed is that depending on which database I enter data into, the indentity field (primary key) is set within a certain range I.e

On Server 1 - Values start from 1 then 2,3,4 etc etc

Server 2 - 24001, 24002, 24003 etc etc

Server 3 - 46001, 46002, 46003 etc etc

Server 4 - 68001, 68002, 68003

My question is what happens when these ranges eventually conflict? Do they automatically gain a different range such as 142 001, 142 002 etc etc?

Ive tried looking in SQL Help, and a quick search here, Im just after some confirmation before I implement this to my app.

cheers

I wouldn;t advice you use only this for your primary key.

Check out this post:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1423523&SiteID=1

Identity Primary Field in Merge Replication

Hello I currently have a merge replication set up with 4 subscribers. A primary field for one of my tables is set to a integer indetity.

What Ive noticed is that depending on which database I enter data into, the indentity field (primary key) is set within a certain range I.e

On Server 1 - Values start from 1 then 2,3,4 etc etc

Server 2 - 24001, 24002, 24003 etc etc

Server 3 - 46001, 46002, 46003 etc etc

Server 4 - 68001, 68002, 68003

My question is what happens when these ranges eventually conflict? Do they automatically gain a different range such as 142 001, 142 002 etc etc?

Ive tried looking in SQL Help, and a quick search here, Im just after some confirmation before I implement this to my app.

cheers

I wouldn;t advice you use only this for your primary key.

Check out this post:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1423523&SiteID=1

Identity Not for Replication

Hi,
I am beginner in replication, I use a merge type replication
I have a Publisher on a Sql2000 Server
I have a Distributor on an other Sql2000 Server
And I have subscriber with laptop
I use a pull subscription
My question is I f my database have 1000 tables and I use only 60 tables in
my replication
Do I have to use Not for replication on all Identity column of the 60 tables
or all the 100 tables?
Thank in advance!
You only need to use it on the ones which are truly bi-directional, or where
the inserts will occur on both sides between syncs. For example if all of
your inserts occur on your publisher you will not need it. To be on the safe
side I would put it everywhere.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"GC" <GC@.discussions.microsoft.com> wrote in message
news:78448474-E3B8-4449-8561-63B13AA33025@.microsoft.com...
> Hi,
> I am beginner in replication, I use a merge type replication
> I have a Publisher on a Sql2000 Server
> I have a Distributor on an other Sql2000 Server
> And I have subscriber with laptop
> I use a pull subscription
> My question is I f my database have 1000 tables and I use only 60 tables
> in
> my replication
> Do I have to use Not for replication on all Identity column of the 60
> tables
> or all the 100 tables?
> Thank in advance!
>
>
>

Identity Management

Hi,
I have a merge replication going on. Say, I have a subscriber with an
identity range from 1000 to 1200 and I have only used 100 identities of that
range. For some what reason I have to reinitialise the subscription. What
publisher does is it allocates a new set of range to the table i.e. 1300 -
1400 to the article on subscription. As you can see I have simply wasted 100
identities i.e. from 1100 to 1200. Please note identity allocation is
happening automatically.
Is there a way that I can avoid the wastage?
How can I deduce using system stored procedure that what identities are in
use for each article on each subscriber?
Thanks
Vivek
This is normally not a concern.
Basically replication does not track which identity ranges are in place by
subscribers. All it does is track the last deployed identity range and the
range in use on the publication database.
While I don't recommend it, you can go to the subscriber and do a dbcc
checkident('TableName',reseed,101) and then run a sp_help TableName identify
the replication constraint which limits the identity values which can be
used in the subscriber, and modify it for the range you wish to use.
Again I don't recommend doing this.
You might want to check this link for more on this feature.
http://www.simple-talk.com/2005/07/05/replication/
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Vivek Sharma" <vivek_nz76@.hotmail.com> wrote in message
news:e97gXp$3FHA.476@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I have a merge replication going on. Say, I have a subscriber with an
> identity range from 1000 to 1200 and I have only used 100 identities of
that
> range. For some what reason I have to reinitialise the subscription. What
> publisher does is it allocates a new set of range to the table i.e. 1300 -
> 1400 to the article on subscription. As you can see I have simply wasted
100
> identities i.e. from 1100 to 1200. Please note identity allocation is
> happening automatically.
> Is there a way that I can avoid the wastage?
> How can I deduce using system stored procedure that what identities are in
> use for each article on each subscriber?
> Thanks
> Vivek
>

Identity Issue

hello, Folks

I am having some issue. i am using a merge replication topology. i have a table that uses identity column. when i start the replication I am loosing one record from the subscriber';s end because taht was also have the same column value. To make long story short here is an example:

Office 1 :
uploaded a file in db and that fileID is an Identity column.

Office 2 :
uploads another different typr of file, this time the file ID is also same
because it is generated by SQL Server 2000.

After Replication : I lose my office 2 file because of the conflict.

PLease Help me . Need serious Help

arshal / nipumollahI solved it

on your publication properties -> articles -> change all the identity column's identity range.

works perfectly

nipu

Identity columns and date columns on transactional replication

Hi,

I am planning to use transacational replication (instead of merge replication) on my SQL server 2000. My application is already live and is being used by real users.

How can I ensure that replicated data on different server would have exact same values of identity columns and date columns (where every I set default date to getdate())?

It is very important for me to have a mirror image of data (without using clustering servers).

Any help would be appreciated.

Thanks,

-Niraj

By default, the data should be replicated as it is, regardless the default values. You can look at the article properties to see your options, you also have option to not replicate identity value if you don't want to.