Attending the sessions at PASS Summit this week it has become obvious that AlwaysOn is a hot topic with about six sessions dedicated to the topic. The one thing that I learned is that although AlwaysOn 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.
Figure 1 – MBps written to a SQL Server database before and after AlwaysOn Synchronous Mirroring
If you are considering AlwaysOn 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.
Clustering For Mere Mortals 
[…] have their place, you may want to consider the overhead associate with them as I discussed in my previous article. Also, you really need to consider what applications will be utilizing the SQL Server database as […]
[…] a hot topic here at SQL PASS Summit. As I mentioned in my previous posts, you need to consider the overhead associated with AlwaysOn as well as other limitations, If however you can deal with the overhead and the […]
[…] https://clusteringformeremortals.com/2012/11/09/how-to-overcome-the-performance-problems-with-sql-ser… […]
Hello,
You have done a great analysis and testing of the setup using AlwaysOn. How are you inserting the 1M rows into the PRIMARY database? Without AlwaysOn Sync, I have noticed waits with log writes with 10x the inserts you have mentioned, but for an average of 1ms as I have using PCIe SSD. When inserts are done with such a load in a loop this seems to be the behavior. It is not the network that is delaying or saturating, but how fast the log is written, read and written on the secondary. I’d like to certainly like to see your setup and configuration. I am using Intel 800GB PCIe SSD in my testing.
R
The test harness was written by a Fusion-io, so I can’t go into much detail other than it is a highly optimized SQL script and SQL is tuned for high performance. When running with no replication in place the CPU becomes the bottleneck as a we pushing them to their limites. In other environments (more cores) I’ve seen this test push multiple millions of inserts per sec.
Hello Dave,
Who do I talk to at SIOS to discuss my requirements and how the data keeper maybe used?
Also, where do I find more on how it is licensed and priced? Is this Windows Server 2012 Certified?
Thanks,
Raman
Raman, glad we could connect.
I have had many years experience with ( Steeleyes Technologies) SIOS – SteelEyes LifeKeeper for SQL Server and DataKeeper.
Three-node cascade F/O clusters with geo nodes.. Work very well as described, very stable and easy to mange and with very few problems over the years. Tech Support from SIOS is second to none, especially if you get into trouble. Comparing this combo of SE-LK&DK for SQL versus AO-AG or FCI’s would be of great interest to me. In my use, I used local RAID-10’s. Failing back over WAN’s under heavy contention post-site fail over recovery by many other applications and windows sessions restarting in the hundreds was for me a critical test for the products durability and resilence. Now I am in a position will presenting a “apples-to-apples” type of comparision in like of the new AO-AG fervor with new SQL-2012 adopters, or others new to clustering with Wintel platforms.