A user asked me the other day how to go about managing backup issues in Availability Groups. Well, Minion Backup is fully Availability Group aware, and has innovative features that makes it a complete solution. This is one of the big reasons we coded MB: any solution for dealing with the issues listed below has to be manually coded. We wanted to stop re-inventing the wheel over and over.
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 \\NAS1\SQLBackups\Server1, while the log backups for the same Availability Group would be going to \\NAS1\SQLBackups\Server3 and even \\NAS1\SQLBackups\Server2. 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
Backing up from a specific server is easy in Minion Backup. Minion Backup knows if your database is in an Availability Group, and you can set the “PreferredServer” column in the Minion.BackupSettingsDB table to tell it where you want each backup type taken. You have the option to use the backup preference in the Availability Group settings by configuring PreferredServer to ‘AGPreferred’, or you can use the name of a specific server. That means you can set a specific server for fulls, and a specific server for logs if you want. If you want Minion Backup to determine your PreferredServer dynamically, you can code your own logic use it as batch precode. While it’s an easy setting, you can make it as flexible as you need it to be.
2. Files in one place
We know that backups coming from different servers means that all of your different backups can be in different locations. However, Minion Backup solves this problem easily using server labels. The ServerLabel field (in Minion.BackupSettingsPath) overrides the ServerName in the backup path, so that all of your backups go to the same location. You can set ServerLabel to the Availability Group or listener name, or even the application name if you want. So, instead of sending backups to \\NAS1\SQLBackups\Server1, the backups would go to \\NAS1\SQLBackups\MyAGName. As long as you set all the nodes to the same ServerLabel, then no matter which node they backup to, they’ll all go to the same location. Now you don’t have to search around for where the different backups are and your restores and file deletes are easy.
3. COPY_ONLY automatically
If you want to take a full backup on a non-primary node of an Availability Group, then you have to use COPY_ONLY. And of course, you do have to code that into your own solution. However, Minion Backup is fully aware of the Availability Group, so it does this for you automatically. There’s nothing you need to code, and nothing you need to manage. It just does it. As databases come and go out of your Availability Groups, Minion Backup makes the right choice and does what it needs to do.
4. Sync settings between nodes
Manually changing settings on each node of your Availability Group is just asking for trouble. You could have a new node that you haven’t heard about, or you could just forget about one, or a node could be down and then you get busy and forget to push the changes when it comes back up. So what you need is a way to automatically push all of your settings changes to all the nodes. This is where Minion Backup comes in. All you need to do is turn on the SyncSettings column in the BackupSettingsServer table and Minion Backup will keep all your node settings in sync. This also means that as nodes come and go, they’ll get the new settings, and if a node is offline, Minion Backup will push them when it comes back online.
5. Backup files, handled
If you could keep your file deletion schedules even when a node goes down, or to be able to easily find the file locations, it can keep you from having a bad day. Minion Backup spares you the worry about where your files, are or whether they’re going to still be deleted on schedule. Again, Minion Backup makes this very easy with the SyncLogs column in the BackupSettingsServer table. Your backup logs will automatically be synchronized to all nodes, so locations and delete times are always in place.
That’s a good roundup of the Availability Group features in Minion Backup. We love these features, because we’ve seen firsthand how much easier it is to manage your Availability Group backups with Minion.
Go on, solve your problems: download Minion Backup!