How did you init you subscriber? With a backup of publisher?
I have seen that error when replicated transactions are waiting to be picked
up in tran log. To confirm- try to truncate your log at subscriber:
backup log 'dbname' with truncate_only
If cannot trunate because of trans pending repl - run the following on
subscriber
-- REMOVE TRANSACTION IN LOG TO ALLOW FOR TRUNCATE -
EXEC sp_repldone @.xactid = NULL, @.xact_segno = NULL, @.numtrans = 0, @.time =
0, @.reset = 1
That error will have you chasing your tail.
ChrisB MCDBA
MSSQLConsulting.com
"pshroads@.gmail.com" wrote:
> I need to use transactional replication to replicate from a SQL Server
> 2005 machine (publisher) to a SQL Server 2000 machine (subscriber).
> The data will be the same on both servers when I start the replication
> so I will not be initializing using an snapshot. The identity columns
> on both databases are currently not set to 'not for replication'. When
> I create the publication is see that the identity columns are
> automatically set to 'not for replication'. I manually change the
> identity columns on the subscriber to also be 'not for replication'.
> Then I start replication. However the identity columns are not
> replicated with the error:
> Cannot insert explicit value for identity column in table 'tab1' when
> IDENTITY_INSERT is set to OFF. (Source: MSSQLServer, Error number:
> 544)
> As a test I set up the replication between 2005 and 2000 and used a
> snapshot to initialize the subscription. In this case the identity
> values are replicated.
> Any idea why setting the 'not for replication' manually on the 2000
> subscriber doesn't work?
> Thanks!
>
In your sp_MSupdxxx stored procedure - comment out the update of your ident
column.
Chris
"pshroads@.gmail.com" wrote:
> Hi Chris,
> Thanks for your reply. Here's a little background:
> We are doing a side-by-side upgrade from SQL Server 2000 to 2005 and
> after the upgrade I want to be replicate from 2005 back to 2000 in
> case we need to roll back to 2000 after our site is live and data is
> changing. I am log shipping from 2000 to 2005. When we are ready to
> cut over I will stop log shipping and the databases will be identical
> on both 2000 and 2005. So that's why there is no initial sync at the
> subscriber.
> I tried your suggestion but it still didn't work. I even tried
> manually creating and populating a test table on both the publisher
> and subscriber and it sill doesn't work. It seems the only way that it
> will work is to initialize the subscriber with a snapshot which with a
> 600GB database seems like it will take a long time.
>
Showing posts with label replication. Show all posts
Showing posts with label replication. Show all posts
Friday, March 23, 2012
Wednesday, March 21, 2012
Identity values not replicated even though 'not for replication' i
How did you init you subscriber? With a backup of publisher?
I have seen that error when replicated transactions are waiting to be picked
up in tran log. To confirm- try to truncate your log at subscriber:
backup log 'dbname' with truncate_only
If cannot trunate because of trans pending repl - run the following on
subscriber
-- REMOVE TRANSACTION IN LOG TO ALLOW FOR TRUNCATE -
EXEC sp_repldone @.xactid = NULL, @.xact_segno = NULL, @.numtrans = 0, @.time =
0, @.reset = 1
That error will have you chasing your tail.
ChrisB MCDBA
MSSQLConsulting.com
"pshroads@.gmail.com" wrote:
> I need to use transactional replication to replicate from a SQL Server
> 2005 machine (publisher) to a SQL Server 2000 machine (subscriber).
> The data will be the same on both servers when I start the replication
> so I will not be initializing using an snapshot. The identity columns
> on both databases are currently not set to 'not for replication'. When
> I create the publication is see that the identity columns are
> automatically set to 'not for replication'. I manually change the
> identity columns on the subscriber to also be 'not for replication'.
> Then I start replication. However the identity columns are not
> replicated with the error:
> Cannot insert explicit value for identity column in table 'tab1' when
> IDENTITY_INSERT is set to OFF. (Source: MSSQLServer, Error number:
> 544)
> As a test I set up the replication between 2005 and 2000 and used a
> snapshot to initialize the subscription. In this case the identity
> values are replicated.
> Any idea why setting the 'not for replication' manually on the 2000
> subscriber doesn't work?
> Thanks!
>
In your sp_MSupdxxx stored procedure - comment out the update of your ident
column.
Chris
"pshroads@.gmail.com" wrote:
> Hi Chris,
> Thanks for your reply. Here's a little background:
> We are doing a side-by-side upgrade from SQL Server 2000 to 2005 and
> after the upgrade I want to be replicate from 2005 back to 2000 in
> case we need to roll back to 2000 after our site is live and data is
> changing. I am log shipping from 2000 to 2005. When we are ready to
> cut over I will stop log shipping and the databases will be identical
> on both 2000 and 2005. So that's why there is no initial sync at the
> subscriber.
> I tried your suggestion but it still didn't work. I even tried
> manually creating and populating a test table on both the publisher
> and subscriber and it sill doesn't work. It seems the only way that it
> will work is to initialize the subscriber with a snapshot which with a
> 600GB database seems like it will take a long time.
>
I have seen that error when replicated transactions are waiting to be picked
up in tran log. To confirm- try to truncate your log at subscriber:
backup log 'dbname' with truncate_only
If cannot trunate because of trans pending repl - run the following on
subscriber
-- REMOVE TRANSACTION IN LOG TO ALLOW FOR TRUNCATE -
EXEC sp_repldone @.xactid = NULL, @.xact_segno = NULL, @.numtrans = 0, @.time =
0, @.reset = 1
That error will have you chasing your tail.
ChrisB MCDBA
MSSQLConsulting.com
"pshroads@.gmail.com" wrote:
> I need to use transactional replication to replicate from a SQL Server
> 2005 machine (publisher) to a SQL Server 2000 machine (subscriber).
> The data will be the same on both servers when I start the replication
> so I will not be initializing using an snapshot. The identity columns
> on both databases are currently not set to 'not for replication'. When
> I create the publication is see that the identity columns are
> automatically set to 'not for replication'. I manually change the
> identity columns on the subscriber to also be 'not for replication'.
> Then I start replication. However the identity columns are not
> replicated with the error:
> Cannot insert explicit value for identity column in table 'tab1' when
> IDENTITY_INSERT is set to OFF. (Source: MSSQLServer, Error number:
> 544)
> As a test I set up the replication between 2005 and 2000 and used a
> snapshot to initialize the subscription. In this case the identity
> values are replicated.
> Any idea why setting the 'not for replication' manually on the 2000
> subscriber doesn't work?
> Thanks!
>
In your sp_MSupdxxx stored procedure - comment out the update of your ident
column.
Chris
"pshroads@.gmail.com" wrote:
> Hi Chris,
> Thanks for your reply. Here's a little background:
> We are doing a side-by-side upgrade from SQL Server 2000 to 2005 and
> after the upgrade I want to be replicate from 2005 back to 2000 in
> case we need to roll back to 2000 after our site is live and data is
> changing. I am log shipping from 2000 to 2005. When we are ready to
> cut over I will stop log shipping and the databases will be identical
> on both 2000 and 2005. So that's why there is no initial sync at the
> subscriber.
> I tried your suggestion but it still didn't work. I even tried
> manually creating and populating a test table on both the publisher
> and subscriber and it sill doesn't work. It seems the only way that it
> will work is to initialize the subscriber with a snapshot which with a
> 600GB database seems like it will take a long time.
>
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
>
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
>
Monday, March 19, 2012
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!
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 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
>
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
>
(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 management with web synchronization
I am using SQL Server 2005 replication with anonymous subscriptions and web
synchronization. The server is SQL 2005 Standard and the subscribers are SQL
2005 Express. Every subscriber syncs with the publisher once an hour. There
are no re-publishers.
The subscription databases each run a client program that works on the local
subset of data (partitioning is done by SUSER_SNAME).
My problem is that periodically the client program will fail with an
Identity Range Check Constraint. The error can be fixed by replicating with
the server.
The way I understand SQL2005 replication with SQL2005 subscribers, each
subscriber should be getting a primary and a secondary range of identity
values. When the primary range runs out they should switch to the secondary
range and refresh the primary range (and vice-versa when the secondary range
runs out).
Alternatively, non-SQL2005 subscribers should request a new identity range
when their identity range threshold is exceeded. I have a subscriber identity
range of 10000 and a threshold of 80%. None of the subscribers have inserted
2000 rows between replications (they typically insert no more than 100 rows).
Please help.
<bump>
Let me try again...
I am using web synchronization for merge replication between SQL 2005 Std
and SQL 2005 Express. Occasionally the subscriber databases use their entire
identity range without requesting a new identity range from the publisher.
They request a new range only after the old range has been completely used.
Does anyone have any ideas how to fix this?
Thanks.
"John Van Vliet" wrote:
> I am using SQL Server 2005 replication with anonymous subscriptions and web
> synchronization. The server is SQL 2005 Standard and the subscribers are SQL
> 2005 Express. Every subscriber syncs with the publisher once an hour. There
> are no re-publishers.
> The subscription databases each run a client program that works on the local
> subset of data (partitioning is done by SUSER_SNAME).
> My problem is that periodically the client program will fail with an
> Identity Range Check Constraint. The error can be fixed by replicating with
> the server.
> The way I understand SQL2005 replication with SQL2005 subscribers, each
> subscriber should be getting a primary and a secondary range of identity
> values. When the primary range runs out they should switch to the secondary
> range and refresh the primary range (and vice-versa when the secondary range
> runs out).
> Alternatively, non-SQL2005 subscribers should request a new identity range
> when their identity range threshold is exceeded. I have a subscriber identity
> range of 10000 and a threshold of 80%. None of the subscribers have inserted
> 2000 rows between replications (they typically insert no more than 100 rows).
> Please help.
synchronization. The server is SQL 2005 Standard and the subscribers are SQL
2005 Express. Every subscriber syncs with the publisher once an hour. There
are no re-publishers.
The subscription databases each run a client program that works on the local
subset of data (partitioning is done by SUSER_SNAME).
My problem is that periodically the client program will fail with an
Identity Range Check Constraint. The error can be fixed by replicating with
the server.
The way I understand SQL2005 replication with SQL2005 subscribers, each
subscriber should be getting a primary and a secondary range of identity
values. When the primary range runs out they should switch to the secondary
range and refresh the primary range (and vice-versa when the secondary range
runs out).
Alternatively, non-SQL2005 subscribers should request a new identity range
when their identity range threshold is exceeded. I have a subscriber identity
range of 10000 and a threshold of 80%. None of the subscribers have inserted
2000 rows between replications (they typically insert no more than 100 rows).
Please help.
<bump>
Let me try again...
I am using web synchronization for merge replication between SQL 2005 Std
and SQL 2005 Express. Occasionally the subscriber databases use their entire
identity range without requesting a new identity range from the publisher.
They request a new range only after the old range has been completely used.
Does anyone have any ideas how to fix this?
Thanks.
"John Van Vliet" wrote:
> I am using SQL Server 2005 replication with anonymous subscriptions and web
> synchronization. The server is SQL 2005 Standard and the subscribers are SQL
> 2005 Express. Every subscriber syncs with the publisher once an hour. There
> are no re-publishers.
> The subscription databases each run a client program that works on the local
> subset of data (partitioning is done by SUSER_SNAME).
> My problem is that periodically the client program will fail with an
> Identity Range Check Constraint. The error can be fixed by replicating with
> the server.
> The way I understand SQL2005 replication with SQL2005 subscribers, each
> subscriber should be getting a primary and a secondary range of identity
> values. When the primary range runs out they should switch to the secondary
> range and refresh the primary range (and vice-versa when the secondary range
> runs out).
> Alternatively, non-SQL2005 subscribers should request a new identity range
> when their identity range threshold is exceeded. I have a subscriber identity
> range of 10000 and a threshold of 80%. None of the subscribers have inserted
> 2000 rows between replications (they typically insert no more than 100 rows).
> Please help.
Identity Range Management
Hello all,
I am just beginning to get into replication and have a couple questions for the experts.
When I set up replication (transactional with queued updates), the default identity range was set to 10,000 for the publisher and 1,000 for the subscriber.I understand that if I try to insert 1,100 records at the subscriber, I will get an error saying that the identity range has been exhausted and I need to run sp_adjustpublisheridentityrange on the publisher and the Distribution Agent for the subscriber.
My questions are:
Why wouldn’t someone increase the identity range to a very large number, just in case something happens and the publisher/subscriber link is broken for an extended period of time?What are the ramifications to a larger identity range?
Since identity range management is done by the distributor, what happens if the distributor goes down?Do the publisher and subscribers receive errors when they fill their current ranges?
Thanks,
Jarret
1. If you set it too large, it's possible to exhaust all possible values. i.e. if you have smallint column with publisher having range of 10,000 and two subscribers with range of say 11,000, and one subscriber hits the threshold, that subscriber cannot get new range since you hit datatype limit even though the 2nd subscriber may not have used any of of their range of identities.
2. If distributor goes down, subscriber inserts will run out of identity values and will most likely fail.
You can read more about this in BOL topic "Replicating Identity Columns", http://msdn2.microsoft.com/en-us/library/ms152543.aspx.
Identity range managed by replication is full and must be updated by a replication agent.
Hello,
You have better luck in microsoft.public.sqlsever.replication. Myself,
I have very little experience of replication.
I'm getting the following error message when I try add a row using a
Stored Procedure.
"The identity range managed by replication is full and must be updated
by a replication agent".
I read up on the subject and have tried the following solutions
according to MSDN without any luck.(http://support.Microsoft.com/kb/
304706 )
sp_adjustpublisheridentityrange (http://msdn2.microsoft.com/en-us/
library/aa239401(SQL.80).aspx ) has no effect
For Testing:
I've reloaded everything from scratch, created the pulications from by
running the sql scripts generated,created replication snapshots and
started the agents.
I've checked the current Identity values in the Agent Table:
DBCC CHECKIDENT ('Agent', NORESEED)
Checking identity information: current identity value '18606', current
column value '18606'.
I check the Table to make sure there will be no conflicts with the
primary key:
SELECT AgentID FROM Agent ORDER BY AgentID DESC
18603 is the largest AgentID in the table.
Using the Table Article Properties in the Publications Properties
Dialog, I can see values of:
Range Size at Publisher: 100,000
Range Size at Subscribers: 100
New range @. percentage: 80
In my mind this means that the Publisher will assign a new range when
the Current Indentity value goes over 80,000?
The Identity range for this table cannot be exhausted! I'm not sure
what to try next.
Please! any insight will be of great help!
Regards,
Bm(miller.brettm@.gmail.com) writes:
Quote:
Originally Posted by
I'm getting the following error message when I try add a row using a
Stored Procedure.
>
"The identity range managed by replication is full and must be updated
by a replication agent".
>
I read up on the subject and have tried the following solutions
according to MSDN without any luck.(http://support.Microsoft.com/kb/
304706 )
>
sp_adjustpublisheridentityrange (http://msdn2.microsoft.com/en-us/
library/aa239401(SQL.80).aspx ) has no effect
You have better luck in microsoft.public.sqlsever.replication. Myself,
I have very little experience of replication.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
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.
>
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.
>
Monday, March 12, 2012
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!
>
>
>
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...
now as i have gotten the bugs out replication seems to work great (2 days and
no more conflicts), but I have a general comment. Microsoft documentation
stinks.
Wheres the Microsoft article on setting up replication, INCLUDING
SCREENSHOTS? Why did the defaults leave me high and dry with respects to
identity fields being primary keys and not reseeding properly. Does it not
make sense to have identity management turned on by default in merge
replication where identity fields are involved? Had this been the case I
would not have even experienced a hiccough in the transition to a replicated
environment. As it was not the case, I called PSS, paid for a support call,
and the agent I spoke with (Terry) never clued me into this fact, he only
helped fix all the primary key troubles (using DBCC CHECKIDENT which is
manually setting the range and manually maintaining it, which I was also not
made aware of). Any support call is a two way street, but I feel MS has made
a great product, poor documentation and in my case caused poor customer
relations.
pUmkInhEd
There are a few articles on the web on setting up replication, but they are
mostly independant. In general, the microsoft documentation doesn't have
screenshots. There are some articles here:
http://www.mssqlcity.com/Articles/Replic/Replic.htm and there is now Hilar'y
book which has loads of graphgics:
http://www.amazon.com/exec/obidos/tg...lance&s=books.
As for the automatic range management being turned on by default, this might
be a good idea in some cases, but I would rather have a prompt. It might be
that you want to use manual range management instead, but either way, to
have a prompt would be enough to make you realize that some type of
intervention was necessary.
You don't have to use dbcc checkident on each subscriber - have a look at
http://www.replicationanswers.com/ManualIdentities.htm for other ways of
manual identity range management. Using dbcc checkident will be ok for
ranges, but more complex algorithms used to ensure no overlap can't be done
this way, because the seed and increment will both need altering.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks for your response Paul, I guess the underlying solution is to educate
myself. Manual range management does not fit my solution since it seems to
require some type of user intervention to keep things going. I appreciate
your input and also your suggested reading material.
Thanks, Brandon
"Paul Ibison" wrote:
> There are a few articles on the web on setting up replication, but they are
> mostly independant. In general, the microsoft documentation doesn't have
> screenshots. There are some articles here:
> http://www.mssqlcity.com/Articles/Replic/Replic.htm and there is now Hilar'y
> book which has loads of graphgics:
> http://www.amazon.com/exec/obidos/tg...lance&s=books.
> As for the automatic range management being turned on by default, this might
> be a good idea in some cases, but I would rather have a prompt. It might be
> that you want to use manual range management instead, but either way, to
> have a prompt would be enough to make you realize that some type of
> intervention was necessary.
> You don't have to use dbcc checkident on each subscriber - have a look at
> http://www.replicationanswers.com/ManualIdentities.htm for other ways of
> manual identity range management. Using dbcc checkident will be ok for
> ranges, but more complex algorithms used to ensure no overlap can't be done
> this way, because the seed and increment will both need altering.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||Manual range management is manual only in the initial
setup, but there again, automatic range management
is 'manua;l' by the same rationale - you have to
configure the form ;)
Admittedly it is simpler to do automatic, and I only
would use manual for nosync initializations.
Good luck,
Paul
>--Original Message--
>Thanks for your response Paul, I guess the underlying
solution is to educate
>myself. Manual range management does not fit my
solution since it seems to
>require some type of user intervention to keep things
going. I appreciate[vbcol=seagreen]
>your input and also your suggested reading material.
>Thanks, Brandon
>"Paul Ibison" wrote:
replication, but they are[vbcol=seagreen]
documentation doesn't have[vbcol=seagreen]
and there is now Hilar'y[vbcol=seagreen]
/0974973602/qid=1107634413/sr=1-1/ref=sr_1_1/103-5571393-
2419806?v=glance&s=books.[vbcol=seagreen]
by default, this might[vbcol=seagreen]
a prompt. It might be[vbcol=seagreen]
but either way, to[vbcol=seagreen]
some type of[vbcol=seagreen]
subscriber - have a look at[vbcol=seagreen]
for other ways of[vbcol=seagreen]
checkident will be ok for[vbcol=seagreen]
overlap can't be done[vbcol=seagreen]
need altering.[vbcol=seagreen]
www.replicationanswers.com
>.
>
no more conflicts), but I have a general comment. Microsoft documentation
stinks.
Wheres the Microsoft article on setting up replication, INCLUDING
SCREENSHOTS? Why did the defaults leave me high and dry with respects to
identity fields being primary keys and not reseeding properly. Does it not
make sense to have identity management turned on by default in merge
replication where identity fields are involved? Had this been the case I
would not have even experienced a hiccough in the transition to a replicated
environment. As it was not the case, I called PSS, paid for a support call,
and the agent I spoke with (Terry) never clued me into this fact, he only
helped fix all the primary key troubles (using DBCC CHECKIDENT which is
manually setting the range and manually maintaining it, which I was also not
made aware of). Any support call is a two way street, but I feel MS has made
a great product, poor documentation and in my case caused poor customer
relations.
pUmkInhEd
There are a few articles on the web on setting up replication, but they are
mostly independant. In general, the microsoft documentation doesn't have
screenshots. There are some articles here:
http://www.mssqlcity.com/Articles/Replic/Replic.htm and there is now Hilar'y
book which has loads of graphgics:
http://www.amazon.com/exec/obidos/tg...lance&s=books.
As for the automatic range management being turned on by default, this might
be a good idea in some cases, but I would rather have a prompt. It might be
that you want to use manual range management instead, but either way, to
have a prompt would be enough to make you realize that some type of
intervention was necessary.
You don't have to use dbcc checkident on each subscriber - have a look at
http://www.replicationanswers.com/ManualIdentities.htm for other ways of
manual identity range management. Using dbcc checkident will be ok for
ranges, but more complex algorithms used to ensure no overlap can't be done
this way, because the seed and increment will both need altering.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks for your response Paul, I guess the underlying solution is to educate
myself. Manual range management does not fit my solution since it seems to
require some type of user intervention to keep things going. I appreciate
your input and also your suggested reading material.
Thanks, Brandon
"Paul Ibison" wrote:
> There are a few articles on the web on setting up replication, but they are
> mostly independant. In general, the microsoft documentation doesn't have
> screenshots. There are some articles here:
> http://www.mssqlcity.com/Articles/Replic/Replic.htm and there is now Hilar'y
> book which has loads of graphgics:
> http://www.amazon.com/exec/obidos/tg...lance&s=books.
> As for the automatic range management being turned on by default, this might
> be a good idea in some cases, but I would rather have a prompt. It might be
> that you want to use manual range management instead, but either way, to
> have a prompt would be enough to make you realize that some type of
> intervention was necessary.
> You don't have to use dbcc checkident on each subscriber - have a look at
> http://www.replicationanswers.com/ManualIdentities.htm for other ways of
> manual identity range management. Using dbcc checkident will be ok for
> ranges, but more complex algorithms used to ensure no overlap can't be done
> this way, because the seed and increment will both need altering.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||Manual range management is manual only in the initial
setup, but there again, automatic range management
is 'manua;l' by the same rationale - you have to
configure the form ;)
Admittedly it is simpler to do automatic, and I only
would use manual for nosync initializations.
Good luck,
Paul
>--Original Message--
>Thanks for your response Paul, I guess the underlying
solution is to educate
>myself. Manual range management does not fit my
solution since it seems to
>require some type of user intervention to keep things
going. I appreciate[vbcol=seagreen]
>your input and also your suggested reading material.
>Thanks, Brandon
>"Paul Ibison" wrote:
replication, but they are[vbcol=seagreen]
documentation doesn't have[vbcol=seagreen]
and there is now Hilar'y[vbcol=seagreen]
/0974973602/qid=1107634413/sr=1-1/ref=sr_1_1/103-5571393-
2419806?v=glance&s=books.[vbcol=seagreen]
by default, this might[vbcol=seagreen]
a prompt. It might be[vbcol=seagreen]
but either way, to[vbcol=seagreen]
some type of[vbcol=seagreen]
subscriber - have a look at[vbcol=seagreen]
for other ways of[vbcol=seagreen]
checkident will be ok for[vbcol=seagreen]
overlap can't be done[vbcol=seagreen]
need altering.[vbcol=seagreen]
www.replicationanswers.com
>.
>
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
>
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
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
Friday, March 9, 2012
Identity in replication
We are going to move Access 2002 database tables to SQL Server 2000. The
current Access tables are replicated. I plan to "unreplicate" them before
converting (otherwise all the repl system fields go over). However, many of
the tables have AutoNumber fields that we want to convert to Identity fields
in SQL. I read an article about Merge Replication (which is our plan) and
they said to set the Identity fields to NOT FOR REPLICATION. Is that true?
It seems the wording is confusing. Thanks.
David
David,
I've seen this setting for so long now that I'd forgotten how unintuitive it
seemed at first. You're right for merge - the correct setting is Yes, Not
for Replication. This means it should function as a normal identity column,
apart from when rows originate from the replication synchronization process,
when effectively identity inserts are allowed. This setting isn't needed for
transactional or snapshot - just Identity - yes, because subscribers can't
change the data. If they are updatable, then the same applies as in merge.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
My concern is with laptop users that will be synching on occasion and
will be creating records in tables with Identity columns. Access
handles this with a random long integer. Does SQL Server do the same?
This is important because I prefer not to have to assign different
unique ID's for each laptop user. Access merely creates a random
negative or positive number. Thanks.
David
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||David,
SQL Server's identity column has a seed and increment - typically 1 and 1.
There's a description in BOL of its behaviour. With this in mind, after you
convert to SQL Server, use DBCC CHECKIDENT to reseed the identity value at
the publisher to be the highest value selected. After that you can use
automatic range management and have ranges allocated to each subscriber.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
current Access tables are replicated. I plan to "unreplicate" them before
converting (otherwise all the repl system fields go over). However, many of
the tables have AutoNumber fields that we want to convert to Identity fields
in SQL. I read an article about Merge Replication (which is our plan) and
they said to set the Identity fields to NOT FOR REPLICATION. Is that true?
It seems the wording is confusing. Thanks.
David
David,
I've seen this setting for so long now that I'd forgotten how unintuitive it
seemed at first
. You're right for merge - the correct setting is Yes, Notfor Replication. This means it should function as a normal identity column,
apart from when rows originate from the replication synchronization process,
when effectively identity inserts are allowed. This setting isn't needed for
transactional or snapshot - just Identity - yes, because subscribers can't
change the data. If they are updatable, then the same applies as in merge.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
My concern is with laptop users that will be synching on occasion and
will be creating records in tables with Identity columns. Access
handles this with a random long integer. Does SQL Server do the same?
This is important because I prefer not to have to assign different
unique ID's for each laptop user. Access merely creates a random
negative or positive number. Thanks.
David
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||David,
SQL Server's identity column has a seed and increment - typically 1 and 1.
There's a description in BOL of its behaviour. With this in mind, after you
convert to SQL Server, use DBCC CHECKIDENT to reseed the identity value at
the publisher to be the highest value selected. After that you can use
automatic range management and have ranges allocated to each subscriber.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Identity Fields
Do ALL identity fields need to be changed to (Not for Replication) before I
create Publisher and Subscribers even if some tables will never be changed
outside of the Publisher? Thanks.
David
David,
this depends on what type of replication you are setting up (and actually
which version of SQL Server as Hilary pointed out to me). Please can you
post back with a little more info.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Sorry. We are planning to use Merge Replication with initially only
replicating to laptops with MSDE and not to a 2nd SQL Server. We are
using SQL Server 2000 with SP4.
Thanks.
David
*** Sent via Developersdex http://www.codecomments.com ***
|||David,
yes - in merge replication, this setting is needed.
Rgds,
Paul Ibison
|||They only need to be set for not for replication if
1) you have more than two nodes in your replication topology (i.e. more than
one subscriber)
2) Inserts occur on both sides between merges
This does capture the majority of merge implementations, but not all of
them.
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
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23tTlvK77FHA.1484@.tk2msftngp13.phx.gbl...
> David,
> yes - in merge replication, this setting is needed.
> Rgds,
> Paul Ibison
>
create Publisher and Subscribers even if some tables will never be changed
outside of the Publisher? Thanks.
David
David,
this depends on what type of replication you are setting up (and actually
which version of SQL Server as Hilary pointed out to me). Please can you
post back with a little more info.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Sorry. We are planning to use Merge Replication with initially only
replicating to laptops with MSDE and not to a 2nd SQL Server. We are
using SQL Server 2000 with SP4.
Thanks.
David
*** Sent via Developersdex http://www.codecomments.com ***
|||David,
yes - in merge replication, this setting is needed.
Rgds,
Paul Ibison
|||They only need to be set for not for replication if
1) you have more than two nodes in your replication topology (i.e. more than
one subscriber)
2) Inserts occur on both sides between merges
This does capture the majority of merge implementations, but not all of
them.
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
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23tTlvK77FHA.1484@.tk2msftngp13.phx.gbl...
> David,
> yes - in merge replication, this setting is needed.
> Rgds,
> Paul Ibison
>
Identity error in replication
I have replication set up from server A to server B. One of the tables used
in publication has an identity column but is set up for (NOT FOR
REPLICATION) . I also have the same setup on subscriber too. Am I getting
this error due to that.
{CALL sp_MSupd_table
(NULL,NULL,NULL,NULL,'N',NULL,NULL,NULL,NULL,NULL, NULL,NULL,NULL,NULL,NULL,2005-
02-25 00:00:00.000,57502,0x1080)}
Should I have NFR turned off on the identity column in subscriber? If so
will it create any duplicate ids at subscriber end? Any solution will be
greatly appreciated.
TIA
Message posted via http://www.sqlmonster.com
From the name of the sp, it appears you are using transactional replication.
Is this TR with updating subscribers or non-updating. Normally in either
case there shouldn't be an identity column on the subscriber. If the
subscriber is updating with a queue set up, then thye identity column is
needed and you'll need to ensure the identity ranges don't overlap - either
using automatic or manual range management.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||You are right we do have transactional replication set up. In this case we
do have the subscriber as updatable which is why we need the identity
turned on for the id columns. If so why is that we are getting this error
and how do we fix this?
Message posted via http://www.sqlmonster.com
|||If the subscriber is updatable, you do not need the identity attribute, and
it shouldn't be there. The publisher allocates the identity value using 2PC
distributed transactions. The subscriber just has a normal int column.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
in publication has an identity column but is set up for (NOT FOR
REPLICATION) . I also have the same setup on subscriber too. Am I getting
this error due to that.
{CALL sp_MSupd_table
(NULL,NULL,NULL,NULL,'N',NULL,NULL,NULL,NULL,NULL, NULL,NULL,NULL,NULL,NULL,2005-
02-25 00:00:00.000,57502,0x1080)}
Should I have NFR turned off on the identity column in subscriber? If so
will it create any duplicate ids at subscriber end? Any solution will be
greatly appreciated.
TIA
Message posted via http://www.sqlmonster.com
From the name of the sp, it appears you are using transactional replication.
Is this TR with updating subscribers or non-updating. Normally in either
case there shouldn't be an identity column on the subscriber. If the
subscriber is updating with a queue set up, then thye identity column is
needed and you'll need to ensure the identity ranges don't overlap - either
using automatic or manual range management.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||You are right we do have transactional replication set up. In this case we
do have the subscriber as updatable which is why we need the identity
turned on for the id columns. If so why is that we are getting this error
and how do we fix this?
Message posted via http://www.sqlmonster.com
|||If the subscriber is updatable, you do not need the identity attribute, and
it shouldn't be there. The publisher allocates the identity value using 2PC
distributed transactions. The subscriber just has a normal int column.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Subscribe to:
Posts (Atom)