none
restrictions on source tables that are replicated RRS feed

  • Question

  • Hi we run 2017 standard.  Our prod tables are replicated to a test environment. I've heard different things about replication including (maybe) the requirement for a primary key.  Are there any gotchas in a replication relationship?  Especially on the source tables in terms of performance issues, constraints etc?  I heard a rumor that deadlocks can occur when sql server replication is happening.
    • Edited by db042190 Wednesday, August 14, 2019 4:17 PM spelling
    Tuesday, August 13, 2019 1:41 PM

Answers

  • Hi db042190,

     

    Transactional replication requires a primary key constraint on each published table. Snapshot replication does not require a primary key.

    By default, primary key constraints, indexes, and check constraints are replicated to Subscribers. The NOT FOR REPLICATION option is specified by default for foreign key constraints and check constraints;.

     

    Have you configured replication? Don't be intimidated by rumors before you configure it.

     

    Best regards,

    Dedmon Dai


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by db042190 Thursday, August 15, 2019 4:50 PM
    Wednesday, August 14, 2019 6:26 AM

All replies

  • Hi db042190,

     

    Transactional replication requires a primary key constraint on each published table. Snapshot replication does not require a primary key.

    By default, primary key constraints, indexes, and check constraints are replicated to Subscribers. The NOT FOR REPLICATION option is specified by default for foreign key constraints and check constraints;.

     

    Have you configured replication? Don't be intimidated by rumors before you configure it.

     

    Best regards,

    Dedmon Dai


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by db042190 Thursday, August 15, 2019 4:50 PM
    Wednesday, August 14, 2019 6:26 AM
  • haven't but have started working at a place that has them. And wondering if there are performance implications etc. For example, I heard that one of the etl products they tried had its share of deadlocks while replication was "happening" but the newer etl product does not have the same problem.
    Wednesday, August 14, 2019 4:16 PM