Sql-server – Dealing with identity ranges for transactional replication

sql serversql-server-2008-r2

I've noticed that when you set up a transactional replication, SQL Server will set identity range management to manual. What this means is that in my subscription database, when I try to insert a new record into a table whose PK is an identity column, it will give me an error and say that it tried to insert a PK of "1", "2", "3", etc. This is because the current identity value for all identity columns on the subscriber gets reset to the seed value (usually 1) instead of staying at what it was on the publisher.

I understand why SQL Server does this – you're supposed to leave the subscriber table as read-only. However, my scenario is a little unorthodox – I update my subscriber from time to time through replication, make an immediate backup of that DB, then I want to do some updates to the subscriber that WON'T be pushed back to the publisher, then when I go to update the subscriber again, I restore its database from the earlier backup and pull the latest updates. Because I want to do updates to the subscriber in between these updates ('temporary deltas' if you will), I need the identity column to work and not to reset to 1 when replicated.

I tried turning on automatic identity range management when setting up my publication, but that just gives me the following error when I try to add a table to the publication:

Msg 21231, Level 16, State 1, Procedure sp_MSrepl_addarticle, Line 2243
Automatic identity range support is useful only for publications that allow updating subscribers.

Is there any way I can get round this problem? I do kind of want to present this replication to SQL Server as if it were read-only at the subscriber end because I don't plan on making updates that will be pushed back to the publisher, but I do want to make temporary updates that will be erased before the next replication.

I have also considered that snapshot replication might be a more appropriate method than transactional replication for my usage pattern, but the trouble is that snapshot replication requires sending the whole darn DB every single update; because I'm planning on taking an immediate backup of the DB after the latest replication, I shouldn't need to do that whole transfer every time; just the changes since last time.

Best Answer

Assuming your Publisher is using an int identity that begins at 1, you could issue DBCC CHECKIDENT('dbo.mytable', RESEED, -2147483648) at the subscriber. You can then use the range from -2147483648 to 0 to hold your "temporary deltas".