Identity column question in transactional replication...

I'm trying to determine if transactional replication would be good to use for creating a standby server...
I understand using "Not For Replication" on publisher/subscriber tables allows insertion of data into identity columns keeping the existing value unchanged. Does the identity value on the subscriber get changed to the last inserted value + 1 for data sen
t from the publisher if I were to insert a record on the subscriber? Or would the identity value be set to the original seed values causing a potentional duplicate key conflict?
Your comments appreciated...
GK,
the identity value is not incremented. You might be able to use DBCC CHECKIDENT. However I have had it fail and BOL says it is not supported for columns having this attribute. If you have a look at Hilary's posts regarding identity columns, you'll find re
ference to a script he uses to change the identity property of columns. You could use this script then apply dbcc checkident on each table.
HTH,
Paul Ibison
|||Paul, thanks for your replay...
BOL states "The NOT FOR REPLICATION option was created specifically for applications using replication. For example, without this option, as soon as the first row from Publisher B (with value 1001) is propagated to Publisher A, Publisher A's next value wo
uld be 1002. The NOT FOR REPLICATION option is a way of telling SQL Server 2000 that the replication process gets a waiver when supplying an explicit value and that the local value should not be reseeded. "
If I interpret the above correctly, NOT using the "not for replication" option at the publisher will change the value at the subscriber which is what I would want to keep consistency in a warm stand by server. Am I correct in my interpretation?
Thanks,
Curtis
"Paul Ibison" wrote:

> GK,
> the identity value is not incremented. You might be able to use DBCC CHECKIDENT. However I have had it fail and BOL says it is not supported for columns having this attribute. If you have a look at Hilary's posts regarding identity columns, you'll find
reference to a script he uses to change the identity property of columns. You could use this script then apply dbcc checkident on each table.
> HTH,
> Paul Ibison
>
|||Curtis,
this isn't quite correct :-)
At the subscriber you have 3 logical choices: No identity, Identity,
Identity (not for replication).
By default the identity property is not sent down at all - the column just
becomes a normal int column (this applies to any identity setting on the
publisher).
NOT FOR REPLICATION on the subscriber allows identity inserts to occur
through the replication process and if there is an Identity property without
NOT FOR REPLICATION on the subscriber, the distribution agent will fail.
If you use NOT FOR REPLICATION on the subscriber and later want to reseed,
you might try DBCC CHECKIDENT, but it is not guaranteed to work. From BOL
"If necessary, DBCC CHECKIDENT corrects the current identity value for a
column. The current identity value is not corrected, however, if the
identity column was created with the NOT FOR REPLICATION clause (in either
the CREATE TABLE or ALTER TABLE statement)."
I would advise Queued updating subscribers or Hilary' script followed by
reseeding.
HTH,
Paul Ibison
"GK_Curtis" <GKCurtis@.discussions.microsoft.com> wrote in message
news:23F41E8F-1D69-475F-8703-54691329586B@.microsoft.com...
> Paul, thanks for your replay...
> BOL states "The NOT FOR REPLICATION option was created specifically for
applications using replication. For example, without this option, as soon as
the first row from Publisher B (with value 1001) is propagated to Publisher
A, Publisher A's next value would be 1002. The NOT FOR REPLICATION option is
a way of telling SQL Server 2000 that the replication process gets a waiver
when supplying an explicit value and that the local value should not be
reseeded. "
> If I interpret the above correctly, NOT using the "not for replication"
option at the publisher will change the value at the subscriber which is
what I would want to keep consistency in a warm stand by server. Am I
correct in my interpretation?[vbcol=seagreen]
> Thanks,
> Curtis
> "Paul Ibison" wrote:
CHECKIDENT. However I have had it fail and BOL says it is not supported for
columns having this attribute. If you have a look at Hilary's posts
regarding identity columns, you'll find reference to a script he uses to
change the identity property of columns. You could use this script then
apply dbcc checkident on each table.[vbcol=seagreen]