Question :
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 results:
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?
Answer :
I kind of like your idea, although it defeats the idea of a primary and second nodes. You should be sure to evaluate if one server can take the load of both, as if one goes down, then one server will have all the load.
You should also be careful, if the activity on one server gets to high in time, it might affect the “seconday” node activities and cause I/O bottlenecks. (It all depends on how your disks are confugred.)
But other than that, it could be a good alternative and when your organization gets bigger, you will be able to afford another server to remove some load from your current setup. Just make sure to review carefully your emergency scenarios and to test them well.
I scripted the failover of some AG on the secondary node in order to be able to “load balance” my AG all the time (the job run on server start).
I didn’t implement it in prod and won’t because:
1 – It’s will be way harder to see if each node will be able to handle the full load (in case of a failure on one node or for DR for example). As the load will always be “split”, I won’t notice it if both server are used at 80% and will suffer from it in case of a server failure, forcing me to run on only one node for a couple of days.
2 – It makes it harder for patching as we could patch the secondary node without any user impact, with a “split” approch, there will not be any secondary server anymore so whatever the node you reboot, you’ll get an impact (twice if the load is automated as there will be another failover once the patched server will come back online).
Finally, you should also make sure that there is no cross database queries. If a query/Store Proc in DB1 goes like this :
update DB2.dbo.table ...
You will end up trying to run an update on a database that is not writable.
If you are ok with those downside, then I guess it’s nice to have “load balancing” which unfortunatly, isn’t build in with the availability Group in SQL.
Thanks