As the topic says, I was wondering if you guys have any advice on potential issues with Always On availability during a massive update?
We're in need of making several massive datamodel changes, removing & adding tables and moving around large amounts of data (some 300+ GB), including creating and rebuilding hundreds of indexes and statistics in a procedure that will take well over 24 hours in total, and schedule-wise can not take more than 30 hours before bringing the DB's back online. And I'm not sure whether the always on availability mode's synchronization is the best option to replicate these changes to the other availability groups. Would it be better to just backup the changes once they're done, restore that to the other groups and re-enable synchronization from there?
Like I said, I don't really have experience working with it, so this is an open question. And apologies for any misunderstandings or misconceptions here, again since my experience is limited. Please correct me where appropriate. 🙂
Thanks!
Best Answer
If the database has a long scheduled maintenance you don't need high availability or do you? Keep in mind that every change is written to the transaction log and than transmitted to the secondaries. I wouldn't use the full recovery model for such a maintenance. If you don't do such a thing each week/month, I would turn off HADR for these databases, change the recovery model and set up the AG afterwards again. Transfering the complete backup after the changes is a lot faster than transfering each change. And the primary server has less work to do.