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.

Improve this question
4
  • 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.

Improve this answer
1
  • 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.