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
>
No comments:
Post a Comment