Identity columns after failover

Hi,
I was doing some testing of Identity columns with 'Not For Replication'
and failovers. I wanted to test Identity column value ranges. Here is some
back ground info. Both the Publisher and Subscriber have Identity column with
the 'Not For Replication' clause. Both have the same (default) value range
(seed) and increment of 1. On the Publisher, I can insert or add new rows of
data with the new Identity column value being replicated to the standby
successfully. When I break replication and make my subscriber the primary
(and only) server, the first time I insert a new row I get the 'duplicate
key' error. I get this regardless of what tool or application I use. It also
does seem to make a difference if I insert the new row immediately after the
failover or 20 minutes later. However, on the second and all other attempts
to insert a new row it works with no errors what so ever. This is true
regardless if I've set up replication to replicate back to the original
publisher (now the subscriber) or not.
So, my question is, why is this happening? What am I over looking on
failover that gives me the duplicate key error?
Thanks for your replies.
JD
JD,
you'll either need to enable automatic range management with an appropriate
seed, or manually reseed the subscriber. Replication is doing the identity
insert for you, but if you use dbcc checkident you'll see that the seed
value isn't changing, so the first value added after failover has the value
of 1 which was already taken.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
Thank you for the answer.
Joe
"Paul Ibison" wrote:

> JD,
> you'll either need to enable automatic range management with an appropriate
> seed, or manually reseed the subscriber. Replication is doing the identity
> insert for you, but if you use dbcc checkident you'll see that the seed
> value isn't changing, so the first value added after failover has the value
> of 1 which was already taken.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||One more quick question Paul,
Besides the DBCC CHECKIDENT to check and reseed the column, is there a
Stored Procedure that can be used? I heard there was but I haven't found any
supporting documentation.
Thanks again,
JD
"JD" wrote:
[vbcol=seagreen]
> Paul,
> Thank you for the answer.
> Joe
> "Paul Ibison" wrote:
|||To reseed this is the only way. There are things like truncate table
which'll reseed as a side effect, but practically dbcc checkident is the
only way.
Rgds,
Paul Ibison
"JD" <John316@.online.nospam> wrote in message
news:1134F064-2FD4-4DBF-BD18-20ABD6830CAB@.microsoft.com...[vbcol=seagreen]
> One more quick question Paul,
> Besides the DBCC CHECKIDENT to check and reseed the column, is there a
> Stored Procedure that can be used? I heard there was but I haven't found
> any
> supporting documentation.
> Thanks again,
> JD
> "JD" wrote:
|||Once again, Thank you Paul.
"Paul Ibison" wrote:

> To reseed this is the only way. There are things like truncate table
> which'll reseed as a side effect, but practically dbcc checkident is the
> only way.
> Rgds,
> Paul Ibison
>
> "JD" <John316@.online.nospam> wrote in message
> news:1134F064-2FD4-4DBF-BD18-20ABD6830CAB@.microsoft.com...
>
>