How to Load Balance Availability Group I have 2 Servers under  SQL Server Cluster (Server 1, Server 2)

2 Databases configured with Availability Group (Database 1, Database 2)

Server 1:
Database 1 PRIMARY REPLICA
Database 2 PRIMARY REPLICA

Server 2:
Database 1: SECONDARY REPLICA
Database 2: SECONDARY REPLICA

If Database 1 is starting to consume a lot of resources and I want to add a 3rd server (Server 3) to lower the stress on Server 1 is this given example possible?

Server 1:
Database 1 SECONDARY REPLICA
Database 2 PRIMARY REPLICA  

Server 2:
Database 1 SECONDARY REPLICA
Database 2 SECONCARY REPLICA  

Server 3:
Database 1 PRIMARY REPLICA
Database 2 SECONDARY REPLICA  

Is this possible to configure with minimum impact on users? I just want to know the possibilities of scaling horizontal with Availability groups as we have not the possibility on scaling vertically by adding resources to the server, the backups can be taken on a secondary replica right?
The million dollar question is - is it worth adding an extra server to the windows failover cluster in your current scenario ?

IMHO, NO - since you are on sql server 2012.

Why ?

SQL server 2012 and 2014 will allow read-only routing directed traffic to the FIRST available replica defined in the routing list - provided the first read-only replica is  accessible else it will pick up the next available replica.

So in sql server 2012 and 2014, even if you have multiple secondaries available for honoring read requests, its not possible to spread the read load across the available secondaries.

SQL server 2016 allows you to configure load-balancing across a set of read-only replicas.


  Is this possible to configure with minimum impact on users?


It is possible with minimum impact, but as I said above it would not be worth doing it.

How to do it ?


Add server 3 to windows failover cluster. 
Take full backup from server 1 (since it is primary) and restore it with no recovery on server 3.
Take a log backup from server 1 and restore it with norecovery on server 3.
Join the database to the availability group.
Change the sync mode to Synchronous and then Failover the server1.db1 to server3.db1 and now server3.db1 will be primary.
Resume the data movement on all the secondary replicas. In my testing, the data movement gets suspended and you have to resume it.


Below is how it will look (the drawing is not pretty, but it explains what I am telling :-) ):




  the backups can be taken on a secondary replica right?


Yes, you can take copy_only full &amp; regular log backups on secondary replica. A full backup must be performed on primary database.