none
what is the difference between replication options proc exec and definition only? RRS feed

  • Question

  • I understand proc exec option as for example when you run a procedure which inserts 1000 records into table, those inserts afterwards (after they were inserted in Publisher) will be replicated to the subscriber. When it comes to definition only option, I do not understand how it differentiate from proc exec option. I tested those options on my servers and for me it turned out performing the same thing. I didn't find detailed explanation about these options even on BOL. Could someone explain to me the difference between above two options?

    • Edited by RaufDBA Monday, November 18, 2019 12:55 PM
    Monday, November 18, 2019 12:53 PM

Answers

  • It the table is also replicated a change to this table modified by the stored procedure whose schema is replicated will have the change replicated as it is picked up the log reader agent.

    If the table is also replicated and you are using the replication of a stored procedure execution, the change will be replicated by the stored procedure execution and NOT by the change in the underlying base table.

    If you make a change to the stored procedure and the code is modified the stored procedure code change should be replicated.

    You replicate the execution of a stored procedure as you can modify 1000 rows and only the stored procedure execution will be replicated, not the 1000 changes. This can results in huge performance increases when replicating large batch changes.

    • Marked as answer by RaufDBA Monday, November 18, 2019 8:10 PM
    Monday, November 18, 2019 7:49 PM
    Moderator

All replies

  • When you replicate the execution of a stored procedure the stored procedure is executed on the subscriber(s) whenever it is executed on the publisher. The code of the stored procedure on the subscriber can be completely different than what is on the publisher. 

    When you replicate the schema of the stored procedure only, just the code is applied on all subscribers.

    Monday, November 18, 2019 3:24 PM
    Moderator
  • Thank you for your response. I am trying to figure out an example for definition only option but unfortunately nothing comes to my mind. I would like to know at least one case when we use that option. I tested that by creating a simple stored procedure with definition only option: 

    CREATE PROCEDURE myPROC

    AS

    BEGIN 

    INSERT INTO mytable 

    VALUES (1, 'Max', 'Richter')

    END

    When I ran this procedure on the publisher, the above record is inserted into mytable and according to definition only option the code is replicated into subscriber. Beside code replication, we can see that the above record was also replicated into the corresponding table in the subscriber. It is ok because log reader's responsibility is to replicate the above insert, no problem with that. 

    But when then do we replicate only code change? When is it useful compared with proc exec option?

    Monday, November 18, 2019 7:39 PM
  • It the table is also replicated a change to this table modified by the stored procedure whose schema is replicated will have the change replicated as it is picked up the log reader agent.

    If the table is also replicated and you are using the replication of a stored procedure execution, the change will be replicated by the stored procedure execution and NOT by the change in the underlying base table.

    If you make a change to the stored procedure and the code is modified the stored procedure code change should be replicated.

    You replicate the execution of a stored procedure as you can modify 1000 rows and only the stored procedure execution will be replicated, not the 1000 changes. This can results in huge performance increases when replicating large batch changes.

    • Marked as answer by RaufDBA Monday, November 18, 2019 8:10 PM
    Monday, November 18, 2019 7:49 PM
    Moderator