We just upgraded the Minion Maintenance suite!

Welcome to the next version of Minion Maintenance! Now you have a single installer for all three modules, plus new features and fixes for the entire backup and maintenance suite!

If you take a look at the page for Minion Backup, Minion Reindex, or Minion CheckDB, you’ll see that the download now says “Minion Maintenance 1.1”. Welcome to the next version!

Now you have a single installer for all three modules, plus new features and fixes for the entire backup and maintenance suite!

Minion Backup and Minion CheckDB got some important fixes, but it’s Minion Reindex that’s the real star of this release. MR was added to the unified installer, got on board the table-based scheduling bandwagon, and received an overall shine, polish, and upgrade. See “New in Reindex”, below!

Download and install

First, download Minion Maintenance from either Minion Backup, Minion Reindex, or Minion CheckDB…it’s the same system no matter where you download it!

Next, extract the files and install using the Minion Install Guide document that’s included in the download.

New in Reindex

New features:

  • Reindex heaps: New setting in the settings tables lets you choose whether to reindex heaps, or not.
  • Database groups: Minion Reindex DB groups for include and exclude.
  • Updated internals: MR now uses the DBMaintSQLInfoGet function, like MC and MB do.
  • Scheduling: The Minion.IndexMaintSettingsServer table provides table-based scheduling, just like Minion Backup and Minion CheckDB.
  • Install: New Powershell installer!
  • “Current” views: Minion.IndexMaintLogCurrent and IndexMaintLogDetailsCurrent show you the most recent set of activity.

Issues resolved:

  • Fixed: Some errors were not captured in the log tables.
  • Fixed: Japanese/international characters in a table name caused errors.
  • Fixed: Performance issues.
  • Fixed: Issue with object names in brackets. If object names have square brackets in the name, it will cause a syntax error. We now surround object names in double quotes instead of brackets.
  • Fixed: Issue with running PrepOnly then RunPrepped immediately after, in the same window.
  • Fixed: Formatting issues in Minion.HELP.
  • Fixed: Updated code “section” comments.

New in Backup

  • Added delete for SyncCmds
  • Fixed: Creating “Christmas tree folders” when using robocopy with multiple files.
  • Fixed: Missing a couple of entries in the InlineTokens table.
  • Fixed: Some sync commands being written to SyncCmds table even though SyncLogs is 0.
  • Fixed: Wasn’t logging properly when a DB is in an AG.
  • Fixed: Formatting issues in Minion.HELP.
  • Fixed: Wrong restore location when restoring to a named instance and the path is being converted from a local drive.  The instance name was being included in the UNC path.

New in CheckDB

  • Fixed: Data explosion for remote CheckDB results pull. Remote runs were pulling all of the CheckDBResult data instead of just for the current run.
  • Fixed: ServerName not populating correctly for remote CheckDB for the Minion Enterprise import.
  • Fixed: MinionTriggerPath wasn’t set correctly for the base folder.
  • Fixed: Databases with periods or other special characters may not import into Minion Enterprise for central reporting. Changed delimiter for the import process to accommodate this better.

Download Minion Maintenance from any of the maintenance pages today! Minion Backup, Minion Reindex, or Minion CheckDB.

Your Service Level Agreement is a Disaster

As a DBA, you’re in charge of getting systems up and running quickly in the event of an emergency. This is all right and proper, right up until you start defining SLAs. Let’s see what went wrong.

As a DBA, you’re in charge of keeping the systems healthy, and getting them back up and running quickly in the event of an emergency. This is perfectly right and proper, right up until you start defining a service level agreement.

A Service Level Agreement (SLA) defines the level of service you agree to provide, to get the system back up after a downtime. An SLA is usually expressed in terms of time. So, if you have a two-hour SLA, that means you agree that when there’s a grave issue, you’ll have the system back up within two hours.

But how did you get that two-hour SLA in the first place? Usually, it goes like this:

  1. The customer explains that they must have the database up within two hours of a problem.
  2. You don’t see any problems with that.
  3. Maybe there’s even a written agreement that you sign, knowing full well your database experience can easily handle a downed database in that time.

Like so many things, this sounds perfectly reasonable on the surface, but doesn’t hold up once it comes in contact with reality.

SLAs in the Real World

SLAs are often poorly thought-out, and very rarely tested. As a matter of fact, most companies don’t even have SLAs; they have SLOs. An SLO is a Service Level Objective – something you’re striving for, but don’t know for sure whether you can achieve. SLOs allow you to have some kind of metric, without bothering to test in advance whether the objective is even possible.

This lack of testing is the primary barrier to achievable SLAs. Lots of factors can impact your ability to get a system up and available after an issue. Let’s take a close look at just two of those factors: hardware failures, and data corruption.

Hardware Failures

When a hardware failure causes an outage, it can take a long time to get replacement parts. Companies work around this – sometimes – by keeping replacement components on hand for important systems, but that’s only sometimes. If your company has no replacements lying around, you are completely at the mercy of the hardware vendor. This can – no, will – demolish your SLA.

Now, maybe your company has an SLA with the parts vendor, and maybe they don’t. Typically, that vendor SLA will be something like a four-hour replacement window…but that’s just when they agree to show up! The vendor can’t promise that the parts will be installed, configured, and running in that time.

So, your two hour SLA won’t work with the four hours it takes just to get the hardware, and the two (or more) hours getting everything set up. Oh yes, plus the time to diagnose the issue in the first place, possibly reinstall SQL, restore the database, troubleshoot additional issues, or all the above. All of this puts you at least three times the SLA you agreed to support.

Conclusions:

  • Consider how long it takes to get replacement parts.
  • If your customer is important enough, keep replacement parts in-house.
  • Account for extra time, for troubleshooting, installation, configuration, restoring, more troubleshooting, and anything else that can come up.

Data Corruption

Database corruption is another outage scenario. Depending on the level of corruption, it may take anywhere from a few minutes to a few hours to diagnose and fix it.

For now, we’ll assume that it’s just a table that’s gotten corrupted. Now, is it the data, or just an index? If it’s an index, depending on the size of the table, it could be a quick fix, or it could be a couple hours. However, if it’s a table, then you may have to restore all or part of a database to bring it back. That brings another host of issues into the fray, like:

  • Do you have enough space to restore the database somewhere?
  • How long will it take?
  • Is the data even onsite?
  • How will you get the data back into the production table?
  • Do you bring the system down while you’re fixing it?

Of course, it could also be the entire database that’s down, in which case you will need a restore (assuming the corruption wasn’t present in the last backup). A few of the things you must consider:

  • Do you know how long that restore will take?
  • Have you done what is necessary to make sure you can restore quickly by tuning your backups, making sure the log is small, turning on IFI (Instant File Initialization), etc.?

Without some foresight, you could easily spend that two hour SLA window zeroing out 90GB of log file. Your 1.5 hours of data restore will put you quite a bit outside of your agreement.

Conclusions:

  • Make sure you have space to restore your largest database, somewhere off the production server.
  • Implement a good CheckDB solution, plus alerting.
  • Practice various recovery scenarios to see how long they take.
  • Make sure your backups are in order.
  • Practice database restores, and get your backups tuned. (Tuning your backups means you can tune your restores, too!)
  • Take these practice sessions into account when you make your SLAs.

The Solution

The conclusions above are a good start, but not at all the complete picture. If you’re keeping a single SLA for any given server, you’re doing yourself and your customers a huge disservice.

First, define separate SLAs for the different types of failure, and define what each specific failure looks like. For instance, if you define an SLA for database availability, define what ‘available’ means. Does it mean people can connect?  Does it mean that major functions are online?  Does it mean absolutely everything is online?  Does it include a performance SLA?  I’ve seen performance SLAs be included in downtime procedures because sometimes a database is so important, if the performance isn’t there, then it might as well be offline.

Next, review SLAs regularly. So, you’ve reasonably determined that you can accommodate a four-hour SLA for the DB1 database. What about, as the database grows?  Are you going to put in an allowance for the database tripling in size?  Surely you can’t be expected to hold the same SLA two years later that you did when the database was new.

Finally, test, test again, and then test one more time just to be sure. In fact, you should be testing your recovery procedures periodically so you can discover things that may go wrong, or lengthen the process. if you promised two-hour downtime and you can’t get your recovery procedures under that time, then you’ve got some re-working to do, don’t you?  Don’t just throw in the towel and say you can’t do it, because contracts may already be signed and you may have no choice but to see that it works. Maybe you’re really close to being able to hit the SLA, and you just have to be creative (and maybe, to work for a company that’s willing to spend the money).

Build Dynamic Backup Locations with Minion Backup

Minion BackupWhen you back up your database with a third-party party backup utility, you’re almost always stuck with their hard coded path structure and file names.  But Minion Backup – our free SQL Server backup tool –  gives you fully dynamic paths and file names. You can even programmatically change them based on any criteria.  We call this our Inline Tokens, and it’s incredibly powerful.

The Basics

In Minion Backup, the file and path info is stored in the Minion.BackupSettingsPath table, and it looks like this:

These are just a few columns from the table.  You’ll notice right away that BackupPath and FileName include SQL Server wildcards.  These are built-in parameters you can use to build your own strings.

Let’s look at a couple examples with FileName.

Default Backup File Names

It’s very easy to stripe your backup files with Minion Backup; when you stripe, it’s an excellent idea to number your files in the FileName itself. For example, “1Of3masterFull”, “2Of3masterFull”, and so on.

In Minion.BackupSettingsPath above, the FileName string looks like this:

%Ordinal%Of%NumFiles%%DBName%%BackupType%

Each of these wildcards stands for something:

  • %Ordinal% – The ordinal number of the current file in the stripe.
  • %NumFiles% – The total number of files in the stripe.
  • %DBName% – The name of the DB being backed up.
  • %BackupType% – Full, Diff, or Log.

So, the files for an AdventureWorks full backup look like this:

1Of5AdventureWorksFull.bak
2Of5AdventureWorksFull.bak
3Of5AdventureWorksFull.bak
4Of5AdventureWorksFull.bak
5Of5AdventureWorksFull.bak

Custom Backup File Names

That’s just the default setting, though.  Instead, you can choose a different filename format. For example:

%Ordinal%outOf%NumFiles%_%DBName%_%BackupType% would yield:

1outOf5_AdventureWorks_Full.bak
2outOf5_AdventureWorks_Full.bak
3outOf5_AdventureWorks_Full.bak
4outOf5_AdventureWorks_Full.bak
5outOf5_AdventureWorks_Full.bak

And, %DBName%_%BackupType%_%Ordinal%outOf%NumFiles% would produce files named:

AdventureWorks_Full_1outOf5.bak
AdventureWorks_Full_2outOf5.bak
AdventureWorks_Full_3outOf5.bak
AdventureWorks_Full_4outOf5.bak
AdventureWorks_Full_5outOf5.bak

A Wide Range of Customization

And there are many built-in parameters you can add.  Want to datestamp your files?  That’s easy.

%DBName%_%BackupType%_%Ordinal%outOf%NumFiles%_%Date%%Time% gives you:

AdventureWorks_Full_1outOf5_201703301100.bak
AdventureWorks_Full_2outOf5_201703301100.bak
AdventureWorks_Full_3outOf5_201703301100.bak
AdventureWorks_Full_4outOf5_201703301100.bak
AdventureWorks_Full_5outOf5_201703301100.bak

Minion Backup comes with about 25 built-in variables.  What’s more, you can add your own very easily, so if you’re not happy with one of the defaults, create your own!

Custom Backup Paths

So, there was a set of examples using filenames, but what about paths?  What kind of dynamic paths could you need?

  • Perhaps you want to change where a backup happens based on which datacenter you’re in.  With Minion Backup, you can detect which server you’re on and change the backup location based on that.
  • Or you can easily setup a monthly archive location, so Minion Backup backs up to a different location at the end of the month.
  • Or you can simply add the database name to your backup path – or the Availability Group name, or the AG listener name, or a ServerLabel you’ve assigned, or even put it in a special folder with the name of the month and day.

The sky’s the limit.

Minion Backup frees you from the rigid paths and filenames defined by a vendor.  Use your own naming conventions, and get exactly what you need. Minion Backup is available for free on MinionWare.net along with Minion Reindex and Minion CheckDB.


Enhance Your DBA Brain

Sign up for the MinionWare newsletter to get:

– The eBook “DBA Rants from a Microsoft Certified Master”
– Our best articles on SQL backup and maintenance
– Links to free MinionWare tools
– News, updates, and tips!

 

Distinguish backup file names or pay the price!

So far, no one has found exercise to be beneficial to servers. Purposeless repetitive motion may be good for human muscles, but your SQL Server instance experiences no gain for the pain. Here’s a good example: taking useless backups. Let me explain…

Running around a track
SQL instances shouldn’t do this

So far, no one has found exercise to be beneficial to servers. Purposeless repetitive motion may be good for human muscles, but your SQL Server instance experiences no gain for the pain.

Here’s a good example: taking useless backups.

(“Did she say useless backups? I’ve never heard of such a thing!” Yeah, just wait.)

Backup file names are critical

Traditionally, backup files are named after the database and the backup type, and given a timestamp. So you’ll see something like master_FULL_20170101.bak. If you like to stripe your backups, you might name the files something like 1of5MasterFull20170101.bak, and so on.

But I have run across shops that takes backups without bothering to time stamp the file name: master_FULL.bak.  These shops either overwrite each backup file, with each successive backup (using INIT and FORMAT), or add to the backup set (which I find mildly annoying, but to each their own).

The problem with using the same backup file name over and over is if you have a cleanup mechanism that deletes old backup files!

The same-name cleanup issue

Let’s say that in your shop, you have Minion Backup (MB) installed and running with the following options:

  • INIT is enabled
  • FORMAT is enabled
  • Backup file retention (in hours) is 48, so we’ll keep 2 days’ worth of backups
  • Backup name is set to %DBName%%BackupType%.bak, which works out to DB1Full.bak for a full backup of DB1.

Note: For more information on the %DBName% and %BackupType% inline tokens, see “About: Inline Tokens“ in our help center.

Here is what happens:

  1. On day 1, MB takes a backup of DB1, to \\NAS1\SQLBackups\DB1Full.BAK.
  2. On day 2, MB takes a backup of DB1, which overwrites the file \\NAS1\SQLBackups\DB1Full.BAK.
  3. On day 3, MB takes a full backup of DB1 (which overwrites the same file). And then the delete procedure sees that it has a file from day 1 (>48 hours ago) that needs deleting. And so it deletes \\NAS1\SQLBackups\DB1Full.BAK. Remember, this is the file that MB has been overwriting again and again.
  4. On day 4, MB takes a backup of DB1, to \\NAS1\SQLBackups\DB1Full.BAK.. Then, it sees that it has a file from day 2 that needs deleting, and so deletes \\NAS1\SQLBackups\DB1Full.BAK.

See? From Day 4 on, we’re creating a backup file just to delete it again!

Fixing the issue if you have MB

One excellent way to figure out if you have this problem is to notice that, hey, you don’t have any backup files. Another indicator in Minion Backup is if you see “Complete: Deleted out-of-process. SPECIFIC ERROR:Cannot find path ‘…’ because it does not exist.” in the Minion.BackupFiles Status column.

But the real smoking gun is if you haven’t time stamped your backup files in Minion.BackupSettingsPath. Here’s how to fix that:

That will give each file its own name, and eliminate the chance of this ever happening.

And it will prevent your server from performing useless exercise. Me? I’m going to hit the gym.

SQL maintenance is a lifecycle, not an event!

You’ve heard me talk about this many times, in so many different ways, but it’s worth repeating: SQL maintenance lifecycles are important.

People who disagree, disagree because they spend too much time firefighting and don’t have time to really think about it. Don’t know anything about SQL maintenance lifecycles? Let’s touch on a couple of points…today, we’ll cover SQL backup.

You’ve heard me talk about this many times, in so many different ways, but it’s worth repeating: SQL maintenance lifecycles are important.

People who disagree, disagree because they spend too much time firefighting and don’t have time to really think about it.  Don’t know anything about SQL maintenance lifecycles? Let’s touch on a couple of points…today, we’ll cover SQL backup.

SQL maintenance lifecycles: Backups

Backups have the biggest lifecycles of all, because the uses for backup files are so varied.

They’re used for restoring databases to the original server, sure.  But here’s a short list of the other kinds of things you need to do with backup files:

  • Restore to development, QA, etc.
  • Send to vendor
  • Initialize replication
  • Initialize Availability Groups
  • Restore to a secondary server to run CheckDB

A backup is not a single event

A database backup isn’t a single event.  But unfortunately, nearly every vendor out there – and most DBAs – treat backups as a single event that has a start and a finish.  “Look, I took a SQL backup and produced a file, and now I’m finished!”  But that’s not remotely the case.  We use and shuffle SQL backup files around all day and all week long on our various servers.

This is why I’m so against those tools that I call “network backup tools”.  You know the ones: Backup Exec, AppAssure, etc.  Your network admin loves those tools, because he doesn’t understand SQL and he wants the same backup utility for the whole shop.

But network backup tools simply aren’t going to cut it for SQL maintenance lifecycles, because they take snapshots of the data and log files.  And quite often those two files aren’t fully synchronized when the snapshot takes place, so you wind up creating a new log file and losing whatever data was in there.  Not only that, but you can only attach those files somewhere, usually on the same server they came from.

Without the understanding that your maintenance has a lifecycle, you wind up with a lot of piecemeal solutions.  You write something to restore a database on a development box, then another DBA does the same thing, then another, then another.  Soon you have a huge mix of solutions that need support, all created by a mix of DBAs over the years…it’s an unmanageable web of solutions, each one with its own quirks and limitations.  That’s no way to run a shop, and it’s no way to support your data.

SQL Maintenance Lifecycles: SQL backup done right

Minion BackupThis is why we’ve made Minion Backup the way we have.  Minion Backup is the only backup tool on the market that treats your environment holistically.  Minion Backup understands your SQL maintenance lifecycle and it works for you to provide you built-in solutions that you just deploy.  There’s no need to write your own processes, or manage something separate because it’s built into Minion Backup by default.

Let’s take for instance that you need to back up your SQL certificates, because you know, who doesn’t?  With Minion Backup you can easily backup every certificate, even to multiple locations.  In fact, you can back up your certificates to as many locations as you need.  This functionality is built into Minion Backup, and setting it up is a simple table setting.

Not only that, but since we store the private key password encrypted, and give it to you in the log, you can even rotate the private key passwords on any schedule you like to increase your security.  This way if you change the private key password every month, then you’ve always got access to the encrypted value in the log so you can restore that certificate with ease.  This is what we mean by SQL maintenance lifecycle management.  All of our SQL maintenance products have this level of lifecycle management.  And we’re improving them all the time.  We work closely with our users to give them built-in solutions that matter.

All of our SQL maintenance products are completely FREE, so download them and try them now.  And if you like what you see, then try our enterprise products and see the power of bringing your SQL maintenance lifecycles into the enterprise architecture.

Download our FREE maintenance modules below:

  • Minion Backup: Free SQL Server Backup utility with world-class enterprise features and full lifecycle management.
  • Minion Reindex: Free SQL Server Reindex utility with unmatched configurability.
  • Minion CheckDB: Free SQL Server CHECKDB that solves all of your biggest CHECKDB problems.

 

Integrity Checks are Useless*

Integrity checks have become more and more important over the years, as companies store more and more data. This data is absolutely critical, so when corruption happens, it can break a company. But in all but the smallest shops, running any kind of integrity check is all but completely useless. Unless you have Minion CheckDB.

Integrity checks have become more and more important over the years, as companies store more and more data. This data is absolutely critical, so when corruption happens, it can break a company.

But in all but the smallest shops, running any kind of integrity check is all but completely useless.

Integrity checks without management

DBCC CHECKDB was originally written without consideration of management. For a very long time, you could only output CHECKDB results to the client – meaning you had to run it by hand – or to the error log. Seriously, what good is that? Later, Microsoft added the ability to output CHECKDB results to a table, but that remained undocumented for years so very few people knew it even existed.

That’s not the only problem. A lot of DBAs don’t run CHECKDB at all, because large databases take a long time to process. CHECKDB takes an internal snapshot for its operations. For large databases, that snapshot can fill up the drive, killing the integrity check process entirely. So, you have nothing to show for your efforts. Some DBAs tried to get around this issue by moving to CHECKTABLE instead, but that process comes with the same issues, and is even harder to manage because there are so many more tables than databases.

Integrity checks without reporting

Another reason that running DBCC CHECKDB is useless is this: reporting is still very difficult. Let’s say you have 50 servers – yes, I know a lot of you may have many more than that – and CHECKDB is set to run through a maintenance plan, outputting results to the SQL error log. In this scenario, you have to mine the log for CHECKDB results. That means you not only have to know when the CHECKDB completes on each and every server, but you also have to know what the log entry will look like, and how to parse out the results so they’re readable…again, on every single one of 50 servers. There is simply no easy way to consume CHECKDB results on any kind of scale.

Useful integrity checks with Minion CheckDBMinion makes integrity checks useful again

The whole goal of Minion CHECKDB was to transform these issues surrounding integrity check processing into simple settings. Now DBAs of every skill level can have a stable, standardized integrity check process for even their largest databases.

Minion CHECKDB has a huge number of groundbreaking features. MC solves the reporting problem by capturing integrity check results to our own log table, along with a huge amount of additional information. Now it just takes a simple query to get the results. Instead of the native raw CHECKDB results, the MC log table tells you how many corruption errors were encountered. Then you can drill deeper from there if you like.

We’ve also solved the problem of the large database that can’t be processed because it runs out of snapshot space. In fact, we’ve solved that a few ways, and I’ll discuss a couple of them here.

  • You can create a custom snapshot on a different drive that has more space. MC manages those snapshots for you, so it’s literally a set and forget operation.
  • Or, you can restore a recent backup of your database to a remote system and run CHECKDB against that instead. Remote CHECKDB even brings the results back to the primary server, so you don’t have to go hunting for them. Again, this is a setting; you configure it, and then MC manages the whole process for you.

Of course, there’s more

There are many more features in Minion CHECKDB that I didn’t mention. But since MC is completely free, you can download it and install it on as many instances as you want. Go explore!

Put MC on all your systems and start doing integrity checks again. We’ve taken away all the excuses. And when coupled with Minion Backup and Minion Reindex (both also free), you’ve got a maintenance suite that meets all your needs from planning to reporting, and everything in between.

 

*Without proper management and reporting!

Managing Backup Issues in Availability Groups

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.

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

Availability Groups: Managing Backup Issues

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!

Proper Backup Alerting

Today I’d like to talk about two topics that get overlooked quite often, the “backups” to the backup, so to speak. First up: proper backup alerting. And second, missing backup recovery.

tsql2sday150x150When I saw the topic for T-SQL Tuesday this time I just had to get in. Maybe I’ve never mentioned it, but backups is one of my big things.  Today I’d like to talk about two topics that get overlooked quite often, the “backups” to the backup, so to speak. First up: proper backup alerting.  And second, missing backup recovery.

Traditional alerting falls short

Well, let’s begin with a story from my days as senior DBA. Years ago, one of the application groups messed something up in their database, and they needed a restore.  “Sure thing,” I said.  No problem.  So I went to the backup drive, and there wasn’t anything that could even be vaguely considered a fresh backup. The last backup file on the drive was from about three months ago.

OOPS.  Oh crap…so what do I tell the app team?

First, a little investigation.  I had to find out why the backup alert didn’t kick off. Every box was set up to alert us when a backup job failed.  I found the problem right away.  The SQL Agent was turned off.  And from the looks of things, it had been turned off for quite some time.  And as you may realize, there’s just no way to alert on missing backups if the Agent is off and can’t fire the alert.

But that was just the first part of the problem.  The SQL Agent couldn’t send the email, of course. But the job never actually failed, because it didn’t start in the first place.

This is the crux of the issue: jobs that don’t start, can’t fail.  Alerting on failed backup jobs isn’t the way to go.

“But it’s okay, we have…”

Hold on, I know what you’re thinking. You have service alerts through some other monitoring tool, so that could never happen to you! To  degree, you’re right. But let’s see what else can go wrong along those same lines:

  • The database in question isn’t included in the backup job.
  • The network monitor agent was turned off, or not deployed to that server.
  • SMTP on the server has stopped working.
  • The backup job has the actual backup step commented out.
  • Someone deleted that backup job.
  • Someone disabled the backup job, or just disabled the job’s schedule.

Service alerts won’t help you in any of these circumstances.

Proper backup alerting

I’ve run into every one of those scenarios, many times.  And there are only two ways to mitigate every one of them (and any other situation you come across) with proper backup alerting.

Number one: Move to a centralized alerting system.  You can’t put alerts on each of your servers. When you do that, you’re at the mercy of the conditions on that box, and those conditions can be whimsical at best.

Move the backup alerts from the server level to the enterprise level.  Then, when there’s an issue with SMTP or something, you only have one place to check. It’s much easier to keep track of whether  an enterprise level alerting system isn’t working than to keep track of dozens, hundreds, or even thousands of servers.  After all, if you haven’t heard from a server in a long time, how do you know whether it’s because there’s nothing to hear, or if the alerting mechanism is down?

Number two: Stop alerting on failed backups.  Alert on missing backups.  When you alert on missing backups, it doesn’t matter if the job didn’t kick off, if the database wasn’t part of the job, or if the job was deleted.  The only thing that matters is that it’s been 24 hours since your the backup. Then when you get the alert, you can look into what the problem is.

The important point is that the backup may or may not have failed, but your enterprise alert will fire no matter what.  This is a very effective method for alerting on backups, because it’s incredibly resilient to all types of issues…not only in the backups, but also in the alerting process.  If you do it right, it’s just about foolproof.

Part 2: Missing Backups

Handling missed backups is not the same as alerting on missing backup (like we talked about above). What we want to do is avoid the need for the alert to begin with.

Minion Backup (which is free, so we get to talk about it all we want, ha!) includes a feature called “Missing Backups”, which allows you to run any backups that failed during the last run.  

Here’s what this looks like:  You set your backups to run at midnight, and they’re usually done by around 2:00 AM.  However, occasionally they fail for one reason or another. Then you get an alert in the middle of the night, and you have to get up to deal with it.

Missing Backups lets you set Minion Backup to run again at, say, 2:30 or 3:00 AM with the @Include = ‘Missing’ parameter.  This will look at the last run and see if there were any backups that failed; if there were, then MB will retry them.  This will prevent the need for alerts in the first place.

We use this feature in many shops we consult in because we see databases that fail from time to time for weird reasons, but they always pass the second time.  So Minion Backup helps improve your backups simply by giving you a second chance at your backups.

Now we mention Minion Enterprise

We’ve got you covered for enterprise-level alerting, too.  Our flagship product, Minion Enterprise, was made for just that purpose and it comes with many enterprise-level features; not just backup alerting.  I invite you to take a look at it if you like.

But if you don’t then by all means, write yourself an enterprise-level alerting system and stop relying on alerts that only fire on missing backups.

And, improve your situation in general by switching to the free Minion Backup.

Announcing Minion CheckDB release date!

Minion CheckDB is the third piece of our free backup and maintenance tools, rounding out the list with Minion Backup and Minion Reindex…and it’s coming in early 2017.

productimg_checkdbMinionWare will release Minion CheckDB on Febuary 1, 2017!

Minion CheckDB 1.0

Minion CheckDB is the third piece of our free backup and maintenance tools, rounding out the list with Minion Backup and Minion Reindex. CheckDB will have the same native interface, the same configurability, and the same rich scheduling introduced in Minion Backup. And of course, it will have a the same kind of visionary features that users have come to expect from MinionWare products, like:

  • Rich logging
  • Automated rotating schedules for objects
  • Automated remote CheckDB operations
  • Automated choice of whether databases get a DBCC CheckDB operation, or a series of DBCC CheckTable operations
  • And much more!

Minion Backup 1.3

What’s more, we will also release Minion Backup 1.3 on that date! New MB features include:

  • Our new dynamic naming functionality (called named parts)
  • Improved restore process
  • Additional advances both for their own sake and to support Minion CheckDB

 

Subscribe to the MinionWare newsletter for news and updates about Minion Enterprise, backup and maintenance, and more.

Back up databases to multiple locations

Many IT shops need multiple copies of a backup saved to multiple locations, for disaster recovery, or for testing and quality assurance. Here we discuss the difference between backup mirror and copy, and explain how to implement each in Minion Backup.

Minion Backup logoMany IT shops need copies of a backup saved to multiple locations. Sometimes it’s a disaster recovery thing. After all, it is a very good idea to have at least one “quick access” backup, and one offsite backup.

Sometimes multiple backups have more to do with testing and quality assurance. For example, we frequently restore production databases to test servers.

So what’s the best way to back up your databases to multiple locations?

Option 1: Mirror the Backup

One option is to perform a mirrored backup, which is a native feature of SQL Server backups. In Minion backup, mirroring your backup is a simple process:

  • Enable mirrored backups in the Minion.BackupSettings table by setting Mirror = 1.
  • Configure a backup mirror path in Minion.BackupSettingsPath, being sure to set isMirror = 1. (It’s very important that you have a primary backup path, in addition to the mirror backup path.)

The downside to mirroring your backups is that two backup locations means twice the chance of backup failure! If something goes awry with the mirror backup, the primary backup will fail, too.

Another downside is that mirrored backups are only available on SQL Server Enterprise.

For more information on mirrored backups, use the command
EXEC Minion.HELP ‘backup’, ‘How to: Set up mirror backups’;
in Minion Backup, or see that section in the help document posted on MinionWare.net/backup/

Option 2: Copy the Backup

With Minion Backup, you can choose to copy your backup files to one or more locations as part of your backup routine. This feature is very flexible:

  • perform the file move after the batch, or immediately after the backup completes
  • choose which utility to use to perform the copy: COPY, MOVE, XCOPY, ROBOCOPY, or ESEUTIL
  • define any number of copy paths

To configure copy operations for backup files:

  1. Configure when the copy will happen in the Minion.BackupSettings table, using FileAction = ‘COPY’ and FileActionTime = ‘AfterBatch’ (or, AfterBackup).
  2. Insert one row per copy path into the Minion.BackupSettingsPath table.

For example, to copy all backups from a server to two new locations, we first enable COPY / AfterBatch:

UPDATE Minion.BackupSettings

SET FileAction = ‘COPY’ ,

  FileActionTime = ‘AfterBatch’;

Second, set up the two backup copy paths:

INSERT INTO Minion.BackupSettingsPath (DBName, IsMirror, BackupType,

BackupLocType, BackupDrive, BackupPath, RetHrs, FileActionMethod,

FileActionMethodFlags, PathOrder, IsActive, Comment )

SELECT ‘MinionDefault’ AS DBName
, 0 AS IsMirror
, ‘COPY’ AS BackupType
, ‘NAS’ AS BackupLocType
, ‘\\QWERTY\’ AS BackupDrive
, ‘SQLBackup\’ AS BackupPath
, ‘168 ‘ AS RetHrs
, ‘COPY’ AS FileActionMethod
, NULL AS FileActionMethodFlags
, 20 AS PathOrder
, 1 AS IsActive
, ‘Copy path 1’ AS Comment
UNION
SELECT ‘MinionDefault’ AS DBName
, 0 AS IsMirror
, ‘COPY’ AS BackupType
, ‘NAS’ AS BackupLocType
, ‘\\ASDF\’ AS BackupDrive
, ‘SQLBackupCopies\’ AS BackupPath
, ’72’ AS RetHrs
, ‘COPY’ AS FileActionMethod
, NULL AS FileActionMethodFlags
, 10 AS PathOrder
, 1 AS IsActive
, ‘Copy path 2’ AS Comment;

From here on out, each batch of backups will run. Then after the backups, MB will copy the files to the \\QUERTY location, and then to the \\ASDF location. (Note that the order is determined by the PathOrder field.)

For more information on copying backup files, use the command
EXEC Minion.HELP ‘backup’, ‘How to: Copy files after backup (single and multiple locations)’;
in Minion Backup, or see that section in the help document posted on MinionWare.net/backup/

 

Download the FREE Minion maintenance modules:

  • Minion Backup: Free SQL Server Backup utility with world-class enterprise features and full lifecycle management.
  • Minion Reindex: Free SQL Server Reindex utility with unmatched configurability.
  • Minion CheckDB: Free SQL Server CHECKDB that solves all of your biggest CHECKDB problems.