SQL Server Performance

Not for replication clause for Identity field

Discussion in 'SQL Server 2005 Replication' started by WingSzeto, Jan 29, 2009.

  1. WingSzeto Member

    We have a transactional push replication (so it is only one direction changes from publisher to subscriber). For all the tables that have identifty field in the publisher, is it true that it is a Must-do for using the 'Not for replication' clause with the identity field?
    Currently we have some tables having that clause and some don't. Our replication is working right now I think it is because the tables that has the identity field in publisher don't have the identity property in the subscriber. With that in mind, should i still apply the clause to the missing ones? If I go ahead to apply the 'Not for replication' clause, will that cause any problem?
    Furthermore, please clarify with me the following situation if I want to make sure the identify field won't cause any problem during replication.
    1. if the 'Not for replication' is not defined in the publiser table which has an identity column, the corresponding subscriber table MUST not have identify property tuned on. If it is turned on, I need to make sure the 'Not for replication' clause defined for those tables in subscriber.
    2. if the 'Not for replication' is defined for an identity column in the publiser table, the corresponding table in the subscriber table can have either one of the options.
    2.1 it can have an identify defined but the 'not for replication' clause has to be there.
    2.2 it can't have an identify property turned on.
    3. Since our push transaction is one direction (from publisher to subscriber), is it true that the 'Not for replication' is not important in publisher tables It is more important for me to make sure the corresponding tables in subcriber have the 'not for replication' clause if the identify property is turned.
    Any pointers on this are very much appreciated.
    Wingman
  2. Elisabeth Redei New Member

    Hi Wingman,
    I always thought the name of the concept,"NOT FOR REPLICATION", made it more confusing than necessary but is really quite simple. If this is set, it means that constraints, triggers or IDENTITY are treated differently when a Repliction Agent touches the table. They are enforced (or triggered) when a user does an INSERT/UPDATE/DELETE, but not if a Replication Agent makes an INSERT/UPDATE/DELETE.
    With IDENTITY, if a new row is replicated from the Publisher to the Subscriber, you do not want the IDENTITY value to be incremented when the row is inserted at the Subscriber (or vice versa), you want the value to be inserted as it is.
    In your case, because you have a one-way replication (Publiser to Subscriber) you
    - do NOT need to set NOT FOR REPLICATION on the Publisher because no INSERTs are going to be made by a Replication Agent (it is a one-way replicaton)
    - obviously you do NOT need it on the subscribers because they have not been defined with the IDENTITY property (if they were defined with the IDENTITY property set, you would set NOT FOR REPLICATION at the Subscriber).
    It is quite well described in the following two chapters in BoL:
    "Replicating Identity Columns" and "Controlling Constraints, Identities, and Triggers with NOT FOR REPLICATION".
    HTH
    /Elisabeth
  3. WingSzeto Member

    Elisabeth,
    Thanks for the explanation. Is there a way for me to set the 'Not for replication' clause to 'Yes' on all the tables with Identity field all at once? Right now the only way I know how is to do it in the SQL management tool but I have to do it table by table and the table will be dropped and recreated behind the scene which is not good for many big tables that we have.
    wingman
  4. Elisabeth Redei New Member

    Hi Wingman,
    You can do it when you set up Replication for all tables at once but I guess you have already set up and intitialized your replication? You can add the constraint with a script:ALTER TABLE tblB
    ALTER COLUMN col1
    ADD NOT FOR REPLICATION
    To identify your IDENTITY columns you can use:SELECT name, object_name(object_id) FROM
    sys.syscolumns WHERE status = 0x80 AND id > 100
    /Elisabeth

Share This Page