How to monitor database backups and integrity with SentryOne SQL Sentry custom advisory conditions

Introduction

This blog post discusses how to leverage SentryOne’s SQL Sentry product to monitor database backups and integrity checks.

At SSG, we are big fans of SentryOne and have been a partner for many years. If you are not familiar with the product, it is a Performance Monitoring solution for SQL Server, as well as Analysis Services, Azure SQL Database, SQL Server on Amazon RDS and more. 

That being said, this blog post is not about why SentryOne is a great product, but rather provides a solution to a problem using custom advisory conditions. 

Problem

One of the basic responsibilities of any DBA is making sure that backups and database integrity checks are in-place. While we can monitor jobs that run these for failures, a more sure way is to get the SQL Server metadata about the last backup and last checkdb. In some cases, backups are running through third party software or from a centralized system, so a job would not even exist. And what we really want to know is “when was the last successful backup.” The same is true for database integrity checks.

Without SentryOne, we have a couple of options:

• Create a job using the dbatools PowerShell module (which we are also big fans of) and use the cmdlets Get-DbaDbBackupHistory  and Get-DbaLastGoodCheckDb for integrity checks. 
• Query msdb.dbo.backupset table (taking latest backup_finish_date column) for backup history and DATABASEPROPERTYEX (‘dbname’, ‘LastGoodCheckDbTime’) for integrity checks.  

Needless to say, those two options are both a bit cumbersome. Additionally, we would need to store this data, so we can see history.

Solution

While SentryOne comes with more than 85 built-in advisory conditions, it does not include these two items. The good news is, it’s super easy to add your own conditions.

Advisory Conditions allow us to create conditions using queries against the SentryOne repository as well as against other database(s) and several other options. Since SentryOne is already collecting the information we need for these two conditions, we can just query the repository database. 

The following query will be used to pull the required data from the repository:

Source code

SELECT esc.objectname + '.' + db.name AS server_database
, DATEDIFF(DAY,MAX(LastDbccCheckDbTime),GETDATE()) AS LastDbccCheckinDays
, DATEDIFF(DAY,MAX(LastBackupTime), GETDATE()) AS LastBackupinDays
FROM
    Device
    JOIN EventSourceConnection esc ON
        esc.DeviceID = Device.ID
    JOIN PerformanceAnalysisSqlDatabase db ON
        db.EventSourceConnectionID = esc.ID
    JOIN PerformanceAnalysisSqlFile f ON
        f.EventSourceConnectionID = esc.ID
WHERE 1=1
AND f.lastbackuptype = 'D'
and db.name NOT IN ('tempdb','distribution')
GROUP BY esc.objectname
, db.name

The following steps outline how to add this query as a custom condition across all monitored servers:

From the  Navigator, expand the Advisory Conditions node and open the Condition List, then click on Create Advisory Condition. 

1. Add a name (Track missing full database backups/Track missing database checkdbs)
2. Click on +
3. Select Add Numeric Comparison
4. Select Repository Query
5. Add the query
6. Select Any
7. Select Is Greater than
8. Select Explicit Value
9. Type 7 (or whatever threshold you want to set).
10. Save the condition

Alternatively, you can just download the two files from the following github public repository and import them: 

Custom Conditions

Your condition list should get updated once you download and import the conditions. At this point you can configure whatever actions you want as  you would for any other condition in SentryOne. These steps should make it easy to monitor database backups using SentryOne.

Disclaimer:

This blog post is partially based on the following resources:

https://www.sentryone.com/blog/query-database-growth-over-time-from-your-sentryone-sql-sentry-database

https://www.mssqltips.com/sqlservertip/1601/script-to-retrieve-sql-server-database-backup-history-and-no-backups/

https://thomaslarock.com/2018/05/using-databasepropertyex-to-find-last-good-dbcc-checkdb-time/.