1

I have a software company asking me to remove a database from a synchronous availability group prior to them running their upgrade scripts.

They are convinced that this will increase the execution time. Are their any DDL or DML queries that would take significantly longer when clustered?

What I want to know is: If I do an operation that takes 5 minutes on a non clustered database, will the same take 10 minutes in a cluster, or the same amount + some minimal overhead? I'm guessing it's not going to double or even get that close, but I was hoping for some experienced insight.

  • Creating or rebuilding indexes, or any size-of-data operations, can certainly cause issues in synchronous mode. I wouldn't call removing a database from an AG "un-clustering" though. – Aaron Bertrand Jan 18 '16 at 21:14
  • Every action will have to get sent to the other nodes before the transaction commits. You could ask to change it to asynchronous for this upgrade and then switch it back when all scripts are done. Then, the primary node won't have to wait for the other nodes. – Antoine Hernandez Jan 18 '16 at 21:14
  • It's hard to predict the time it will take to write the same operation to a log on one or more secondaries, even if the hardware is identical. – Aaron Bertrand Jan 18 '16 at 22:36
  • Look into suspend data movement and then resume once your upgrade is done. Be aware that transaction log records will be piling up at primary until you resume the data movement. Test this option out if you don't want to remove the databases. – Kin Shah Feb 3 '16 at 23:02
2

Yes they are correct it will increase the execution time. As the comments state though there is no measurable way to determine this unless you have a matched test/stage environment and try it both ways (which would also be a best guess since you cannot know latency and bandwidth of your network and the load on the nodes).

I see this as the company asking you to trade HA and your time (adding the DB back to the AG) to save some indeterminate, likely small, overhead for their scripts to run faster. They better have a pretty good reason to trade that.

  • This ended up being the real reason. Their script ended up having no determinable impact. It was their unfamiliarity with clusters that made them concerned. – sreimer Feb 4 '16 at 15:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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