SQL database backup monitor a different approach


There are several samples on how to monitor the last date a SQL database has been backed up, to mention a few:

For my environment they lack two common functionalities:

  1. being able to differentiate when the monitor runs, for example some of our customers do not have a 24×7 business so don’t backup their database during the weekend. In these cases I don’t want to run my monitor on Monday morning.
  2. leverage cookdown, it’s not uncommon to have sql servers with tens of dbs, some of them have more than 100 databases, I don’t want to spawn a script for each database

So what I basically did is to use the oledb provider to run my monitor and add a full scheduling datasource. Fast, clean and no scripting to maintain. You can find the library MP used in this post here: SQL Library.

You can use the Monitor Type defined in the library (QND.SQLServer.Library.DBBackupTime.MonitorType) in your own MP with a snippet like the following:

<UnitMonitor ID="QND.SQLServer.2008.DBBackupTime.Monitor" Accessibility="Public" Enabled="true" Target="SQL2008Core!Microsoft.SQLServer.2008.Database" ParentMonitorID="SystemHealth!System.Health.ConfigurationState" Remotable="true" Priority="Normal" TypeID="QNDSQLLibrary!QND.SQLServer.Library.DBBackupTime.MonitorType" ConfirmDelivery="false">
  <Category>ConfigurationHealth</Category>
  <AlertSettings AlertMessage="QND.SQLServer.2008.DatabaseBackupTime.AlertMessage">
    <AlertOnState>Warning</AlertOnState>
    <AutoResolve>true</AutoResolve>
    <AlertPriority>Normal</AlertPriority>
    <AlertSeverity>MatchMonitorHealth</AlertSeverity>
    <AlertParameters>
      <AlertParameter1>$Target/Property[Type="SQL!Microsoft.SQLServer.Database"]/DatabaseName$</AlertParameter1>
      <AlertParameter2>$Target/Host/Property[Type="SQL!Microsoft.SQLServer.ServerRole"]/InstanceName$</AlertParameter2>
      <AlertParameter3>$Target/Host/Host/Property[Type="Windows!Microsoft.Windows.Computer"]/NetworkName$</AlertParameter3>
      <AlertParameter4>$Data/Context/Columns/Column[3]$</AlertParameter4>
      <AlertParameter5>$Data/Context/Columns/Column[4]$</AlertParameter5>
    </AlertParameters>
  </AlertSettings>
  <OperationalStates>
    <OperationalState ID="MonitorStateWARN" MonitorTypeStateID="MonitorStateWARN" HealthState="Warning" />
    <OperationalState ID="MonitorStateOK" MonitorTypeStateID="MonitorStateOK" HealthState="Success" />
  </OperationalStates>
  <Configuration>
    <IntervalSeconds>86400</IntervalSeconds>
    <SyncTime>08:00</SyncTime>
    <TimeoutSeconds>300</TimeoutSeconds>
    <ConnectionString>$Target/Host/Property[Type="SQL!Microsoft.SQLServer.DBEngine"]/ConnectionString$</ConnectionString>
    <ThresholdHR>24</ThresholdHR>
    <DaysOfWeekMask>127</DaysOfWeekMask>
  </Configuration>
</UnitMonitor>

<DisplayString ElementID="QND.SQLServer.2008.DatabaseBackupTime.AlertMessage">
  <Name>Database backup age</Name>
  <Description>Database {0} in SQL Server instance {1} on computer {2} has been backed up {4} hours ago on {3}.
  Accordingly to the threshold the backup set is too old.</Description>
</DisplayString>

For those who are curious on how this works here are the details.

We have three module types:

  1. a Probe that does the core job
  2. a Data Source who schedules the probe
  3. a Trigger useful in on demand scenarios or console task (on demands are not implemented see Permanent link to On Demand Detections caveats (a cookdown story))

The probe configuration takes four parameters:

  1. The query timeout in seconds
  2. A boolean to split or aggregate returned rows
  3. The connection string
  4. And the number of hours after which to consider the database in troubles

image

The probe call an oledb module with the following query (taken with a few changes from the referred articles). As you can see I add the threshold parameter in the output so that I can use it in my alert description string if I want to.

image

Now the datasource is simply the composition of a scheduler plus a scheduler filter to able to choose on which days to run the monitor and the probe (I always split my MPs in this basic components to improve reusability see Standard Module Composition for monitors). The datasource adds the scheduling specific parameters to the picture (you can find the common pattern used to limit the monitor to specific business hours of my previous posts):

  1. The scheduling in seconds "IntervalSeconds"
  2. The sync time (to be able to run the monitor each morning at 08:00 for example if you want)
  3. The days of the week mask to be able to run the monitor just when needed (http://msdn.microsoft.com/en-us/library/ff453828.aspx)

image

Now we have all the building blocks for the monitor type, the monitor configuration will be exactly the same as the datasource

image

the only important thing is the condition detection to filter the right database from the returned oledb data, this is necessary to properly implement cookdown and have in the state change context only the relevant data

image

this will lead to the following composition for regular detections

image

As previously stated I defined a trigger probe type useful for on demand detections, I didn’t implement on demands because of their current limits (see my previous link), but if you want to try here it is:

image

In the end this is just another approach to last database backup time monitoring that adds cookdown and business hours filtering getting rid of scripting. Hope it is useful.

– Daniele

This posting is provided "AS IS" with no warranties, and confers no rights.

Advertisements
  1. Drupal : Part 1, Installing the Framework

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: