2

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?
  • I think that maybe when you move the distributor the clean up job don't has execute yet. Do you get the id and check if it's exists in the destination? – Krismorte Feb 22 '16 at 11:20
  • Yes, ids do exist in destination. Don't know how clean up job would be related as I recreated replication from scratch. – SQLPRODDBA Feb 22 '16 at 13:13
  • The Clean up job clear the commands that alread in the destination. During the migration you maybe to run this job and now SQL is try to delivering the commands in the queue. – Krismorte Feb 22 '16 at 13:17
  • These are coming after the synch got completed. – SQLPRODDBA Feb 22 '16 at 13:24
  • Really, really strange. – Krismorte Feb 22 '16 at 13:28
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.