Keep smaller databases out of an availability group (and recover via backup) to avoid cluster/AG issues taking the db offline?

Question

We have several availability groups and each one has a mix of large databases and small databases, all critical. They're all asynchronous commit because their secondaries are in a different data center, and while we want quick recovery/failover, some data loss is acceptable.

We've run into issues with some of the two nodes + file share witness clusters, where the cluster goes offline for some unknown reason, bringing every AG database offline. The primary node itself is fine, so the other non-AG databases on the primary are still available. Some of the most critical dbs are small (<100gb), so I'm thinking we may want to take those out of their AGs and have them be normal dbs, where cluster problems won't affect it. We would change its log backup schedule to something like 5 minutes to minimize rpo, and handle any recovery as a normal restore from backup.

AGs are great when they work, but when they don't it can be a bear to troubleshoot and get back online. Any thoughts on this design? Thanks!

Answer 1

We've run into issues with some of the two nodes + file share witness clusters, where the cluster goes offline for some unknown reason, bringing every AG database offline.

Along with:

[...] so I'm thinking we may want to take those out of their AGs and have them be normal dbs, where cluster problems won't affect it.

I would, instead, figure out why the cluster is losing quorum and then take appropriate actions to not have that happen.

They're all asynchronous commit because their secondaries are in a different data center, and while we want quick recovery/failover, some data loss is acceptable.

Where is the FSW located? HA is questionable at best if it's in one of those sites. You might only have these two, I'm not knocking only having two sites, but then something else should be done to achieve HA, though it seems like you really just want DR/BC.

AGs are great when they work, but when they don't it can be a bear to troubleshoot and get back online.

I heavily work with them. In most cases, the configuration or assumptions in the setup are the issue (not saying it is here).

[...] I'm thinking we may want to take those out of their AGs and have them be normal dbs, where cluster problems won't affect it.

If you go this route, which I would not, then I'd get rid of all the AGs, period. Don't half do something and half do something else, you're creating parity between the solutions and only adding in more manual effort and troubleshooting issues.

It sounds like you'd rather use logshipping, or if you'd still like to use AGs, then distributed AGs to the other data center.