I have been a DBA for about 2 years now, and there are some subtleties that I still do not understand about AlwaysOn availability groups. First off, they have been nothing but trouble as far as I can tell, mainly because of a situation that occurs frequently in our environment.
We do Windows patches once per month, with the expected server reboots. Roughly once per month, we encounter a situation where clusters with multiple availability groups divide themselves up between the nodes of a cluster.
If I am scheduling backup jobs, I usually administrate the jobs via multi-server administration from a master server. I do this at the resolution of the listener, since it connects to the primary node of an availability group. The problem is, if I specify "all databases," the entire node attempts to backup, no matter the primary/secondary status of a given availability group.
As a result, a lot of noise is generated in our monitoring solutions, as any cluster with diffuse availability groups returns a failed status for the backup, as the failure occurs on the attempt to take a normal backup (not copy-only) on a secondary AG.
Do I have to script jobs to run by availability group in these cases?
Is there a combination of settings that only backup an availability group which is primary for that node?
I have made the argument for consolidating the groups so that we can administrate by SERVER instead of by AG. My bosses argue that we should be using AG's for load balancing across clusters so that we are not paying a SQL license for a node we only use for HADR. I have put forth everything that can go wrong with these diffuse AG's, but maybe there is something I am not aware of.
For clarity, I get that we can point listeners to the different AG's and that they will connect to the primary of its respective node. I just cannot seem to be able to manage any sort of generic backup plan that doesn't produce monitoring and write capability conflicts against these split AG situations. Any clarity that anyone can offer would be greatly appreciated.