1

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?

  • What happens when one node goes offline? Can the primary deal with the overall workload? This question is quite opinion based by the way and may be closed. – George.Palacios Jan 18 at 14:55
  • 1
    When you say load balancing - do you mean allowing reads from secondary ? SInce you are using standard BAG - this is not allowed. – Kin Shah Jan 18 at 14:58
  • @George.Palacios : Yes the primary can, but it goes sit at 80% utilization or more. RAM is probably around 90% utilization when all DBs are on one node. – WadeH Jan 18 at 15:00
  • @Kin : No, I mean splitting the availability groups between the 2 nodes. So both nodes have primary and secondary availability groups. – WadeH Jan 18 at 15:00
1

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.

  • Agreed on the Disk IO. Our Architecture is not ideal, and both servers end up using Virtual disks on the same SAN. – WadeH Jan 18 at 15:07
0

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

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.