I wanted to get notification only when the status wasn't normal for 3 consecutive times. It seems like DPA doesn't support by default. Correct me, if I'm wrong.
This is consist of two Custom SQL Alert - Multiple Numeric Return alerts.
1. Replication UndistributedCommands
Originally I wanted to use Custom Metrics but it only support only 1 row and 1 column. So I ended up using custom alert.
2. Replication not normal for 3 consecutive times
I query collected history of #1(Replication UndistributedCommands) alert and check if all of the last three was not normal and send alert.
1. Replication UndistributedCommands
ALERT DEFINITION
To create the alert, click on Alerts > Manage Alerts tab and create a Custom Alert of type Custom SQL Alert - Multiple Numeric Return. Configure the alert with values similar to these:
Alert Name: Replication UndistributedCommands
Execution Interval: 5 Minutes - change this based on your need
Notification Text:
I got initial script from this link(http://www.brentozar.com/archive/2014/07/monitoring-sql-server-transactional-replication/) and modified
SQL Statement:
declare @tabDistStatus table(
agent_id int
, UndelivCmdsInDistDB bigint)
-- Refresh replication monitor data
Exec [distribution].sys.sp_replmonitorrefreshjob @iterations = 1;
insert into @tabDistStatus
SELECT ds.agent_id, sum(ds.UndelivCmdsInDistDB) UndelivCmdsInDistDB
FROM distribution.dbo.MSdistribution_status ds WITH(NOLOCK) -- article level
GROUP BY ds.agent_id
SELECT md.agent_name, isnull(ds.UndelivCmdsInDistDB,0) UndelivCmdsInDistDB
FROM distribution.dbo.MSreplication_monitordata md WITH(NOLOCK)
Left Join @tabDistStatus ds
ON md.agent_id = ds.agent_id
Inner Join distribution.dbo.MSdistribution_agents ag with(nolock)
On ag.id = md.agent_id
Execute Against: Monitored Database -- This query should be run on Distributor
Units: Count
High Threshold: You should collect undistributed command count for good period of time to determine right value for your environment and choose correct value
2. Replication not Normal for 3 consecutive times
ALERT DEFINITION
To create the alert, click on Alerts > Manage Alerts tab and create a Custom Alert of type Custom SQL Alert - Multiple Numeric Return. Configure the alert with values similar to these:
Alert Name: Replication not Normal for 3 consecutive times
Execution Interval: 10 Minutes - change this based on your need
Notification Text: Your message
SQL Statement:
Declare @ConcecutiveCount int=3
select ahr.PARAMETERNAME
, count(case when ahr.LEVELNAME<>'NORMAL' then 1 end) AboveCount
from (
SELECT top (@ConcecutiveCount)
a.[ALERTNAME], a.[FREQUENCY]
, ah.HISTORYID, ah.ACTIONDATE, ah.LEVELNAME
FROM [ignite].[CON_ALERT] a
JOIN [ignite].[CON_ALERT_HISTORY] ah
ON a.ID = ah.ALERTID
where [ALERTNAME]='Replication UndistributedCommands' -- Should be the same as the name of #1 alert
@YOURDBNAME'
-- change this
order by a.[ID], ah.ACTIONDATE desc
) ah
JOIN [ignite].[CON_ALERT_HISTORY_RESULTS] ahr
ON ah.HISTORYID = ahr.HISTORYID
group by ahr.PARAMETERNAME
Execute Against: Repository
Units: Count
High Threshold:
High : Min 3, Max Empty
