Let’s talk about some of the challenges that DBAs face when managing backups with Availability Groups. Then we’ll talk about solving these issues with Minion Backup.
Managing Backup Issues in Availability Groups – The Issues
1. Back up from…wherever
Availability Groups don’t let you pick a specific server to run backups on. You can only give weight to a specific node, but you can’t say that you want logs to run on Server1 and Fulls to run on Server2, etc. You can code those choices in, but if you decide to make a change, you have to make it on all the nodes manually.
2. Mixed file locations
Pretty much every backup solution on the market appends the server and/or instance name to the backup path. Usually, this is a good idea: if you have a centralized backup drive, you’ll want your backups separated by server name. Availability Groups introduce a problem though, because you can back up on different nodes. So of course, each backup will be under a different path (based on the node name). Full backups could be going to NAS1SQLBackupsServer1, while the log backups for the same Availability Group would be going to NAS1SQLBackupsServer3 and even NAS1SQLBackupsServer2. The backups can move around as nodes go up and down. Restoring in this scenario can be difficult if all the backups aren’t in one place.
3. COPY_ONLY woes
Backups have to be run with COPY_ONLY on non-primary nodes. This isn’t an issue on its own, but it is something that you must code into your solution. And if you remove a database from an Availability Group, then you have to remember to manage that in your code (or preferably, to have coded it properly in the first place). Either way, it’s just one more thing to manage.
4. Work multiplier
Any change to a schedule, or to a backup setting must be made on all nodes. Nothing in the Availability Group setup lets you manage your multiple backup locations, and remembering to do this on all the nodes is doomed to fail.
5. Backup file backlog
If your backup node fails, you lose your record of where its backups were. Without that log, the backup files won’t delete on your defined schedule. Sure, you could probably get that log back by restoring a copy of the management database from that server, but how much time has gone by since then? That is, of course, if the management database was being backed up properly. And if all that happens, it’s just one extra step you don’t need when you’ve got a node down. You shouldn’t have to worry about those tasks that should be taken care of for you.
These are the biggest issues with managing backups in Availability Groups. Now let’s look at Minion Backup. It solves each one of them with no coding required.
Managing Backup Issues in Availability Groups – The Solutions
1. Back up from the right server
Continue reading on MinionWare.net.