So I have the current scenario, and it looks to be working fantastically, but I just want to get some input on the configuration. Is it smart? Are there any issues I am not thinking of?
We have SQL Server Standard, and as such, you can only have 1 DB per AG (we have 20 databases), and no Read-Only secondaries. Basically meaning, the primary server is doing ALL the lifting, with the secondary doing a lot less. So essentially, you are paying for resources for Node 2, that are sitting at 10% workload, while node 1 is at 70/80% workload. Both nodes are fully licensed with regards to SQL Server cores.
What I have done, to assist this, is slit the database primaries up. So about 50% of the databases are Primary on node1, while the other 50% are primary on node2.
The applications all connect great to either node via their respective listener. If a failover occurs, just the databases on the failing node are affected, and failover to the other node (we have tested this fairly well).
Each node, can now split the load, essentially load balancing. It is a manual process to set it up this way and when deploying new DBs and groups they go to the lighter node. but a small price to pay for "more" hardware punch without much cost (licenses which we already have and a bit of admin).
What are your thoughts on this, guys?