SQL Server – How Identity Columns Get Replicated

identityreplicationsql serverssms

During Transactional Replication, we have data going from Server A to Server B.

Say we were to reseed an Identity Column on a Table on Server A (for whatever reason), how would this affect the data on Server B?

My intuition would say that the data on Server A will use the newly seeded Identity, and that would be sent over to Server B as-is (and Server B would have content using the new Identity), but I'm not sure if this is correct.

Best Answer

It is always tricky when you have Identity column and that is a Primary Key for the table.

Based on my experience dealing with T-Rep and Merge, I would recommend to use NOT FOR REPLICATION to NO. By default it is set to YES. You can do it on sql 2005 and up dynamically (without affecting replication) using sys.sp_identitycolumnforreplication.

If you are not updating the data on subscriber side, you should be fine.

To answer your question :

Say we were to reseed an Identity Column on a Table on Server A (for whatever reason), how would this affect the data on Server B?

Considering, you are not updating the data on subscriber, when you want to reseed, you should always take the max value from the identity column and then reseed the value accordingly.

You can use below sql to find that out :

SELECT  QUOTENAME(SCHEMA_NAME(t.schema_id)) + '.' +  QUOTENAME(t.name) AS TableName, 
        c.name AS ColumnName,
        IDENT_CURRENT(SCHEMA_NAME(t.schema_id)  + '.' + t.name) AS CurrentIdentityValue,
        IDENT_INCR (SCHEMA_NAME(t.schema_id) + '.' + t.name) as Identity_increment
    FROM    sys.columns AS c 
        INNER JOIN  sys.tables AS t ON t.[object_id] = c.[object_id]
        WHERE   c.is_identity = 1 
  -- write here the table name if you want to filter for specific table
    and t.name like '%TABLE_NAME%'

for example consider below customer table :

TableName     ColumnName    CurrentIdentityValue    Identity_increment
[dbo].[customer]    id         7                    1

Now if you want to somehow reseed the identity value, you can run 

dbcc checkident ('customer',reseed,8 )

This means that it will set the current Identity value to 8, so next time any row is inserted into the table, it will have current identity value as 8 (CurrentIdentityValue) + 1(Identity_increment) = 9.

You have to be careful doing this as this can cause identity gaps.

Other things you can do are :

  • Partitioning
  • Manual identity range management solutions using sp_addaritcle with parameters like @identity_range ,@pub_identity_range and @threshold

Read up on :

  1. The identity crisis in replication - by Hilary Cotter
  2. All about “Identity Range Management” - by Chris Skorlinski
Related Question