4

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.

6

Availability Group aware backup scripts should exist on all nodes that can possibly host the primary copy of each database. This allows the active node to perform the backup, avoiding error reports from nodes where the database is in secondary or read-only mode.

There are several excellent Availability Group-aware backup solutions, such as the ones from Ola Hallengren or Midnight DBA's MinionWare Backup

  • 2
    Also each AG has a policy to configure which replica is preferred for backup, which you can test in your backup script: docs.microsoft.com/en-us/sql/relational-databases/… – David Browne - Microsoft Jan 15 at 18:59
  • Thanks for your reply, Max. I will assume from this that Microsoft does not have an out-of-the-box solution for doing SQL backups in diffuse availability group situations. That's quite an oversight in my humble opinion. – Aaron Rheams Jan 16 at 18:12
  • Arguably, it's not Microsoft's place to create a solution to every single possible situation. You can run backups on every SQL Server instance, some will fail if the backup cannot take place because of the state of the database, but that is hardly Microsoft's fault. It's really not terribly difficult to write a simple backup script that is sensitive to the state of the database and only run a backup when it is desirable. Probably 999 out of 1,000 DBAs prefer to use scripts for backups, and they prefer to manage those scripts carefully, rather than leaving it entirely up to the vendor. – Max Vernon Jan 16 at 18:17
  • I was running my own custom scripts that were based on MSSQL multi-server management to create rolling backups on a dynamic schedule. We are making some changes, however, and I have been given directives to rework and simplify the process for anyone filling in. In any case, we may have found a solution in Quest LiteSpeed, as it has an interface option to distinguish availability groups and only backup databases which are primary. – Aaron Rheams Jan 17 at 22:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.