Multiple SQL Server instances each with one Availability Group on one Cluster of two nodes in a DAG configuration

Question

I'd appreciate some advice please. I am about to get to work on implementing a SQL 2019 Distributed Availability Group configuration with following architecture:

• Two sites - one primary & one DR
• Each site has a WSFC configuration with two nodes.
• DAG configuration asynchronous between the two clusters.
• Each local AG is synchronous availability mode and holds the AG databases for one component of the application.
• The application in question has about 3 or 4 components, each with its own set of back-end databases
• The databases for each application component are hosted in one SQL instance
• so the 3/4 SQL instances are all hosted on the pair of nodes on each cluster.
• each AG has it's own listener, hence one listener in each instance, all on the same local cluster.
• all the secondaries are non-readable

This is my first experience with multiple SQL instances on a cluster, each configured with AG and DAG between the two clusters.

Could you advise me please if there are any specific issues I need to pay particular attention to in such a configuration?

Thank you.

• PTL_SQL: A link to any helpful documentation for this architecture will also be appreciated please.

Answer 1

Could you advise me please if there are any specific issues I need to pay particular attention to in such a configuration?

so the 3/4 SQL instances are all hosted on the pair of nodes on each cluster.

I would not use multiple instances of SQL Server for Availability Groups or Distributed Availability Groups. When you mix instances, it makes troubleshooting harder and you get into nit picking for resources as none of the instance know or case about each other. If you do multiple instances, you’ll want to set scheduler affinity, max server memory, I/O governance, etc., so that one instance doesn’t stomp on the other.

• PTL_SQL: thank you for this very helpful caution. I will take this up with the team tomorrow, and see how it goes. I am very appreciative. Just one question please - would you then suggest the best way will be to have each instance on its own set of nodes? In which case we are talking of more VMs? Or would one instance with multiple AGs (each AG hosting the databases for each application component) be an easier solution to troubleshoot? I had thought that would be more difficult to troubleshoot, but maybe I'm wrong?
• Sean Gallardy: That depends on how the application is architected. It seems as though the components for the application can be on different instances so in this case I'd make them each their own VM which would add flexibility but at the cost of much more administration. If you put them all on one instance, you'd remove a bunch of admin overhead but at the cost of flexibility. I said my choice but it's up to you and your team. @PTL_SQL
• PTL_SQL: thank you again for your guidance, very much appreciated.