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 :
- The identity crisis in replication - by Hilary Cotter
- All about “Identity Range Management” - by Chris Skorlinski
not for replication
toTRUE
for identity column.