SQL Server – Identity Column Behavior After Replication

identityreplicationsql servert-sql

Say we have two Servers: Server A and Server B. Data is being replicated (vanilla Transactional Replication) from A to B.

For one of the Tables, we have data as follows:

Server A   - Server B
=========    =========

ID|Content   ID|Content
----------   ----------
1 | ...      1 | ...
2 | ...      2 | ...
  ...         ...
98| ...      98| ...
99| ...      99| ...

After a while, we delete old data:

Server A   - Server B
=========    =========

ID|Content   ID|Content
----------   ----------
50| ...      50| ...
51| ...      51| ...
  ...         ...
98| ...      98| ...
99| ...      99| ...

Now, we changed the order of replication, so now Server B is Replicating to server A.

The issue is:

I Would have expected that new data that is added to Server B (and then replicated to Server A) would continue using the ID column (It would go to 100, 101, 102, …). Instead, I noticed it starts back at 1.

The question is:

How will the DB handle it when the ID finally hits 50? Will the DB just start throwing Primary Key violations, or will it realize what is going on, and jump from ID 49 to ID 100?

If it would start throwing Primary Key violations, is there a command we can use to tell Server B that it should start at ID 100? And if so, can this command be done while it is replicating data?

Best Answer

After switching to a different master you must reseed the identity value, otherwise you will get duplicates in the id column. Use the DBCC CHECKIDENT statement.

DBCC CHECKIDENT ( 'dbo.ITest', reseed, 1000 ) ;

Choosing the number could be automated by using a SELECT MAX( TID ) query and placing the returned number into a dynamic SQL string. This must be placed in a serializable transaction to make sure no other sessions insert new rows while this session is reseeding the value (thanks David). Also, the master must not be changed while reseeding is taking place.

One way to deal with this might be to create non-overlapping identity domains. On ServerA, create the identity column as identity ( 1, 2 ) and on ServerB as identity (2, 2 ). That way ServerA's inserts will be 1, 3, 5… and ServerB's inserts will be 2, 4, 6… Of course, if you ever want to create a third server then you are hosed. :-)

Better still, don't use identity in a multi-master situation. It provides no prevention of duplicates. Use a uniqueidentifier or an application-generated identifier instead.

Related Question