Marking Identity Columns as “Not For Replication” in existing Publication
We encounter a problem today with a Distribution Agent failing with “Row Not Found” when pushing an UPDATE to a Subscriber. Reaching the problem we discovered the Subscriber was created using a Backup\Restore from the Publisher and had Identity Columns. The Identity Columns had “Not For Replication” set to NO, which flags the Distribution Agent to “renumber” (i.e. reseed) data as it is Replication to the Subscriber.
For example: Row 100 inserted at Publisher, but is renumbered by the Distribution Agent to row 20 at the Subscriber. Later same row 100 is edited on the Publisher. The Distribution Agent looks for but is unable to find row 100 and fails with “row not found”. Instead we want the Distribution Agent to use the same value from the Publisher. For this we need to enable the “Not For Replication” setting on the Identity Column on the Publisher table.
If you try and change the Publisher table design while data exists you get error which prevents saving changes. To work around this problem you can use the stored procedure below to change the “Not For Replication” setting for all tables in the database then reinitialize the subscriber.
Change identity setting for all tables in the database:
EXEC sp_msforeachtable @command1 = '
declare @int int
set @int =object_id("?")
EXEC sys.sp_identitycolumnforreplication @int, 1'
Chris Skorlinski
Microsoft SQL Server Escalation Services
I've been aware of this setting for a long time and cannot for the life of me think of a situation where you'd want it to be set to 0. Wouldn't setting it to 1 inevitably lead to data inconsistency at the subscriber?
Actually we have an upgrade process that customizes the subscription replication stored procedures. They occasionally want to have the subscriber manage the identity columns of the table rather than taking the value from the publisher, or possibly because they have added an identity column on the subscriber side that does not exist on the publisher.
Actually we have a situation where this is genuinely used.
We have competition entries that can be entered at both ends of a replicated database. One end is the master and will have all entries, the secondary just has the entries made at that end. In this case the secondary db publishes changes to the master, which re-seeds the identity columns that come down so that they don't conflict with any that are added only at the master.
To be honest though, in this case we could have just skipped the ID column