Sql-server – SQL Server Replication changing a tables ID numbers

identitymerge-replicationsql server

I am using a merge replication setup for our ERP system and I encountered this problem:

While creating a new entry on a table and with the last ID number given was 25, then new entry got the ID number 40000+ (I don't quite remember the exact number). I found a way to solve it with this query:

DBCC CHECKIDENT('tableName', RESEED, NEW_RESEED_VALUE)

but I wasn't sure if using this would be ok with the replication. I know that replication uses an ID pool range for each system connected to the publication. Anyway after restarting the System the ID seed was fixed.

What I want to ask is if there is a safe way to change the ID range for a whole table if needed while using replication.

Best Answer

You need to turn no 'NOT FOR REPLICATION' on the field, then the target will take the identity field from the source table rather than trying to generate its own

this can be done by:

alter table dbo.testtable
alter column [id] add NOT FOR REPLICATION

NOTE, I'm not sure what doing this while replication is enabled will do, if you do enable it while replication is on, ensure your replicate schema changes setting is on for that publication

Related Question