SQL Server Performance Forum – Threads Archive
Primary keys not replicated in transactional repli
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? ThanksSatya
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/
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
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
is these primarykey identity columns? is this SQL Server 2005 ? Madhu
Yes Madhu This is SQL Server 2005, and not all PK’s are on identity columns Thanks
Satya
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
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
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
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.
Got it Now its clear Thanks
Satya
what was the problem ? could u pse share with us
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
Any time<img src=’/community/emoticons/emotion-1.gif’ alt=’
‘ /><br /><br />Mohammed U.
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
]]>