none
SQL AlwaysON config - is it possible to delay the sync of data to a secondary replica ? RRS feed

  • Question

  • In a SQL AlwaysOn Configuration is there anyway to delay the replication to one of the replicas? 

    eg if there are 4 replicas, synchronous commit to 2 secondary and asychronous commit to the other secondary replica but have a delay of 30 minutes or so to that replica?  

    To protect against something like Accidental full update or delete of a table

    thanks 


    ilikefondue

    Thursday, November 21, 2019 10:18 AM

Answers

  • I am not aware of any such feature where you can delay the commit moving of transaction log to secondary replica. You can configure logshipping on AG database such that backup happens every 30 minutes and similarly restore. In this case for accidental DML operations you may get 30 minutes or more windows to recover.

    Other easy way( not recommended just posting it for sake of argument) to do this is choke the bandwidth which is used to send logs BUT this will be major effect on production system, specially if you have Sync replicas. Its widely said that your AG is as fast as your slowest replica and if you make it further slow this will definitely affect database performance and soon you might get disk space related warning on drive where transaction log resides. 

    To avoid accidental deletes use backup and restore or policy of Principal of Least Privilege.

     

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP


    Thursday, November 21, 2019 10:43 AM
    Moderator

All replies

  • I am not aware of any such feature where you can delay the commit moving of transaction log to secondary replica. You can configure logshipping on AG database such that backup happens every 30 minutes and similarly restore. In this case for accidental DML operations you may get 30 minutes or more windows to recover.

    Other easy way( not recommended just posting it for sake of argument) to do this is choke the bandwidth which is used to send logs BUT this will be major effect on production system, specially if you have Sync replicas. Its widely said that your AG is as fast as your slowest replica and if you make it further slow this will definitely affect database performance and soon you might get disk space related warning on drive where transaction log resides. 

    To avoid accidental deletes use backup and restore or policy of Principal of Least Privilege.

     

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP


    Thursday, November 21, 2019 10:43 AM
    Moderator
  • thank you for your reply, i was thinking this was the answer.. 

    im looking into prevention triggers also.. 

    https://www.sqlmvp.org/prevent-accidental-update-or-delete-in-a-database-table/



    ilikefondue

    Thursday, November 21, 2019 10:44 AM
  • thank you for your reply, i was thinking this was the answer.. 

    im looking into prevention triggers also.. 

    https://www.sqlmvp.org/prevent-accidental-update-or-delete-in-a-database-table/

    First of all that is fake website NOT run by an MVP. Plus what he is saying does not fits your requirement. I have updated my first answer, read it again. Triggers are means to implement business logic not to stop accidental deletes. So let us say if you have 10 K tables and you want all to be protected are you going to create that amount of trigger. 

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Thursday, November 21, 2019 10:48 AM
    Moderator
  • thanks again for your reply.. I will tighten up security and i agree its not feasible to create triggers on alot of tables but it might be an option for a small number of key tables.. maybe.. 

    ilikefondue

    Thursday, November 21, 2019 10:52 AM
  • thanks again for your reply.. I will tighten up security and i agree its not feasible to create triggers on alot of tables but it might be an option for a small number of key tables.. maybe.. 

    ilikefondue

    The article you are referring is preventing from update of all rows not a single row is this what you need and is this your requirement ?. I believe not, your is any accidental delete which trigger would not help.

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Thursday, November 21, 2019 10:55 AM
    Moderator