Create some table that gets updated every N seconds that is readable by all. This will allow a heartbeat and potential alerting for DPA central or from other monitoring tools very easy to implement.
I had a similar issue and with some help from support, and some scripting, I've been able to implement a heartbeat alert. There is a table in DPA that gets updated with a new value internally (their own heartbeat). I then extract that value every half hour and store it in a custom table. I then have a custom alerting program I created that runs hourly, and compares the old value to the current value. If they are the same, it means DPA isn't running, but if they are different, then DPA must be working. It's not perfect, but it has alerted me to a problem with DPA within one hour of that problem occurring. I created this, because one time DPA was down for a couple of days before I noticed. Here's the details:
Query run every half hour to store the "old" value of the heartbeat:
UPDATE dbo.C_COA_HEARTBEAT_MONITOR
SET
PI_HEARTBEAT_OLD = b.V,
TIMESTAMP = CAST(GETDATE() AS SMALLDATETIME)
FROM
dbo.CONPRM b
WHERE
b.P = 'PI_HEARTBEAT'
Query run every hour to check if the hearbeat value has changed (aka DPA is alive), or if it's still the same (aka DPA is down):
SELECT
a.V
dbo.CONPRM a
a.P = 'PI_HEARTBEAT' AND
a.V = (
b.PI_HEARTBEAT_OLD
dbo.C_COA_HEARTBEAT_MONITOR b)
My custom alerting program basically will fire off an e-mail if the second query returns any rows.
Hope someone else finds this helpful.
Glen
Glen,
Nice work. The value you mention is the heartbeat. It isn't really very readable by all. If you're interested it is the time in Unix epoch stored as varchar.
Epoch Converter - Unix Timestamp Converter
here is some TSQL to convert it to a readable date
declare @HeartBeat bigint set @HeartBeat = (select convert(bigint,V) from CONPRM where P = 'PI_HEARTBEAT')
select dateadd(ms, @HeartBeat%(3600*24*1000),dateadd(day, @HeartBeat/(3600*24*1000), '1970-01-01 00:00:00.0'))
