Post reply

Primary key in transactional replication

  • May 10, 2006 at 9:04 am

    #168283

    Hi,

    I have a question in Transactional replication.What happends to the primary key when Table with primary key gets replicated to subscriber.Is primary key still exsist in subscrbed table?Any help is appreciated.

    Thanks,

    SR

     

    Thanks,
    SR

  • May 10, 2006 at 9:24 am

    #637030

    Yes, the primary key exists in the Subscriber table. Replication needs the PK in order to retrieve the subscription records to apply the replicated transactions.  You do have other options when creating the intial snapshot, such as bringing over referential integrity, clustered indexes, triggers, extended properties.

  • May 10, 2006 at 9:58 am

    #637040

    Thanks a lot for your reply.I will really appreciate if you can explain me in little detail or guide me to the proper article about how primary keys and identity columns works in transactional replicataion.

    Thanks in advance.

     

    SR

    Thanks,
    SR

  • May 10, 2006 at 11:21 am

    #637063

    When you create the table on the Subscriber, use of this clause for the Identity column is required: NOT FOR REPLICATION

    Indicates that the IDENTITY property should not be enforced when a replication login such as sqlrepl inserts data into the table. Replicated rows must retain the key values assigned in the publishing database; the NOT FOR REPLICATION clause ensures that rows inserted by a replication process are not assigned new identity values. Rows inserted by other logins continue to have new identity values created in the usual way. It is recommended that a CHECK constraint with NOT FOR REPLICATION also be defined to ensure that the identity values assigned are within the range wanted for the current database.

    I've never used the CHECK constraint part of this excerpt from BOL. I didn't need to because I used the proper values when creating the tables on the Publisher and Subscriber. If you are using Bi-directional Replication, you'll need to do it on both sides.

    HTH,

    Mark

  • May 11, 2006 at 5:45 am

    #637193

    A Guide to SQL Server 2000 Transactional & Snapshot Replication by Hillary Cotter is excellent. We have multiple replication streams running with a Central Distributor. Found this book excellent when setting the whole lot up. Regards Derek.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply