Post reply

Always on - huge set of inserts and updates

  • October 17, 2019 at 8:23 am

    #3689556

    Hi everyone,

    I need some advice please on how best to do something.

    We have a database which is 1TB in size, its part of a SQL 2014 Always On AG (synchronise sync).

    We have to do a very large data change, initial estimates are from testing it will take around 15 hours. Optimisation has been done to keep this down, trust me on that.

    I'm concerned that whilst this took 15 hours in testing (server specs matching production) that the additional over head of the always on may cause additional delay and perhaps issues witht the AG.

    So I wondered if anyone had any tips, do I just have to bit the bullet and let it do its thing.

    I had wondered about suspending the replica then letting it do the insert work on the primary and then resume it and let the secondary catch up after that as its not needed for reporting, but sadly this is 2014 server and suspend is not an option.

    So other than me just letting it do its thing does anyone have any suggestions,tips etc.

    Thanks for taking the time to read this.

    Nic

    Check out my blog http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • October 18, 2019 at 9:10 am

    #3690052

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • October 25, 2019 at 4:14 pm

    #3691995

    Hi NicHopper,

    you could try to move the DB out of the AG (and then remove it from all replica nodes) , do the data changes/updates etc... , and then re-add the DB to the AG eventually with the secondary replica set in asynch mode .

  • October 25, 2019 at 9:02 pm

    #3692043

    I think suspending would not help anyway, just would defer all that work until later (and I think it piles up in the LDF?).

    Another way to possibly improve things would be to change AG to async for duration of the operation.  We used to do that before large index rebuilds, seemed a decent solution.

    Or, like elenad says, temporarily take DB out of the AG for duration of your operation.

    Hard to choose an option w/out knowing how painful each will be.  Maybe you make no config changes, and it all works fine?  Setting up a similar AG in test would help you know.  I'm guessing this not trivial for you.

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

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