9

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.

migrated from stackoverflow.com Jul 2 '13 at 16:40

This question came from our site for professional and enthusiast programmers.

  • What version of SQL Server are you using? Can you redefine the table? – Daniel Gimenez Jul 2 '13 at 12:41
  • 2008 r2. I don't see how redefining the table would solve this problem... – Jez Jul 2 '13 at 13:11
  • I was thinking of a solution using SEQUENCE, but that is only for SQL 2012. – Daniel Gimenez Jul 2 '13 at 13:30
  • 2
    Is there any way I can get round this problem? You have to set identity column as NOT FOR REPLICATION using sys.sp_identitycolumnforreplication for sql server 2005 and up. You even don't have to resnapshot your articles when you change the identity column as not for replication. Just dont do it using GUI. – Kin Shah Jul 2 '13 at 18:07
  • It's already marked as not for replication. That's basically the problem - SQL Server doesn't copy over the identity information so on the subscriber, it starts over at 1. – Jez Jul 2 '13 at 20:27
3

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

  • This is the solution I came up with, but it still means my code connecting to the publisher and subscriber and synchronizing the identities manually. I was hoping there was a more automatic way to do it. – Jez Jul 3 '13 at 16:00
  • Why would you need to synchronize the identities manually? Just write a stored procedure at the subscriber that runs checkident for each table that you are storing temporary delta's in, and run it after the snapshot finishes applying. The distribution agent will insert changes as they happen in the "real" identity range, and the changes made directly to the subscriber will be in the negative range. – Liam Confrey Jul 3 '13 at 20:34
1

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.

0

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.

  • 1
    lol, compared to using DBCC CHECKIDENT, this method is a massive amount of work. – Jez Jul 3 '13 at 16:00
  • @Jez you'll need to recreate the table (with IDENTITY) in order to run the DBCC CHECKIDENT... A snapshot of replication will create the table with out the IDENTITY constraint (based on your q I'd say the DBCC CHECKIDENT won't work) – Andrew Bickerton Jul 3 '13 at 16:32
  • FYI it did work and the replication does create the table with the IDENTITY constraint... – Jez Jul 4 '13 at 8:12
  • @Jez what type of replication did you setup? (if you set it up as MERGE that will happen, for TRANSACTIONAL it usually doesn't, though replication is highly customisable if you don't use the GUI) – Andrew Bickerton Jul 5 '13 at 9:49
  • Transactional. As I said the IDENTITY is there, but the current identity value is reset to the seed value (1). – Jez Jul 5 '13 at 9:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.