info@techdevops.com
|
437-991-3573
|
Data Engineering Services
|
TechDevOps.com
|
| | | |
|
Experts in Microsoft SQL Server on Windows, Linux, Containers | Clusters, Always On, FCI | Migrations, Cloud, Performance
|
| | | |
|
SQL Replication Monitor
by BF (Principal Consultant; Architecture; Engineering)
2017-09-09
SQL Replication Monitor - Version 1:
Captures, Logs, Alerts & Reports for Microsoft SQL Server Transaction Replication Distribution Metrics.
The below T-SQL script will output the Transactional Replication Distribution Metrics per Publication per Distribution Agents. We recommend you add this Stored Procedure in a SQL Agent Job at run on at least a 15 min basis - if you have a busy database system. This object has parameters and also added the ability for Warnings and also emails a HTML generated report. The report has row-level markers that differentiate each type Replication State for easy display to the user indicating the items needing attention. We also include a permanent table for logging of all of the metrics. If there is a Warning Threshold Passed - Log and Notify. If there is no Warning Threshold Passed - Log only.
T-SQL Solution:
Create Logging Table:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[LOG_REPLICATION_DISTRIBUTION_MONITOR](
[LogID] [int] IDENTITY(1,1) NOT NULL,
[State] [int] NULL,
[Publication] [varchar](150) NULL,
[Distribution System] [varchar](250) NULL,
[Agent_ID] [int] NULL,
[Time] [datetime] NULL,
[SinceLastStats_ElapsedTime] [varchar](10) NULL,
[SinceLastStats_Work] [varchar](10) NULL,
[SinceLastStats_Cmds] [varchar](10) NULL,
[SinceLastStats_CmdsPerSec] [varchar](10) NULL,
[SinceLastStats_Reader_Fetch] [varchar](10) NULL,
[SinceLastStats_Reader_Wait] [varchar](10) NULL,
[SinceLastStats_Writer_Write] [varchar](10) NULL,
[SinceLastStats_Write_Wait] [varchar](10) NULL,
[InsertedDate] [datetime] NULL,
[PeriodEnding] [datetime] NULL,
CONSTRAINT [PK_LOG_REPLICATION_DISTRIBUTION_MONITOR] PRIMARY KEY CLUSTERED
(
[LogID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
Create Stored Procedure:
Create Procedure [dbo].[Admin_Server_Replication_Monitor_Distribution]
@PublicationName varchar(150),
@WarningThresholdPercent decimal(18,0) = 0, --Future
@WarningThresholdValue decimal(18,0) = 0, --Future
@AlertThresholdPercent decimal(18,0) = 0, --Future
@AlertThresholdValue decimal(18,0) = 0, --Future
@AlertStateChange int = 1,
@EmailReport int = 1,
@Debug int = 0
as
Set Nocount ON
/*
Reference: www.TechDevOps.com
*/
/*
Debug:
Exec dbo.Admin_Server_Replication_Monitor_Distribution 'EnterPublicationName',0,0,0,0,1,1,1
Email if Warning:
Exec dbo.Admin_Server_Replication_Monitor_Distribution 'EnterPublicationName',0,0,0,0,1,1,0
*/
Declare @PeriodEnding datetime;
Declare @IsWarnings bit = 0;
Declare @IsAlerts bit = 0;
Declare @tableHTML nvarchar(max);
Declare @TotalDistroAgents int = 0
Set @PeriodEnding = GetDate();
Declare @agent_id int
Declare @timeindays int = 1
Create Table #TMP ([State] int, [Distribution System] varchar(250), Agent_ID int, [Time] datetime, SinceLastStats_ElapsedTime varchar(10), SinceLastStats_Work varchar(10), SinceLastStats_Cmds varchar(10), SinceLastStats_CmdsPerSec varchar(10),
SinceLastStats_Reader_Fetch varchar(10), SinceLastStats_Reader_Wait varchar(10), SinceLastStats_Writer_Write varchar(10), SinceLastStats_Write_Wait varchar(10))
Create Table #AGENTS (AgentID int)
Insert into #AGENTS(AgentID) Select ID from Distribution.dbo.MSdistribution_agents where Publication = @PublicationName order by ID ASC;
Set @TotalDistroAgents = (Select count(*) from #AGENTS)
While Exists (Select 1 from #AGENTS)
Begin
Set @Agent_id = (Select top 1 AgentID from #AGENTS order by AgentID ASC);
;with cte_stats as(
select h.agent_id, ag.name, h.time, cast(h.comments as xml) comments
from Distribution.dbo.MSdistribution_history h with (nolock)
inner join Distribution.dbo.MSdistribution_agents ag with (nolock) on h.agent_id = ag.id
where
(h.[comments] like '<stats state="1"%' or h.[comments] like '<stats state="2"%')
and h.agent_id = @Agent_id
and h.time > getdate() - @timeindays
)
Insert into #TMP
select
c.value('(/stats/@state)[1]', 'bigint') AS [State]
,cte_stats.Name as 'Distribution System'
,cte_stats.Agent_ID
,cte_stats.[Time]
--,c.value('(/stats/@work)[1]', 'bigint') AS [work]
--,c.value('(/stats/@idle)[1]', 'bigint') AS [idle]
--,c.value('(/stats/reader/@fetch)[1]', 'bigint') AS [reader_fetch]
--,c.value('(/stats/reader/@wait)[1]', 'bigint') AS [reader_wait]
--,c.value('(/stats/writer/@write)[1]', 'bigint') AS [writer_write]
--,c.value('(/stats/writer/@wait)[1]', 'bigint') AS [writer_wait]
,c.value('(/stats/sincelaststats/@elapsedtime)[1]', 'bigint') AS [sincelaststats_elapsedtime]
,c.value('(/stats/sincelaststats/@work)[1]', 'bigint') AS [sincelaststats_work]
,c.value('(/stats/sincelaststats/@cmds)[1]', 'bigint') AS [sincelaststats_cmds]
,c.value('(/stats/sincelaststats/@cmdspersec)[1]', 'float') AS [sincelaststats_cmdspersec]
,c.value('(/stats/sincelaststats/reader/@fetch)[1]', 'bigint') AS [sincelaststats_reader_fetch]
,c.value('(/stats/sincelaststats/reader/@wait)[1]', 'bigint') AS [sincelaststats_reader_wait]
,c.value('(/stats/sincelaststats/writer/@write)[1]', 'bigint') AS [sincelaststats_writer_write]
,c.value('(/stats/sincelaststats/writer/@wait)[1]', 'bigint') AS [sincelaststats_writer_wait]
--,cte_stats.[comments]
from cte_stats
cross apply cte_stats.comments.nodes('/stats[@state="1"]') b(c)
union all
select
c.value('(/stats/@state)[1]', 'int') AS [State]
,cte_stats.Name as 'Distribution System'
,cte_stats.Agent_ID
,cte_stats.[Time]
--,c.value('(/stats/@work)[1]', 'bigint') AS [work]
--,c.value('(/stats/@idle)[1]', 'bigint') AS [idle]
--,c.value('(/stats/reader/@fetch)[1]', 'bigint') AS [reader_fetch]
--,c.value('(/stats/reader/@wait)[1]', 'bigint') AS [reader_wait]
--,c.value('(/stats/writer/@write)[1]', 'bigint') AS [writer_write]
--,c.value('(/stats/writer/@wait)[1]', 'bigint') AS [writer_wait]
,c.value('(/stats/sincelaststats/@elapsedtime)[1]', 'bigint') AS [sincelaststats_elapsedtime]
,c.value('(/stats/sincelaststats/@work)[1]', 'bigint') AS [sincelaststats_work]
,c.value('(/stats/sincelaststats/@cmds)[1]', 'bigint') AS [sincelaststats_cmds]
,c.value('(/stats/sincelaststats/@cmdspersec)[1]', 'float') AS [sincelaststats_cmdspersec]
,c.value('(/stats/sincelaststats/reader/@fetch)[1]', 'bigint') AS [sincelaststats_reader_fetch]
,c.value('(/stats/sincelaststats/reader/@wait)[1]', 'bigint') AS [sincelaststats_reader_wait]
,c.value('(/stats/sincelaststats/writer/@write)[1]', 'bigint') AS [sincelaststats_writer_write]
,c.value('(/stats/sincelaststats/writer/@wait)[1]', 'bigint') AS [sincelaststats_writer_wait]
--,cte_stats.[comments]
from cte_stats
cross apply cte_stats.comments.nodes('/stats[@state="2"]') b(c)
order by cte_stats.agent_id, cte_stats.name asc, cte_stats.time desc;
Insert into [dbo].[LOG_REPLICATION_DISTRIBUTION_MONITOR]
([State],[Publication],[Distribution System],[Agent_ID],[Time],[SinceLastStats_ElapsedTime],[SinceLastStats_Work],
[SinceLastStats_Cmds],[SinceLastStats_CmdsPerSec],[SinceLastStats_Reader_Fetch],[SinceLastStats_Reader_Wait],
[SinceLastStats_Writer_Write],[SinceLastStats_Write_Wait],[InsertedDate],[PeriodEnding])
Select top 5 --############ Get Lastest 15 minutes of Distribution Stats Logs ############
[State],@PublicationName,[Distribution System],[Agent_ID],[Time],[SinceLastStats_ElapsedTime],ISNULL(SinceLastStats_Work,'-'),
ISNULL(SinceLastStats_Cmds,'-'),ISNULL(SinceLastStats_CmdsPerSec,'-'),ISNULL(SinceLastStats_Reader_Fetch,'-'),ISNULL(SinceLastStats_Reader_Wait,'-'),
ISNULL(SinceLastStats_Writer_Write,'-'),ISNULL(SinceLastStats_Write_Wait,'-'), GetDate(), @PeriodEnding
from #TMP order by Agent_ID ASC, [Distribution System] ASC, [Time] DESC
Delete from #AGENTS where AgentID = @agent_id
Set @Agent_id = ''
Truncate Table #TMP
End
--Begin Warnings:
--State Change:
IF @AlertStateChange = 1
Begin
IF EXISTS(Select 1 from [dbo].[LOG_REPLICATION_DISTRIBUTION_MONITOR] where PeriodEnding = @PeriodEnding and [State] = 2)
Begin
Set @IsWarnings = 1
Select 'Warning[!] Replication Distribution: State = ' + Convert(varchar(5),[State]) + ' on ' + [Distribution System] as 'Message' from [dbo].[LOG_REPLICATION_DISTRIBUTION_MONITOR] where PeriodEnding = @PeriodEnding and [State] = 2
End
Else
Begin
Select 'No Warnings' as 'Message Warnings:'
End
End
--Begin Notifications:
If (@IsWarnings = 1 and @EmailReport = 1) or @Debug = 1
Begin
SET @tableHTML =
N'<br/><H4><Font Color=black><font face="Arial, Verdana, sans-serif;"><u>REPORT TITLE:</u> REPLICATION DISTRIBUTION MONITOR - WARNINGS & ALERTS</H4>' +
N'<H4><Font Color=black><font face="Arial, Verdana, sans-serif;">' + 'Source: ' + @@SERVERNAME + + '; Publication: ' +
@PublicationName + '; Total Distribution Agents:' + convert(nvarchar(25),@TotalDistroAgents) + '</H4></font>' +
N'<H4><Font Color=black><font face="Arial, Verdana, sans-serif;">' + 'Report Runtime: ' + convert(nvarchar(25),@PeriodEnding) + '</H4></font><br/>' +
N'<table border="1" style=”font-family:arial;color:black;font-size:11pt;”> ' +
N'<tr align="left">
<th align="left" bgcolor="black" style="color:white;">State</th>
<th align="left" bgcolor="black" style="color:white;">Distribution System (Publisher-PublisherDB-Publication-Subscriber)</th>
<th align="left" bgcolor="black" style="color:white;">Occurrence</th>
<th align="left" bgcolor="black" style="color:white;">SinceLastStats(s)</th>
<th align="left" bgcolor="black" style="color:white;">Agent Working(s)</th>
<th align="left" bgcolor="black" style="color:white;">Commands</th>
<th align="left" bgcolor="black" style="color:white;">CommandsPerSec</th>
<th align="left" bgcolor="black" style="color:white;">Reader Fetch(s)</th>
<th align="left" bgcolor="black" style="color:white;">Reader Wait(s)</th>
<th align="left" bgcolor="black" style="color:white;">Writer Write(s)</th>
<th align="left" bgcolor="black" style="color:white;">Writer Wait(s)</th>
</tr>' +
CAST ((
Select
Case
When [State] = 1 then '#d9d9d9' Else '#ffff33' end AS [@bgcolor],
td = [State], '',
td = [Distribution System], '',
td = CONVERT(VARCHAR, [Time], 20), '',
td = [SinceLastStats_ElapsedTime], '',
td = [SinceLastStats_Work], '',
td = [SinceLastStats_Cmds], '',
td = [SinceLastStats_CmdsPerSec], '',
td = [SinceLastStats_Reader_Fetch], '',
td = [SinceLastStats_Reader_Wait], '',
td = [SinceLastStats_Writer_Write], '',
td = [SinceLastStats_Reader_Wait], ''
From
(
Select
*
from [dbo].[LOG_REPLICATION_DISTRIBUTION_MONITOR]
where PeriodEnding = @PeriodEnding
) t
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ;
Set @tableHTML = @tableHTML + '</br></br>' + '<font size=2>- Data is the latest Distribution Stats logged per Distribution Agent'
Set @tableHTML = @tableHTML + '</br></br>' + '<font size=2>- Interval: 15mins; If Warnings or Alerts - Log + Email; If No Warnings or Alerts - Log Only'
Set @tableHTML = @tableHTML + '</br></br>' + '<font size=2>- Source: Admin_Server_Replication_Monitor_Distribution'
Set @tableHTML = @tableHTML + '</br></br>' + '- Delivered by DBMail on SRV-NYC-01' +
+
'</font>'
If @tableHTML <> '' and @tableHTML IS NOT NULL
Begin
Declare @ProfileName as varchar(100)
Set @ProfileName = (Select value from ops.dbo.config where configid = 1)
Declare @Recipients as varchar(100)
Set @Recipients = (Select value from ops.dbo.config where configid = 2)
EXEC msdb.dbo.sp_send_dbmail
@profile_name = @ProfileName,
@recipients= 'ops@xyz.ca',
@subject = 'MSSQL - Replication Distribution Monitor - Warnings & Alerts - SRV-NYC-01',
@body = @tableHTML,
@body_format = 'HTML' ;
End
End
--Select * from [dbo].[LOG_REPLICATION_DISTRIBUTION_MONITOR] Order By Agent_ID ASC, [Distribution System] ASC, [Time] ASC
Drop Table #TMP
Drop Table #AGENTS
|
|
|
|
|
|
|
|
|
|