Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question

1 Answer 1

up vote 3 down vote accepted

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.

share|improve this answer
    
Thanks for the answer. Returning to this... are there any other steps needed to re-enable Hadr after the procedure has been done, other than just setting the property IsHadrEnabled back to 1 again? Also, by "Transfering the complete backup after..", do you mean that I should just let alwaysOn replicate the changes directly from the finished version of the primary group DB? OR should I actually restore the finished backup to the other groups first, and THEN re-enable Hadr? –  Kahn Mar 20 at 6:31

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.