Mixing 2 and 3 node AG on a single WSFC?

Question

I have a 3 node availability group (Enterprise 2014), it is multi subnet, with two data centers and file share witness in a third data center. There are about 20 "production" database that exist on the Primary, Reporting and DR instances, via an AG with a listener. Currently configured, async with manual failover. Production is about 1TB, active OLTP.

The application points to the listener, reporting points to Report Server.

We get HA by temporarily changing to sync-commit and failing over. Reporting is fine with async as is DR (risks understood)

There is a "Reportsdb" that only needs to exist on the reporting database. I have used a 2 node AG with async to have it available on DR. Reportsdb is about 50GB, with a rebuild from scratch time of a few hours.

The DR goal is have reporting on a seperate server as long as any two are up. With two down, there is no reporting.

Question: If we change to sync and automatic failover; what if any issues will having mixed 2 and 3 node AGs on one WSFC have?

Answer 1

None. The settings and behavior of each availability group (AG) are completely independent of the other AGs. When an AG is created, a separate role is created in the cluster to contain that AG's objects. Each role acts independently and doesn't share, care about, or even know about the settings of other AGs that are on the cluster.

Answer 2

Assuming from the image and design that the "prod server" is the primary OLTP server and that the "report server" is for reporting only. The AGs are probably set up with different Primary locations.

• "prod server" has "PrimaryAG" as the Primary in normal run time.
• "report server" has "ReportingAG" as the primary in normal run time

The current configuration is bad, while failover happens at the AG, voting is set and occurs at the cluster level. The Microsoft Recommendations indicate the "report server" should both have a vote "Each WSFC node that hosts an availability group primary replica" AND not have a vote "Each node that could host a primary replica, as the result of an automatic availability group failover or FCI failover, should have a vote" You are either giving a vote to a node that can not be primary as the result of an automatic or you are taking it from a node that hosts a primary replica.

Switching to sync commit with auto failover, would allow proper voting, but could complicate fail back.

Recommended Adjustments to Quorum Voting When enabling or disabling a given WSFC node’s vote, follow these guidelines:

No vote by default. Assume that each node should not vote without explicit justification.

Include all primary replicas. Each WSFC node that hosts an availability group primary replica or is the preferred owner of an FCI should have a vote.

Include possible automatic failover owners. Each node that could host a primary replica, as the result of an automatic availability group failover or FCI failover, should have a vote. If there is only one availability group in the WSFC cluster and availability replicas are hosted only by standalone instances, this rule includes only the secondary replica that is the automatic failover target.

Exclude secondary site nodes. In general, do not give votes to WSFC nodes that reside at a secondary disaster recovery site. You do not want nodes in the secondary site to contribute to a decision to take the cluster offline when there is nothing wrong with the primary site.

Odd number of votes. If necessary, add a witness file share, a witness node, or a witness disk to the cluster and adjust the quorum mode to prevent possible ties in the quorum vote.

Re-assess vote assignments post-failover. You do not want to fail over into a cluster configuration that does not support a healthy quorum. source

(This answer is a work in progress, I believe I missing some key points still)