Date: February 5, 2018
Tags: alwayson, Asynchronous, failover clustering, SIOS DataKeeper Cluster Edition, SQL Server, Synchronous
Asynchronous vs synchronous replication on AlwaysOn
From attending the sessions at PASS Summit this week, it has become obvious that AlwaysOn is a hot topic with about six sessions dedicated to this solution . The one thing that I learned is that although the solution certainly has its applications, most of the successful deployments are based on using AlwaysOn in an asynchronous fashion. The reason people avoid the synchronous replication option is that the overhead is too great. During synchronous replication any write must be committed on the replica before it is committed on the source. In the testing that I have done, this overhead introduced can be as much as 68%.
For example, in a test where I have a database inserting about 1,000,000 rows per second and we measure the throughput on the log file, we see that with no mirroring in place we are writing about 400 MBps. Once we start replicating that database with AlwaysOn Availability Groups across a 10 Gbps LAN, we see about a 68% drop off in performance, with this particular database slowing down to about 250,000 inserts per second.
If you are considering the solution as a replacement to your failover cluster, this drop off should be of a major concern to you. In order to achieve the automatic failover that you are accustomed to in failover clustering, you must use synchronous mirroring, which means that you must live with this performance hit. Generally this is not going to be acceptable, which is probably why you don’t hear the experts recommending such configurations on a regular basis.
So what should you do?
Should you stick with you traditional failover cluster and a SAN? What if you want to take advantage of fast, high speed storage such as Fusion-io? In that case, you can’t use a traditional cluster…or can you?
The good news is that you can build a cluster without a SAN and do it all without the expense, limitations and overhead associate with AlwaysOn Availability Groups (more on the limitations and expense in my next blog post). By using DataKeeper Cluster Edition you can build clusters without shared storage AND the overhead associated with Synchronous replication is closer to 10% vs. the close to 70% we see with AlwaysOn Availability Groups.
Come to booth 351 at #SQLPASS and I’ll be glad to demonstrate how the solution works.
Reproduced with permission from https://clusteringformeremortals.com/2012/11/09/how-to-overcome-the-performance-problems-with-sql-server-alwayson-availability-groups-sqlpass/