SQL Server Performance

Primary keys not replicated in transactional repli

Discussion in 'SQL Server 2005 General DBA Questions' started by satya.sqldba, Jan 19, 2007.

  1. satya.sqldba New Member

    Hello DBA's

    While setting up transactional replication I found that the primary keys were not replicated onto the other side. Is there any option to be enabled to do so?

    Thanks
    Satya
  2. dineshasanka Moderator

    By default all fields will be replciated to the other end. you should explicitly mention if you don't want to tranfer.

    I have a question, are your primary keys values changing ?

    ----------------------------------------
    Contributing Editor, Writer & Forums Moderator
    http://www.SQL-Server-Performance.Com

    Visit my Blog at
    http://dineshasanka.spaces.live.com/
  3. satya.sqldba New Member

    No

    Is it related to that? Because the first time the replication is setup between that pair of publication/subscription. After the snaphot agent is run I checked for the primary keys and they are not there

    Also you said we can specify if we donot want to, where should we do that if we donot want to replicate primary keys.

    Thanks
    Satya
  4. satya.sqldba New Member

    Oh Strange,

    I came to know only today that primary keys are not at all replicated onto the subscriber side. It is only the indexes that are replicated. If I am correct, we have no other option than manually creating the primary and foreign keys on the subscriber side after the snapshot is generated(if we want them on the other side)

    Thanks
    Satya
  5. madhuottapalam New Member

    is these primarykey identity columns? is this SQL Server 2005 ?

    Madhu
  6. satya.sqldba New Member

    Yes Madhu

    This is SQL Server 2005, and not all PK's are on identity columns

    Thanks
    Satya
  7. madhuottapalam New Member

    i think what you are mentioning is PrimaryKey Constraint not primarykey columns right?. In transactional replication all PK and FK constraints are also replicated. you don't need to create it again at the subscriber

    Madhu
  8. satya.sqldba New Member

    Yes Madhu

    I am talking about the contraints. We have trasactional replication implemented, but I don't see the constraints on the subscriber after the replication is setup. I tried doing this more than twice, but the result is the same. I dont find the constraints on the subscriber.

    Thanks
    Satya
  9. madhuottapalam New Member

    check the schema options for this articles. Use sp_addarticle Schema_options. Read sp_addarticle in BOL.

    From BOL

    0x80 Replicates primary key constraints. Any indexes related to the constraint are also
    replicated, even if options 0x10 and 0x40 are not enabled


    Madhu

  10. MohammedU New Member

    Go to article properties by clicking...
    RightClick publication/Properties/Articles/ArticleProperties/Hilight article/set highlited article properties/
    You need to change the properties in the article properties window... by defaul only "Copy clustered index" is enabled...You may need to enable some other options... take a look...

    Mohammed U.
  11. satya.sqldba New Member

    Got it

    Now its clear

    Thanks
    Satya
  12. madhuottapalam New Member

    what was the problem ? could u pse share with us
  13. satya.sqldba New Member

    Mohammed

    Thanks for explaining. I knew that clustered index would be copied by default, but now I knew that we can copy the constaints by using the schema options mentioned by Madhu.


    Thanks for the explanation Madhu and Mohammed.
    Satya
  14. MohammedU New Member

    Any time<img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br />Mohammed U.
  15. satya.sqldba New Member

    Madhu

    The problem was that I could not find the primary key constraints on the subscriber side after I generated the snapshot agent. SO I was wondering how could we do that. Now with this schema_options parameter, I guess we can do that

    Thanks
    Satya

Share This Page