Always On - tips for running large transactions

  • NicHopper

    SSCrazy Eights

    Points: 9037

    Hi all,

    I wanted to get a second opinion on something, this weekend I need to do a deployment to 2 databases which are part of an always on availability group. The deployments are going to cause a table rebuild of some tables, the largest of which is 61GB

    So I know that the work is going to have to done on the primary and also the replica, thats how it goes but I wondered if there were

    any tricks that may speed things up.

    I did entertain the idea of suspending the always on, do the deployment then re-enable it but I think all I'm doing there is moving the wait for the replica down to the end, its still going to have to do it, so probably not worth it.

    So does anyone have any ideas on how I can speed things up, or am I just best waiting for it to do its thing? Also is there a way to monitor the progress/status of the AG in more detail than the GUI gives me.

    Thanks,

    Nic

  • Michael L John

    One Orange Chip

    Points: 25965

    NicHopper wrote:

    So I know that the work is going to have to done on the primary and also the replica, thats how it goes but I wondered if there were

    Uh, no.  If you make a data or schema change on the primary, it will be replicated to the secondary.  There should be no need to do anything on the secondary.

    NicHopper wrote:

    any tricks that may speed things up.

    I did entertain the idea of suspending the always on, do the deployment then re-enable it but I think all I'm doing there is moving the wait for the replica down to the end, its still going to have to do it, so probably not worth it.

    So does anyone have any ideas on how I can speed things up, or am I just best waiting for it to do its thing? Also is there a way to monitor the progress/status of the AG in more detail than the GUI gives me.

    Set the secondary to asynchronous commit, let it do it's thing.

    If you suspend the data movement, the transaction log may grow very large, which may cause a whole set of other issues.

    As far as monitoring outside the GUI, there are any number of queries that a google search will turn up.

    I use some of these:

    https://www.sqlshack.com/measuring-availability-group-synchronization-lag/ 

     

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • NicHopper

    SSCrazy Eights

    Points: 9037

    Thanks very much, I'm going to just leave it to do its stuff, going to be much safer that way.

    Thanks,

    Nic

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply