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?
Best Answer
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.