none
Statement Delivery Default Stored Procedures Rather Than SQL Commands

    Frage

  • Reading the MSDN documentation for transactional replication statement delivery it notes " by default, transactional replication propagates changes to subscribers through a set of stored procedures that are installed on each subscriber". I have tried to find reasons to use this approach rather than the SQL option which just replicates the sql commands but have not found any clear benefits to using the proc's rather than the straight SQL commands. I understand the ability to customize the I/U/D changes at the subscriber in the procedures but outside of that if there are no performance benefits, it seems to only add a large number of stored procedures to each subscriber. 

    I would also like to know if anyone by chance knows if the default changed somewhere along the way as SQL has progressed through versions. I see that the default for non-sql subscribers is the SQL commands and wondered if this is how it was in SQL Server at some point.

    Thanks


    • Bearbeitet Abeljdang Montag, 2. Juli 2018 19:57 spelling
    Montag, 2. Juli 2018 19:56

Antworten

  • Using stored procedures to apply the replicated commands has been the default ever since I can remember - which was SQL 6.5.

    The only benefit I am aware of is that you can use this to replicate to heterogeneous subscribers as you point out.  

    • Als Antwort markiert Abeljdang Donnerstag, 5. Juli 2018 18:27
    Dienstag, 3. Juli 2018 13:31
    Moderator

Alle Antworten

  • Hi,

    In your scenario, you want to know why transactional replication propagates changes through a set of stored procedures rather than SQL commands. Right?

    " There are a couple points that will be interesting to power users. The first is that these procedures are executed once per row on each subscriber. If you insert, update, or delete 1000 rows on the publisher, these stored procedures will get called 1000 times. The second is that you can change these procedures manually and as long as you preserve the calling signature (same parameter names and same data types), what you do with the passed data or how you do it is entirely up to you "

    Please refer to following articles, hope it helps:

    http://www.sqlservercentral.com/articles/Replication/3199/

    http://www.sqlservercentral.com/articles/Replication/3202/

    Thanks,
    Xi Jin.


    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.

    Dienstag, 3. Juli 2018 08:18
    Moderator
  • Thanks I have seen andy's articles and I am asking what the benefits of using the sql commands over stored procs as I am aware of the ability to customize the procs on the subscribers.
    Dienstag, 3. Juli 2018 13:26
  • Using stored procedures to apply the replicated commands has been the default ever since I can remember - which was SQL 6.5.

    The only benefit I am aware of is that you can use this to replicate to heterogeneous subscribers as you point out.  

    • Als Antwort markiert Abeljdang Donnerstag, 5. Juli 2018 18:27
    Dienstag, 3. Juli 2018 13:31
    Moderator