none
dml triggers behaviour on secondary replica RRS feed

  • Question

  • Quick Q: Say on primary replica database has DML triggers on tables which again writes to other tables (writes to audit log history table) when a DML action is performed on principle table - what is the behavior on secondary replica db? 


    Mahesh

    Wednesday, December 10, 2014 3:34 PM

Answers

  • I understand that part on primary replica and transaction log, but what's the behavior of triggers on secondary replica - are they triggered or disabled?

    They aren't triggered, replaying the log doesn't cause anything such as that to happen. There is nothing to cause them to fire, it's not redoing the statement used, it's just redoing the log. Individual statements are not captured by the log, just the changes are reflected.

    -Sean


    The views, opinions, and posts do not reflect those of my company and are solely my own. No warranty, service, or results are expressed or implied.

    • Marked as answer by Mahesh Dasari Wednesday, December 10, 2014 5:40 PM
    Wednesday, December 10, 2014 4:44 PM
    Answerer
  • Unfortunately not. It'll still trickle over from the primary, but it can't be explicitly read from the secondary.


    The views, opinions, and posts do not reflect those of my company and are solely my own. No warranty, service, or results are expressed or implied.

    • Marked as answer by Mahesh Dasari Wednesday, December 10, 2014 9:26 PM
    Wednesday, December 10, 2014 9:06 PM
    Answerer

All replies

  • Hello,

    The behavior is that the transaction logs are replayed and it is an exact copy of the primary. The DML trigger is actioned on the primary, log records are generated, and eventually they flow to the secondary where it is replayed.

    -Sean


    The views, opinions, and posts do not reflect those of my company and are solely my own. No warranty, service, or results are expressed or implied.

    Wednesday, December 10, 2014 3:52 PM
    Answerer
  • I understand that part on primary replica and transaction log, but what's the behavior of triggers on secondary replica - are they triggered or disabled? - if so how, and what happens on failover, etc.  Any link article link that describes these in details.

    Here is the scenario we have main db and auditlog db; the triggers write to auditlog tables for DML action on main db tables. We want to set up Always on HG on main db. So wanted to understand the inter dependencies of DBs, etc for this setup; along triggers behavior. 

    Thanks in adv..


    Mahesh

    Wednesday, December 10, 2014 4:23 PM
  • I understand that part on primary replica and transaction log, but what's the behavior of triggers on secondary replica - are they triggered or disabled?

    They aren't triggered, replaying the log doesn't cause anything such as that to happen. There is nothing to cause them to fire, it's not redoing the statement used, it's just redoing the log. Individual statements are not captured by the log, just the changes are reflected.

    -Sean


    The views, opinions, and posts do not reflect those of my company and are solely my own. No warranty, service, or results are expressed or implied.

    • Marked as answer by Mahesh Dasari Wednesday, December 10, 2014 5:40 PM
    Wednesday, December 10, 2014 4:44 PM
    Answerer
  • Another QQ: Can we setup CDC on secondary DB replica?


    Mahesh

    Wednesday, December 10, 2014 8:20 PM
  • Unfortunately not. It'll still trickle over from the primary, but it can't be explicitly read from the secondary.


    The views, opinions, and posts do not reflect those of my company and are solely my own. No warranty, service, or results are expressed or implied.

    • Marked as answer by Mahesh Dasari Wednesday, December 10, 2014 9:26 PM
    Wednesday, December 10, 2014 9:06 PM
    Answerer