products:promonitor:6.8:monitorsguide:mssql:ms-sqlbackups

MS-SQL Backups
Purpose
Configuration hints
Surveillance table
Examples
Generated metrics

MS-SQL Backups

Purpose

When a disaster strikes SQL Server databases, a efficient working backup is essential in order to recover from most disasters
Backup will also help to protect against database upgrades that go bad, table corruption, and other system problems.
Monitoring database backup execution results becomes then crucial in order to guarantee Database Administrators that a full database recovery is possible, database damages etc..

Monitor features include :

Monitoring of every SQL Server database backup tpe (Full, Differential, Transaction log …)
It is able to alert if time elapsed from last backup (of any type) has reached a given threshold expressed in number of days, hours, or minutes
It is capable to retrieve duration time of a given backup and to alert in case a threshold is reached
It will also send an alert in case a backup size overreached a threshold set by the user
The monitor can be configurated so to trigger an alert if the database has been damaged

Configuration hints

Surveillance table:

This is a table of rules that you can use to configure and customize the configuration.
Each line of the table will define a rule of monitoring. You can combine multiple rules to cover different cases.
Within a rule, you can configure the last database backup type you want to monitor, the elapsed time from last backup that you can express in days, hours or minutes, the duration time in seconds of last backup
You can also seta severity level alert if the database is detected as damaged

Backup type
Select the specific backup type you want to monitor or select “All” to monitor any kind of backup that has been made of the database

Elapsed time from last backup:
Use the multi-threshold syntax to set multiple threshold/severity associations: G2W:1 W2M:2 M2C:3 (Green To Warning, Warning To Major, etc…), expressed in days, hours or minutes An alarm of the corresponding severity will be sent if time eapsed from last backup is over threshold. Set 0 in the field if unused

Duration time in seconds:
Use the multi-threshold syntax to set multiple threshold/severity associations: G2W:60 W2M:120 M2C:180 (Green To Warning, Warning To Major, etc…), expressed in seconds An alarm of the corresponding severity will be sent if backup time duration is over threshold. Set 0 in the field if unused

Backup size in MB:
Use the multi-threshold syntax to set multiple threshold/severity associations: G2W:512 W2M:1024 M2C:2048 (Green To Warning, Warning To Major, etc…), expressed in MB An alarm of the corresponding severity will be sent if backup size is over threshold. Notice that values retrieved will be rounded Set 0 in the field if unused

Damaged DB:
Set a alert severity level if the database has been detected as damaged and you want to be notified. Set it to “DISABLED” to deactivate the alert

Surveillance table

Parameter	Description
Active	Use this field to activate or deactivate a line of configuration.
Backup type	Select the specific database backup type you want to monitor or select “All” so to monitor all types of backup
Elapsed time from last backup	The threshold for the elapsed time from the last backup.You can use the multi threshold syntax (i.e. : G2W:10 W2C:40) . Values can be expressed in : Days, Hours or Minutes
Unit	Time unit of measure of “Elapsed time from last backup” field
Duration time in sec	The threshold setting for the last backup duration time in seconds.You can use the multi threshold syntax (i.e. : G2W:10 W2C:40) . Values have tobe expressed in seconds
Backup size in MB	The threshold setting for the last backup size in MB.You can use the multi threshold syntax (i.e. : G2W:10 W2C:40) . Values have tobe expressed in MB
Damaged DB	Set a severity level if you need to be alerted if the database has been detected as damaged.
Auto clear	If checked, the alarm will be cleared as soon as the alarm condition is not met anymore.
Alarm tag	This field allows to add custom text within the alarm message. %MSG% variable will contain the actual generated message and can be used such as: “my_prefix %MSG% my_suffix”. By default, tag will be used as prefix.
Alarm	If checked, this line of surveillance will be used for alarm generation.
Metric	If checked, this line of surveillance will be used for metric generation.
Report	If checked, this line of surveillance will used for showing threshold and severity in the daily report

Examples

Active	Backup type	Elapsed time from last backup	Unit	Duration time in sec	Backup size in MB	Damaged DB	Auto clear	Alarm tag	Alarm	Metric	Report
true	Full	G2W:1 W2M:3	Days	G2W:60 W2M:180	G2W:1024 W2M:4096	CRITICAL	true		true	true	true

Effect : For this rule, only Full database backup type will be monitored (Backup Type set to : “Full”).

A WARNING alarm is sent if elapsed time time from last Full database backup is 1 day (see Unit field) or more, a MAJOR alarm if 3 days (see Unit field) or more.

A WARNING alarm is sent if backup duration time was 60 seconds or more, a MAJOR alarm if 180 seconds or more. A WARNING alarm is sent if backup size is 1024 MB or more, a MAJOR alarm if 4096 MB or more.

A CRITICAL alarm will be sent if the database has been detected as damaged.

Generated metrics

metricId	metricUnit	metricTarget	metricDescription
MSSQL_DATABASE_ELAPSED_TIME_SINCE_LAST_BACKUP_DAYS	Days	[Backup Type]	The retrieved elapsed time since the last backup of a given type, expressed in Days
MSSQL_DATABASE_ELAPSED_TIME_SINCE_LAST_BACKUP_HOURS	Hours	[Backup Type]	The retrieved elapsed time since the last backup of a given type, expressed in Hours
MSSQL_DATABASE_ELAPSED_TIME_SINCE_LAST_BACKUP_MINUTES	Minutes	[Backup Type]	The retrieved elapsed time since the last backup of a given type, expressed in Minutes
MSSQL_DATABASE_BACKUP_DURATION_TIME	seconds	[Backup Type]	Fetched duration time of the last backup of a given type, expressed in Seconds
MSSQL_DATABASE_BACKUP_SIZE	MB	[Backup Type]	Fetched size of the last backup of a given type, expressed in MB