Is it best practice to have 2 Primary then 1 Secondary for SQL Server AlwaysOn Availability Groups?

My purpose for having 2 Primary instances is to balance the database load. (e.g. my db1 and db2 is on 1st Primary Server then my db3 and db4 is on 2nd Primary Server, then i will configure my AG to add db1 and db2 to 2nd Primary as passive and db3 and db4 to my 1st Primary Server as passive).

Will this improve the performance of my SQL Server?

| improve this question | | | | |
  • 1
    AlwaysON is not about improving performance. Its meant to be high availability and scaling out your reads (if you have readable secondaries configured). Depending on how you configure your AGs - sync or Async will affect your RTP and RPO. – Kin Shah Oct 23 '15 at 15:58
  • 1
    You can't balance writes using AGs, sorry. Maybe with merge or peer-to-peer replication but have fun - they don't have the reputation of being the easiest to configure / maintain / troubleshoot. – Aaron Bertrand Oct 23 '15 at 16:24

AlwaysON in a roundabout way can help with overall performance but it does that by helping you scale out the reads to other nodes which then also help you with blocking/locking.

From how you described it you are spreading out your databases across 2 different instances, but then making replica's of each instance on the other database server. Outside of locking/blocking benefits you won't get too much out of this, so no, I wouldn't consider this a 'best practice' for performance. Your active production databases are still sharing server resources with the read only copies. Thus, this isn't a 'best practice' for performance and AGs. Also AG's represent a issue where you have to decide if you're willing to get data that's a little bit stale and near real time, or if you want to force performance loss on the master writer and keep the data on the secondary up to date.

If you're interested, here is how StackExchange uses AGs to give you a bit more idea of how enterprises use it.

| improve this answer | | | | |

If you take a look at this article http://solutioncenter.apexsql.com/performance-monitoring-of-alwayson-availability-groups-part-1/ - you will understand that AlwaysOn availability group performance is not linked with "performance" but with "reliability". AlwaysOn is the synergy of the best of clustering (FCI) and database mirroring, folded in the single feature - as analogy to this, look at this as RAID 1 which is there to keep your data safe, rather than RAID 0 which sacrifies reliability to gain some performance.

Unlike monitoring the Failover cluster or mirroring (where passive/secondary components are inactive), AlwaysOn Availability Group allows the direct interaction between a user and secondary databases, but ONLY for reading (not writing).

What’s more - with AlwaysOn, the failover is possible for a single database, a group of databases or for the entire SQL Server instance. In addition, AlwaysOn availability group allows the user to create more than one failover target and to have the full control of the failover process.

So, by careful planning you can actually get some performance improvements, by distributing loads from primary to secondary etc. Anyway, I strongly advise you to monitor AlwaysOn metrics to get some insight in how your AlwaysOn AG behaves performance wise and if needed you can reconfigure your AG to squeeze out some better performance if possible.

You can use some SSMS built-in monitoring solutions via AlwaysOn dashboard or use T-SQL scripts for direct querying of the AlwaysOn dedicated DMVs (or Performance Monitor, but it's a bit tricky).

Create alerts and notification on AlwaysOn metrics that can help you to analyze the potential bottlenecks and performance problems, so that you can deal with them timely. Once you establish this well, it should help you in both fields - reliability and performance.

| improve this answer | | | | |

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.