Dealing with identity ranges for transactional replication

Question

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.

Answer 1

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".

Answer 2

What I ended up doing was sticking with a pull-based transactional replication, and having my program update the subscriber identity values to be the same as those on the publication database immediately after synchronization (kinda what I wish the distribution agent did of its own accord). In pseudo-code it looked a bit like this:

synchronize databases with TransSynchronizationAgent

equivalentTablesNotFound is a list of strings
for each table in publisher tables:
    try:
        check table identity value (this is via functionality provided by .NET's Microsoft.SqlServer.Management.Smo.Server class)
        parse identity value as integer to newIdentity
        if the table's identity value was NULL, skip to next loop iteration
        (HACK) increment newIdentity value by 1
        if there is no subscriber table with the same name as this one:
            record its name in equivalentTablesNotFound and skip to next loop iteration
        set subscriber table with same name's identity value to newIdentity using TSQL: DBCC CHECKIDENT ("tableName", newIdentity)
    catch:
        if exception shows that the error was because the table doesn't have an identity column, drop the exception

if equivalentTablesNotFound has more than zero entries, warn about tables on publisher without an equivalent name on subscriber

Seems to work OK. The HACK bit is because, although by default and with all of my tables, the identity value just increments by one, it can be configured differently, so technically here you should find out how the identity value increments on the publisher table and increment it the same way.

Answer 3

My preferred method for handling this is to do the following:

a. First stop your replication agent (so you're not getting any new data into your subscriber DB)

b. Second rename your existing table

exec sp_rename '[CurrentTable]', '[BackupTableName]'

c. Re-create your table with IDENTITY set

CREATE TABLE [CurrentTable]
(
   ID INT NOT NULL IDENTITY(1,1), 
   OtherField VARCHAR(10) NULL,
   ....
)

d. Backfill your table (from the [BackupTableName]) with SET IDENTITY_INSERT

SET IDENTITY_INSERT [CurrentTable] ON
INSERT INTO [CurrentTable] (ID, OtherField, ...)
SELECT ID, OtherField, ....
FROM [BackupTableName]
SET IDENTITY_INSERT [CurrentTable] OFF

Once you have the IDENTITY constraint on your DB, then you can either do custom replication (ie: change your insert repl proc to SET IDENTITY_INSERT [TableName] ON or you can set the NOT FOR REPLICATION flag on the table (which tells SQL server that if the connecting user is the replication agent, expect the IDENTITY value to be supplied) (I prefer the custom replication approach, as it gives me more flexibility)

e. Modify your insert replication stored procedure (usually named sp_MSins_CurrentTable) to also insert using SET IDENTITY INSERT

ALTER procedure [dbo].[sp_MSins_CurrentTable]
    @c1 int, @c2 varchar(50), ...
as
begin
    /* allow replication to insert values for IDENTITY */
    SET IDENTITY_INSERT [CurrentTable] ON
    insert into [CurrentTable]
        ([ID], [OtherField], ...)
    values
        (@c1, @c2, ...)
    /* now turn off Identity insert */
    SET IDENTITY_INSERT [CurrentTable] OFF
end

f. Now you can restart your replication agent.