ServerLabels in Minion Backup

There’s a great way to increase the effectiveness of your backup and HA strategy: use the ServerLabel feature in Minion Backup.

The problem with most backup solutions is that they don’t take AG failover into account.  Here’s a common scenario to show you what I mean.

Let’s say you’re backing up to \\BackupNAS\SQLBackups.  Most of the time, your backup routine will append the server name and probably the database name to the path.  There are other things that can get added, but we’ll keep this simple.  So your backup path winds up looking like this instead: \\BackupNAS\SQLBackups\Server1\MyDB.  The problem comes when you’re running an AG and you’re either taking backups on different nodes or when your backup node fails over and the backups continue on a different node.  Either way you’re stuck with your backups being in two different locations.  Here’s what I mean.

Before Failover:

Full backup – \\BackupNAS\SQLBackups\Server1\MyDB Log backup – \\BackupNAS\SQLBackups\Server1\MyDB Log backup – \\BackupNAS\SQLBackups\Server1\MyDB Log backup – \\BackupNAS\SQLBackups\Server1\MyDB Log backup – \\BackupNAS\SQLBackups\Server1\MyDB

After Failover: Log backup – \\BackupNAS\SQLBackups\Server2\MyDB Log backup – \\BackupNAS\SQLBackups\Server2\MyDB Log backup – \\BackupNAS\SQLBackups\Server2\MyDB Log backup – \\BackupNAS\SQLBackups\Server2\MyDB

Fail Back to Original Node:

Diff backup – \\BackupNAS\SQLBackups\Server1\MyDB Log backup – \\BackupNAS\SQLBackups\Server1\MyDB Log backup – \\BackupNAS\SQLBackups\Server1\MyDB Log backup – \\BackupNAS\SQLBackups\Server1\MyDB Log backup – \\BackupNAS\SQLBackups\Server1\MyDB

So you can see that there are different backups in different locations.  And the log chain starts on Server1, then moves to Server2, and then back to Server1.  This can make it very difficult to build a restore statement if you don’t really know where your files are going to be.  And also, if you look above you’ll also see a diff backup was taken once it failed back to Server1.  But if Server1 wasn’t the primary node, then the diffs would be taken on another server which would add a 3rd one into the mix.

This exact scenario is what we have solved in Minion Backup.  With MB you can define a ServerLabel that gets used instead of the server name.  Let’s say we define the ServerLabel to be ‘AGListener1’.  We can do that with a simple update statement like this:

 

UPDATE Minion.BackupSettingsPath

SET ServerLabel = ‘AGListener1’

 

Now every backup on your server is going to use this ServerLabel instead of the server name.  Here’s what the failover scenario above looks like with this new ServerLabel.

 

Before Failover:

Full backup – \\BackupNAS\SQLBackups\AGListener1\MyDB Log backup – \\BackupNAS\SQLBackups\ AGListener1\MyDB Log backup – \\BackupNAS\SQLBackups\ AGListener1\MyDB Log backup – \\BackupNAS\SQLBackups\ AGListener1\MyDB Log backup – \\BackupNAS\SQLBackups\ AGListener1\MyDB

After Failover: Log backup – \\BackupNAS\SQLBackups\ AGListener1\MyDB Log backup – \\BackupNAS\SQLBackups\ AGListener1\MyDB Log backup – \\BackupNAS\SQLBackups\ AGListener1\MyDB Log backup – \\BackupNAS\SQLBackups\ AGListener1\MyDB

Fail Back to Original Node:

Diff backup – \\BackupNAS\SQLBackups\ AGListener1\MyDB Log backup – \\BackupNAS\SQLBackups\ AGListener1\MyDB Log backup – \\BackupNAS\SQLBackups\ AGListener1\MyDB Log backup – \\BackupNAS\SQLBackups\ AGListener1\MyDB Log backup – \\BackupNAS\SQLBackups\ AGListener1\MyDB

Problem solved.  Now no matter which node you backup on, the files go to the same location.  And why not?  They’re the same database aren’t they?  So why complicate things by having them go to separate locations just because your AG failed over.

And it’s not just for AGs.  You can use a ServerLabel for any server you like.  Say you want to associate it with the DNS name of the server instead, or the application name.  That’s just as easy.  ServerLabel is here to give you a customizable server name to use in your path.

And it doesn’t stop there.  You can setup a ServerLabel for specific databases on a server, or even for specific backup types or backup types for specific databases.  It’s very flexible.

It’s such a tiny, unassuming feature, but it can have tremendous effects on your backup scenario.  You can see this http://midnightdba.itbookworm.com/Video/Watch?VideoId=428, and our other Minion Backup videos at http://midnightdba.itbookworm.com/Minion/Backup.

Leave a Reply