SolarWinds SQL Sentry is the leading monitoring and alerting platform for Microsoft Data Platform DBAs, and it is our platform of choice for our managed service customers. It uses a Windows service to monitor its targets. That's all great, but... What if this monitoring service is unavailable for some reason? How could you be alerted when your alerting service is down? Who is watching the watchers? Who is monitoring the monitors?
As a SQL Sentry MSP, we found a solution for this a long time ago. Join me in this blog post to learn how to do it yourself!
The Heartbeat
SQL Sentry has a built-in "heartbeat" mechanism, supposedly made exactly for this purpose. Every time a monitoring service connects to the SQL Sentry repository, it also updates a table row representing itself, with the current UTC time, to save its latest "heartbeat".
There is even a "Failsafe Condition" called "Monitoring Service: Send Heartbeat Error". But that is actually helpful only when the Monitoring Service itself is still up! After all, the monitoring service itself is the only entity that actually triggers alerts in SQL Sentry!
However, this "monitoring service heartbeat" is still something that could be of great use for us, as it is nothing more than a database table field.
This data is saved in the SQL Sentry table "ManagementEngine", in the column called "HeartbeatDateTime":
Checking the Pulse
Checking the last heartbeat for each monitoring service would be as easy as querying from a table. Although, it's often not as easy as that, as we also need to take into consideration when was the last time that an alert was already dispatched and whether we need to re-alert again or not.
Luckily for you, I have already prepared a T-SQL script for you that implements (almost) all the necessary logic for you:
-- Run this in the right SQL Sentry database
--USE [SentryOne];
--USE [SQLSentry];
GO
IF OBJECT_ID('[dbo].[heartbeat_log]') IS NULL
BEGIN
CREATE TABLE [dbo].[heartbeat_log](
[servername] [nvarchar](300) CONSTRAINT PK_Heartbeat_Log PRIMARY KEY CLUSTERED WITH(IGNORE_DUP_KEY=ON,DATA_COMPRESSION=PAGE),
[heartbeatdate] [datetime] NULL,
[ActualHeartbeatDate] [datetime] NULL
);
END
GO
DECLARE
@HeartbeatThresholdMinutes INT = 5,
@HeartbeatRepeatAlertThresholdHours INT = 1,
@GlobalObjID UNIQUEIDENTIFIER
SELECT @GlobalObjID = ObjectID FROM dbo.vwObjects_Global;
DECLARE @SubjectTitle NVARCHAR(255), @BodyText NVARCHAR(4000);
SET @SubjectTitle = N'Monitoring Service: Heartbeat Failure'
-- Delete any irrelevant heartbeat failures from log
DELETE HL
-- optionally use this output to implement an "auto-resolve" logic:
-- OUTPUT deleted.servername INTO @RestoredHeartbeats
FROM dbo.heartbeat_log AS HL
INNER JOIN dbo.ManagementEngine AS E
ON HL.servername = E.servername
WHERE E.HeartbeatDateTime > HL.HeartbeatDate
-- Get all monitoring servers with outdated heartbeats and are not snoozed
DECLARE @site NVARCHAR(200), @servername NVARCHAR(200), @HeartbeatDateTime datetime
, @text NVARCHAR(200), @title NVARCHAR(200), @HeartbeatDateTimeLocal datetime;
DECLARE serverlist CURSOR
LOCAL FAST_FORWARD
FOR
SELECT T.[Name],E.[servername], ISNULL(E.HeartbeatDateTime, E.LastInitializationDateTime)
FROM dbo.ManagementEngine E
INNER JOIN [dbo].[Site] T ON T.ID = E.SiteID
WHERE ISNULL(E.HeartbeatDateTime, E.LastInitializationDateTime) < DATEADD(MINUTE, -@HeartbeatThresholdMinutes, GETUTCDATE())
AND NOT EXISTS (SELECT st.ObjectID FROM dbo.SnoozeStatus st WHERE st.ObjectID IN (E.ObjectID,T.ObjectID,@GlobalObjID) )
OPEN serverlist
WHILE 1=1
BEGIN
FETCH NEXT FROM serverlist INTO @site, @servername, @HeartbeatDateTime
IF @@FETCH_STATUS <> 0 BREAK;
SET @HeartbeatDateTimeLocal = CONVERT(datetime, @HeartbeatDateTime AT TIME ZONE 'UTC' AT TIME ZONE 'Israel Standard Time' )
SET @title = @site + ': '+ QUOTENAME(@servername) + ' ' + @SubjectTitle
SET @text = N'Monitor server <b>' + @servername + N'</b> has lost connection to the repository.<br/>Last heartbeat was at <b>' + convert(nvarchar(200),@HeartbeatDateTimeLocal, 121) + N' (IST)</b>'
SET @BodyText = N'[Message]: ' + @text
+ N'<br/><br/>[Start Time (Local)]: ' + CONVERT(nvarchar(25), @HeartbeatDateTimeLocal, 121)
+ N'<br/>[Start Time (UTC)]: ' + CONVERT(nvarchar(25), @HeartbeatDateTime, 121)
+ N'<br/>[Duration]: ' + ISNULL(NULLIF(CONVERT(varchar(100), DATEDIFF(dd,0, GETUTCDATE()-@HeartbeatDateTime)), 0) + ' day(s), ', '')
+ CONVERT(varchar(100), GETUTCDATE()-@HeartbeatDateTime, 114)
+ N'<br/>----------------------------------------------------------------------'
+ N'<br/>[Timestamp (Local)]: ' + CONVERT(nvarchar(25), GETDATE(), 121)
+ N'<br/>[Timestamp (UTC)]: ' + CONVERT(nvarchar(25), GETUTCDATE(), 121)
+ N'<br/>[Generated By]: ' + @@SERVERNAME
+ N'<br/>[Monitor Type]: SQLSentryHeartbeatCheck<br/>[Condition]: ' + @SubjectTitle
;
WITH trgt AS (SELECT * FROM dbo.heartbeat_log WHERE servername = @servername)
MERGE INTO trgt
USING (SELECT * FROM (VALUES(@servername, GETUTCDATE())) AS v(servername, TimeNow)) AS src
ON trgt.servername = src.servername
WHEN NOT MATCHED BY TARGET THEN
INSERT (servername, heartbeatdate)
VALUES (servername, TimeNow)
WHEN MATCHED AND src.TimeNow > dateadd(hour, @HeartbeatRepeatAlertThresholdHours, trgt.heartbeatdate) THEN
UPDATE SET heartbeatdate = src.TimeNow
;
IF @@ROWCOUNT > 0
BEGIN
PRINT N'Heartbeat failure for ' + QUOTENAME(@servername)
-- Get additional metadata about the server if it's also a SQL Server
DECLARE @SQLVersion NVARCHAR(4000), @SQLEdition NVARCHAR(4000)
SELECT
@SQLVersion = CASE
WHEN MajorVersionNumber = 10 AND MinorVersionNumber = 50 THEN '2008R2'
WHEN EngineEdition IN (5,6) THEN 'sql_azure'
ELSE
CASE MajorVersionNumber
WHEN 8 THEN '2000'
WHEN 9 THEN '2005'
WHEN 10 THEN '2008'
WHEN 11 THEN '2012'
WHEN 12 THEN '2014'
WHEN 13 THEN '2016'
WHEN 14 THEN '2017'
WHEN 15 THEN '2019'
WHEN 16 THEN '2022'
ELSE ''
END
END
,@SQLEdition = CASE [EngineEdition] -- https://docs.microsoft.com/en-us/sql/t-sql/functions/serverproperty-transact-sql
WHEN 2 THEN 'standard' -- also web and BI
WHEN 3 THEN 'enterprise' -- also datacenter, developer and evaluation
WHEN 4 THEN 'express'
WHEN 5 THEN 'azure_sql_database'
WHEN 6 THEN 'azure_sql_data_warehouse'
WHEN 8 THEN 'azure_managed_instance'
ELSE ''
END
FROM [dbo].[vwSqlServer]
WHERE [ObjectName] = @servername
/* ---- TODO: Add your alert logic below this line ---- */
/*-- Example 1: Sending an e-mail using DBMail:
EXEC msdb..sp_send_dbmail
@recipients = 'dba@acme.com'
, @subject = @title
, @body = @BodyText
, @body_format = 'HTML'
, @importance = 'high'
*/
/*-- Example 2: Throwing an error to fail the job:
--RAISERROR(N'%s', 16, 1, @BodyText);
*/
/*-- Example 3: Whatever else:
EXECUTE SendSNMPAlertCLR
@SourceServer = @servername,
@Subject = @title,
@Body = @BodyText,
@Severity = 3,
@SQLVersion = @SQLVersion,
@SQLEdition = @SQLEdition,
@Condition = @SubjectTitle,
@Site = @site
*/
/* ---- TODO: Add your alert logic above this line ---- */
END
END
CLOSE serverlist
DEALLOCATE serverlistWhat this script does, essentially, is as follows:
If it doesn't exist already, create a table called "heartbeat_log" which will save any triggered heartbeat alerts.
Delete from the heartbeat_log table any records related to monitoring services that already updated their heartbeat since their alert was triggered.
Declare a cursor to traverse any monitoring services with an outdated heartbeat time. This will NOT include monitoring services inside a snoozed site or when all targets are snoozed.
For each such monitoring service, construct an alert message, and add or update a record in the heartbeat_log table.
Once the alert message is ready, do something with it (such as sending an e-mail, raising an error, or whatever else you want).
The above script is supposed to be executed by a SQL Agent Job that you would create on the same SQL Server instance where you have your SQL Sentry repository database (this database by default is called "SentryOne" or "SQLSentry").
This "Heartbeat Monitoring" job should be scheduled with a frequency of a few minutes apart, as per your requirements.
Modify the script to customize it for your needs however you want (it's only a starting point, after all).
And that's it! You now have not only a monitoring service that monitors your SQL Servers, but also a SQL Server that monitors your monitoring service!
To learn more about SQL Sentry Troubleshooting, you can check out this article at the official SQL Sentry documentation site.