SQL Server – Impact of Transactional Replication on Table and Column Settings

replicationsql serversql-server-2012

I'm trying to get replication set up – this is my first time ever doing it, so I'm probably making some rookie mistake here. Right now I'm just working in the dev environment. I've been following the example given in this MSDN tutorial.

I've setup the instance to be it's own distributor, then I go to create a publication (which includes all but a few tables, and all but one view). The publication creates just fine, but afterwards, all of the tables and columns have been set to "not for replication"! So of course when I go to the snapshot agent status, I see the error "Explicit value must be specified for identity column in table either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column."

The first time this happened, I figured I had done something stupid, so I rolled back by selecting "Disable Publishing and Distribution", and did some digging. Before I started enabling & setting up replication, all tables are columns had "not for replication" set to "false", which is what I would expect. I redid the publisher & distributor setup, which didn't change anything on the table/column settings. But then I created the publication, and they had all been modified. I undid again, which reverted them all back to 'for replication', then generated but did not run a publication script (with SQL Server Management Studio's GUI), and executed each command one at a time, checking the state of the "not for replication" setting along the way. It's the call to sp_addarticle that changes the setting.

Each article is being added with the option @identityrangemanagementoption = N'manual' and @schema_option = 0x000000000803509F, which translates to options 0x1, 0x2, 0x4, 0x8, 0x10, 0x80, 0x1000, 0x4000, 0x10000, 0x20000, and 0x8000000 (see this MSDN article for what they mean).

The environment is SQL Server 2012 – dev is Developer edition, and the eventual production environment is Standard edition. I'm using this script to check the state of the "not for replication" setting:

SELECT 
    tables.name AS table_name,
    columns.name AS column_name,
    COLUMNPROPERTY(columns.object_id, columns.name, 'IsIdNotForRepl') AS IsIdNotForRepl
FROM sys.tables
    JOIN sys.columns
        ON tables.object_id = columns.object_id
ORDER BY 3 DESC, 1, 2

Why would creating a replication publication actively exclude the subject objects from replication, and how do I change this behavior?

Best Answer

Not sure if you have checked this; but there is an option in sp_addarticle called @identityrangemanagementoption. If set to manual, then it "Marks the identity column using NOT FOR REPLICATION". Pls check if this is the case.

Ref : https://msdn.microsoft.com/en-us/library/ms173857(v=sql.110).aspx

Related Question