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.
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.
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:
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.
- Add a name (Track missing full database backups/Track missing database checkdbs)
- Click on +
- Select Add Numeric Comparison
- Select Repository Query
- Add the query
- Select Any
- Select Is Greater than
- Select Explicit Value
- Type 7 (or whatever threshold you want to set).
- Save the condition
Alternatively, you can just download the two files from the following github public repository and import them:
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.
This blog post is partially based on the following resources: