Identity management in Transaction replication with updatable subscription

Question

I have migrated my publisher DB to new server yesterday. For migration what I did was

1. removed subscriptions and publication completely
2. backed up DB on which publication was configured
3. restored DB to new publisher server
4. Ran sp_removedbreplication on newly restored DB to give a clean start
5. recreated publication and subscribers on my respective servers

I was able to setup replication properly and it came in sync normally. But when user tried to insert new records at subscriber I got primary key violation errors for lot of tables. Primary key is on identity columns for most of my tables and have not for replication true on both publisher and subscriber end. Upon checking the identity check constraint on subscriber I saw that it had old ranges than my current max values. It wasn't getting the new ranges even though it had automatic identity range management set. I had to manually RESEED the identity on subscriber to make it work and had to go through manual process of reseeding it for all tables. My environment is :

Replication type: Transaction replication with updatable subscription with immediate updating

1. 1 publisher -- SQL server 2008 R2 + SP2
2. 1 distributor -- SQL server 2008 R2 + SP2
3. 2 subscribers -- SQL server 2008 R2 + SP2

I have below questions:

1. What could be the reason for automatic identity management not working?
2. How to fix this problem permanently? Is there a script available which can check my max values on publisher and subscribers and then RESEED it accordingly?
3. What is the fastest way to fix this?

Answer 1

My solution for your problem is skip the primary key error in the replication.

Through the SQL Monitor create a Agent Profile with the code 2627 and let the primary keys error pass. After that the replication should be normal again.

When you create and mark the agent profile you will need stop and run the subscriber job.