This article explains how backup alert thresholds work in Minion Enterprise, and how you can easily configure different alerting scenarios to suit all your needs.

Backup alert thresholds are stored in the dbo.BackupReport table.  This table is the central place to configure all the backup alert thresholds for your entire environment.

Backup alert thresholds don’t require Minion Backup

A common misconception with Minion Enterprise is that you must be using Minion Backup in order to get the alerting benefits. That’s just not true.  There are some benefits to using Minion Backup with Minion Enterprise, and the benefit in alerting is that with Minion Backup integration, alerts will include the error message from the backups as well as any warnings from the rest of the process.  Otherwise, alerting works the same whether you’re on Minion Backup or another product. 

As long as the backup being taken on your servers updates the msdb database – and all native SQL backups do – you’ll be able to alert on it. 

The last backup date data is stored in Collector.DBProperties;  that’s where the alert gets the values to compare against the dbo.BackupReport table.

The columns in dbo.BackupReport  are:

*Be very careful when specifying log thresholds.  Notice this value is in minutes, while the other two thresholds are measured in hours.

Conceptually, using this table is very easy.  It starts with the pervasive ME concept that global values are set, and then only overridden for exceptions.  So:

• A default row, covering all instances, will have InstanceID=0 and DBName = ‘MinionDefault’.
• To set different thresholds for all ‘master’ databases on all instances, enter a row with InstanceID=0 and DBName=’master’.
• And so on.

Manage Backup Alert Thresholds with Setup.BackupReportThreshold

You can certainly manage this table manually with Insert/Update/Delete statements. But, it’s easier to use the procedure provided: Setup.BackupReportThreshold.  Not only is it easier, but it has standardized functionality that helps remove human error out of the process. 

The documentation for Setup.BackupReportThreshold can be found here.  It also has examples on how to configure your environment in the different scenarios you’ll come across.

How to handle Missing Backup alerts: Fix, Defer, or Except

When you first install Minion Enterprise, you might start receiving missing backup alerts. You have several options for handling these alerts: resolve, change the threshold, or defer the alert. 

Change the Threshold

Change the backup alert threshold, if the instance in question has less frequent backups:

EXEC Setup.BackupReportThreshold
@ServerName = 'MyServer',
@DBName = 'MinionDefault', -- This will apply to all databases
@BackupType = 'Full', 
@Threshold = 14; -- as this is a Full entry, threshold is in hours.

This inserts or updates the appropriate row to dbo.BackupReport.

Change the Threshold for a single database

Change the backup alert threshold for a single database , if the database in question has less frequent backups:

EXEC Setup.BackupReportThreshold
@ServerName = 'MyServer',
@DBName = 'MyDB', 
@BackupType = 'Full', 
@Threshold = 60; -- as this is a Log entry, threshold is in minutes.

This inserts or updates the appropriate row to dbo.BackupReport.

Defer the Alert

Defer the instance in question from alerts for a specific period of time, if the resolution is coming (and you don’t want alerts in the meantime):

EXEC Setup.BackupDefer
@ServerName = 'MyServer',
@DBName = 'MinionDefault',
@BackupType='Full',
@DeferDate = '08/20/2015',
@DeferEndDate = '08/20/2015',
@DeferEndTime = '06:00';

This inserts a row to the table Alert.BackupDefer. 

Exclude the Database from Alerting

Exclude a server or database from alerting, if it isn’t your responsibility or should otherwise not be alerted on. To do this, insert or update a row to dbo.BackupReport, and set the appropriate "Report" column to 0. For example:

To prevent all missing backup reporting for all databases on server MyServer (InstanceID = 4) - when there is no existing row for MyServer - run the following:

INSERT INTO dbo.BackupReport (InstanceID, DBName, ReprtFull, ReportDiff, ReportLog, IsActive)
VALUES (4, 'MinionDefault', 0, 0, 0, 1);

To prevent missing backup reporting for one database (MyDB) on server MyServer (InstanceID = 4) - when there is no existing row for MyServer - run the following:

INSERT INTO dbo.BackupReport (InstanceID, DBName, ReprtFull, ReportDiff, ReportLog, IsActive)
VALUES (4, 'MyDB', 0, 0, 0, 1);

 Special Case Databases

You can have some special case databases that you want to behave differently than the others.  This may be because the DB has changed status and therefore requires special treatment. 

A perfect example of this is with read-only databases.  I'll outline the situation so you can see the issue with RO (read-only) DBs, and how Minion can make it easy.

Let's say that  you've got a server with some RO DBs, and some RW (read-write) DBs.  You're likely backing up the RW DBs nightly, or weekly, but you're likely only backing up the RO DBs monthly, or even less than that.  Well, in this case you don't want to alert on missing backups for the RO DBs on the same schedule as the RW DBs.  You know you're not backing them up every night, so to have them alerted on every 24hrs is just dumb, and it creates false positives in the alerts.  Currently, you can have individual overrides for DBs, so you can give each one of these RO DBs its own alert thresholds, but that's more manual than you need it to be.  You shouldn't have to manually manage something like that. 

For starters, you could have certain DBs being archived every month, so they're being made RO.  This is a common scenario and you shouldn't have to remember to go into Minion and configure an override for this month's archive DBs.

This is where the special case DBs come in.  Here, instead of making individual overrides for the DBs as they become RO, you set a special case for RO DBs for that server, and the DBs automatically use those new settings when they become RO.  For example, let's say that you have 300 DBs on Server1, some are RW and some are RO, but every month, you archive a new set of DBs and put them RO mode.  And of course, you also probably create new DBs to handle the new data.  This is the cycle of the server; older DBs get archive and put into RO mode, and new DBs are created for the newer data. 

Now let's look at how the reporting for this server can be accomplished.

In the above table, we've got InstanceID 1 where every DB is alerted after 24hrs.  But DB1 and DB2 are RO and they're alerted on every month (672hrs), and logs aren't reported on at all.  So without the special case DBs, it's possible to manage RO DBs on a server, or rolling RO DBs as I like to call them.  Now DB3 is about to be made RO and when it is, you'll need to add another override row so it gets handled on the right alerting schedule.  Let's see how this would be handled with the special case.

Now you can see how the scenario has been simplified.  DB1 and DB2 are still RO, but now they're covered by the special case DB.  And when DB3 gets made RO in a few days, it'll automatically be covered by the special case so there's nothing to do.  You could have a global-level special case as well if you like. 

However, special cases never apply to a single DB.  This simply wouldn't make sense because the DBName has to match and you can always give an individual override for a single DB.

Currently, the only special case supported is:

Minion Case: ReadOnly

Also, as a point of reference, Minion Backup is free and it makes it really easy for you to manage this type of RO and RW backup schedule.  You can easily backup RO DBs say on the first of the month, or on the last of the month, while still backing up your RW DBs on their regular schedule.

Related Topics

Deferring Alerts

Night Mode

Setup.DeferMaster

Configuring Report Options