December 2, 2004 at 9:51 pm
We are merge replicating (MR) one database between two remote servers. Most
of the tables in the database , identity columns are primary key . To ensure
no conflicts of primary key column arise we have set the publisher and the
subsciber servers’ database to generate odd and even identity #s
respectively. We have not experienced any primary key violation in these
tables. We have been running this MR process for over two months now.
Recently we experienced some transactions in one of the tables were rolled
back, due to some time out issues at the server. Subsequently on this table
(Pkey –Identity column) we had experienced primary key violation.
Upon review with DBCC checkindent we noticed that the current identity
value (CIV) and current column value (CCV) were not same. We run the DBCC
checkindent (tablename, reseed) to make these values look reset (CIV) and
(CCV). After this there were no primary key violation error.
Now our questions
a) What is the reason for the CCV and CIV does not match
b) Do we need to routinely check the tables which have identify column so
the values are same (CIV) and CCV
c) Is there any DBCC procedure to make these CCV and CIV values similar
Thanks
Viji
December 6, 2004 at 10:08 am
Okay, the odd vs. even numbers thing sounds like it's prone to failure. When transactions are rolled back, the identity number reserved by that failed new entry is still filled, so the system will go to the next available number which will probably be an odd number (if it was even before), or vice-versa. If you select a max(value) on the column, it'll reflect the value for the last entry that was successfully inserted. That stuff aside, why not just use identity ranges? I think that'll solve your problem here. Identity ranges are available in a merge replication scheme.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply