Introduction
Just in case you are not familiar with SentryOne SQLSentry product. SQLSentry is SQL Server Performance Monitoring solution from SentryOne. It works with different SQL Server versions, private and public clouds (AWS and Azure), Linux and Windows OS, Hyper-V and VMware hypervisors. That being said, this blog post is not about why SQLSentry is a great product, but rather providing a solution to a problem by extending SQLSentry defaults. This blog post is going to discuss how to leverage an existing SQLSentry product to enforce database backups and integrity checks (aka checkdb).
Problem
One of the basic responsibilities of any DBA is to make sure that both backups and integrity checks are in-place. Without those two, any hardware/software fault can lead to significant loses to any business and this is why there is a need for a constant monitoring.
How do we do it? Without SQLSentry, we would schedule a job to run one of the the following processes:
- Run dbatools cmdlets Get-DbaDbBackupHistory for backup and Get-DbaLastGoodCheckDb for integrity
- Query msdb.dbo.backupset table (taking latest backup_finish_date column) for backup and DATABASEPROPERTYEX (‘dbname’, ‘LastGoodCheckDbTime’) for integrity
Needless to say, those two steps are a bit cumbersome. Additionally, we would need to store this data, so we can have historical data to look back.
Solution
Unfortunately with over 85 built-in advisory conditions, SQLSentry doesn’t come equipped with checkup for backups and integrity conditions. The Good news is, it’s super easy to add your own conditions and have SQLSentry to constantly monitor and alert.
Introducing SQLSentry custom advisory conditions. Custom advisory allow us to effectively create queries that would have SQLSentry periodically scan and collect whatever OS and/or SQL Server performance metrics for later analysis and evaluation.
Just to make sure, checking for database backups and integrity doesn’t require SQLSentry, but having SQLSentry software makes is easier due to an easy to use condition framework (SentryOne Client) and robust data collection engine (a.k.a. SentryOne database).
The following query is going to pull all the required data from the SentryOne repository for both backup and checkdb.
select esc.objectname + '.' + db.name as server_database
, DATEDIFF(DAY,MAX(LastDbccCheckDbTime),GETDATE()) AS LastDbccCheckinDays
, DATEDIFF(DAY,MAX(LastBackupTime), GETDATE()) AS LastBackupinDays
FROM
SentryOne.dbo.Device
JOIN SentryOne.dbo.EventSourceConnection esc ON
esc.DeviceID = Device.ID
JOIN SentryOne.dbo.PerformanceAnalysisSqlDatabase db ON
db.EventSourceConnectionID = esc.ID
JOIN SentryOne.dbo.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.nameThe following steps is describing how to add the above query (essentially 2 queries, one for backup and one for checkdb) as custom conditions across all monitored servers.
- Get to the custom condition screen (see Fig #1)
- Click on Condition list and then open
- Click on Create Advisory Condition
- Add custom condition (see Fig #2)
- Add a name (Track missing full database backups/Track missing database checkdbs)
- Click on +
- Select Add Numeric Comparison
- Select Repository Query (this selection will make sure to run the condition on server with SentryOne database only)
- Add the query
- Select Any
- Select Is Greater than
- Select Explicit Value
- Type 7
Alternatively, you can just download those 2 condition files (from my public GitHub repo in Fig #3) and import those into your existing SQL Sentry setup.
https://github.com/steverezhener/S1-custom-conditions/archive/master.zip
Your condition list (Fig #4) should get updated once your download and import those 2 custom conditions.
Disclaimer:
This blog post is partially based on the following resources:
https://thomaslarock.com/2018/05/using-databasepropertyex-to-find-last-good-dbcc-checkdb-time/.
DataSteve 