AlwaysOn Asynchronous vs Synchronous Commit Performance for SharePoint

Hot on the heels of the last analysis showing the performance hit for a synchronous SQL Server AlwaysOn cluster, re-running the same tests show a distinct performance boost when asynchronous commits are used. In fact it’s nearly the same as standalone at that point, which makes sense as DB updates aren’t restricted to needing confirmation of a commit on the 2^nd node before completing.

Here are the new numbers.

Reading data is pretty much identical on all setups and updates are slightly slower with asynchronous commits configured for the AlwaysOn cluster. Here’s the data in graphical format:

There is a slight slowdown by using asynchronous commits over synchronous commits for data updates, but not much.

Which AlwaysOn Availability Mode Should I Use? Asynchronous or Synchronous Commits?

Good question, and it really depends on A: what type of database we’re talking about here and B: your business requirements.

Asynchronous commits are only supported for the following types of SharePoint databases:

• Content database (minus central administration)
• App management
• Business Data Connectivity
• Foundation subscription settings
• Managed metadata
• Secure store
• User Profile
• User Profile Social
  • Notably, user profile sync database isn’t supported in asynchronous mode.

No search databases are supported in this mode, or any other type not mentioned @ https://technet.microsoft.com/en-us/library/jj841106(v=office.15).aspx

Cluster Failovers – Automatic or Manual

Next, you need to decide if you need the ability to be able to failover the primary node automatically or not.

If you need automatic failover then your only option is “synchronous commit”. This is because by definition, with asynchronous commit it’s impossible to be sure there’ll be no data loss on a failover (as no confirmation is ever waited for before moving on from a data-write), so SQL Server forces you to failover manually because then you’ll get the blame if something didn’t sync properly (because you told SQL Server performance is priority over integrity).

This screen is what greets you when you try and failover an AlwaysOn group manually that’s in asynchronous mode. Of course if your primary SQL node dies, so will SharePoint until this manual failover process is done.

SharePoint + AlwaysOn Best Practises

My recommendation? Unless you’ve got a burning need for high-speed updates, I’d leave everything in synchronous mode with automatic failovers enabled. If you have content databases that need updates to occur quickly you can make them the exception on another availability group but as a rule, synchronous updates & automatic failovers are the way forward generally speaking.

I hope that helps!

Cheers,

Sam Betts