September 28, 2009 at 10:31 am
The Issue/facts
I have a replicated system 2 nodes running transactional replication on 2005
The tables in the publication all contain an identity column.
Replication is being used as a disaster recovery mechanism
When a DR test was performed and the app tried to then make an insert into the "Subscriber" the identity became an issue.
The seed was off and tried to give a similar number as what was already in the table on the subscriber.
The identity range management feature is currently set to "manual"
Proposed Solution
Keep the identity range management feature is set to "manual"
To create a SSIS package which loops through all table with an identity value
Select the max identity from the table and put it into a variable (@id) as well as the tablename (@table)
Perform a DBCC CHECKIDENT(@table, reseed, @id+1)
go to the next table
When the DR occurs the package could then be ran to reset the identity values at the subscriber to allow the seed to flow without trying to insert a record with an inconsistent identity value.
My Questions
Is there a better way of handling this possibly with the identity range management feature is set to "auto"?
Have you done anything else in the past that is a better option?
September 29, 2009 at 3:09 am
I would turn the subscriber identity field off. It should then just pass the ID down as it is.
This is if I have understood your issue. 😉
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply