Configuring Always On Availability Groups in SQL Server 2019

Question

Please can I get some help with this - setting up Available Groups in a 2-node WSFC.

Questions:

1) I have more than one AG (each with >1 availability databases) in the SQL instance

• Each AG belongs to a different application
• Can I set a different node (of the 2 nodes) as a primary replica for each AG? For example:
  • Node1 has SQL instance SQL1
  • Node2 has SQL instance SQL2
  • The two replicas host AG1, AG2, AG3 each with >1 agdb
• Can I choose SQL2 as initial Primary replica for AG2 while AG1 & AG2 use SQL1 as initial Primary?
• The idea is to try and distribute the load between the two nodes. Is this permitted? This means the two instances will be an active-active pair, as each replica is a primary for a different AG? Is this a permitted configuration?

2) Is there any benefit in having more than one AG per application?

Thank you

Answer 1

Can I choose SQL2 as initial Primary replica for AG2 while AG1 & AG2 use SQL1 as initial Primary?

I believe there is a typo, one of those needs to be AG3. If that's the case, yes you can do this. Each AG is independent of each other.

The idea is to try and distribute the load between the two nodes. Is this permitted?

Yep.

This means the two instances will be an active-active pair, as each replica is a primary for a different AG? Is this a permitted configuration?

Not the proper nomenclature, but yes you can do this.

Is there any benefit in having more than one AG per application?

Administrative benefits only (without being pedantic). If one application/node/whatever requires maintenance, then it could possibly affect only 1 AG. Same with deciding to move applications to different servers or migrations or whatever. There's no performance benefit.

Answer 2

It doesn't work like the "traditionnal" cluster. You cannot select a "prefered node" with AG.

As AGs are independant, you could have some primary on one node and other on the other node, with some TSQL coding, you could create a job that would "rebalance" your AGs and an alert that would launch this job on different trigger (server reboot, AG state change, etc).

This is not something I would do as:

1 - This can cause more failover then wanted. Each time a server will be reboot, some AG will failover to the other node and after a min or two, will have to fail back

2 - (which I think is the most important reason) You may end up exceeding the SQL server capacity without noticing it.

Let's say you have AG1 and AG2. You put AG1 on node1 and AG2 on node2. Everything is working fine. Gradually, AG1 and AG2 start using more ressources. AG1 uses about 75% of the CPU on Node1 and same for AG2 on node2.

At that point, n body will raise any warning as servers will be running at 3/4 of their capacity so no performance issue, no monitoring warning, etc...

Now let's say Node1 dies and you need to run only on node2 for a couple of days... suddently, you would need 150% of the CPU which may cause a lot of problem (I'm guessing those app are in AG because they are business critical).

Usually, I prefer to run all my AGs on the same node and make sure everything is tune to work. I also make sure Node2 have the same sizing as node1 and I will run on Node1 until it's patch then on Node2 until this one is patch (usually more then 1 week). That way, I can make sure the AG is working fine and that in case of an incident with one of the node, everything will keep running just fine