info@techdevops.com | 437-991-3573 | Data Engineering Services
TechDevOps.com
Resources Tools
Experts in Microsoft SQL Server on Windows, Linux, Containers | Clusters, Always On, FCI | Migrations, Cloud, Performance



SQL Always On Monitor
by BF (Principal Consultant; Architecture; Engineering)
2018-01-09








SQL Always On Monitor - Version 1:

Captures, Logs, Alerts & Reports for Microsoft SQL Server Always On Availability Groups. Tested on SQL Server 2016 Environment.

The below T-SQL script will output the Always On Metrics per each Availability group. 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 & Alerts & emails a HTML generated report. The report has row-level markers that differentiate Warnings & Alerts for easy display to the user indicating the items needing attention. We also include a permanent table for logging of all of the metrics.


T-SQL Solution:


Create Logging Table:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[LOG_ALWAYSON](
[LogID] [int] IDENTITY(1,1) NOT NULL,
[AlwaysON Node] [varchar](50) NULL,
[Availability Group] [varchar](50) NULL,
[Database] [varchar](50) NULL,
[Synchronization State] [varchar](50) NULL,
[Synchronization Health] [varchar](50) NULL,
[RPO] [int] NULL,
[PRI_LogSendQueueSize_RecordsUnsent_KB] [int] NULL,
[PRI_LogSendRate_KB_sec] [int] NULL,
[SEC_RedoQueueSize_RecordsNotDone_KB] [int] NULL,
[SEC_RedoRate_KB_sec] [int] NULL,
[IsPrimary] [char](1) NULL,
[InsertedDate] [datetime] NULL,
[PeriodEnding] [datetime] NULL
) ON [PRIMARY]

GO



Create Stored Procedure:


USE [OPS]
GO
/****** Object: StoredProcedure [dbo].[Admin_AlwaysON_Monitor] Script Date: 1/5/2018 1:53:40 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create Procedure [dbo].[Admin_AlwaysON_Monitor]
@WarningThresholdPercent int = 10,
@WarningThresholdValue int = 10,
@AlertThresholdPercent int = 5,
@AlertThresholdValue int = 5,
@EmailReport int = 1
as

/*
Sample Execution:
Exec dbo.Admin_AlwaysON_Monitor 0, 0, 0, -60000, 1 --WarningValue: < 0; AlertValue <- 10000 -- -10000=10secs
*/

Declare @IsWarnings bit = 0;
Declare @IsAlerts bit = 0;
Declare @tableHTML nvarchar(max);
Declare @PeriodEnding datetime;
Set @PeriodEnding = GetDate();

INSERT INTO dbo.LOG_ALWAYSON
(
[AlwaysON Node], [Availability Group], [Database], [Synchronization State], [Synchronization Health], [RPO],
[PRI_LogSendQueueSize_RecordsUnsent_KB], [PRI_LogSendRate_KB_sec], [SEC_RedoQueueSize_RecordsNotDone_KB], [SEC_RedoRate_KB_sec],
[IsPrimary], [InsertedDate], [PeriodEnding]
)

SELECT
ar.replica_server_name as 'AlwaysON Node',
ag.name as 'Availability Group',
adc.database_name as 'Database',
--ISNULL(drs.database_state_desc,'') as 'DatabaseState',
drs.synchronization_state_desc 'Synchronization State',
drs.synchronization_health_desc as 'Synchronization Health',
ISNULL(DATEDIFF(MS,LAG(drs.last_commit_time) OVER(PARTITION BY drs.database_id ORDER BY drs.database_id DESC, is_primary_replica DESC),drs.last_commit_time),'') AS 'RPO(ms)',
ISNULL(drs.log_send_queue_size,'') as 'PRI-LogSendQueueSize(RecordsUnsent(KB))', --Amount of log records of the primary database that has not been sent to the secondary databases, in kilobytes (KB).
ISNULL(drs.log_send_rate,'') as 'PRI-LogSendRate(KB/s)', --Average rate at which primary replica instance sent data during last active period, in kilobytes (KB)/second.
ISNULL(drs.redo_queue_size,'') as 'SEC-RedoQueueSize(RecordsNotDone(KB))', --Amount of log records in the log files of the secondary replica that has not yet been redone, in kilobytes (KB)
ISNULL(drs.redo_rate,'') as 'SEC-RedoRate(KB/s)', --Rate at which the log records are being redone on a given secondary database, in kilobytes (KB)/second.
CASE drs.is_primary_replica WHEN '1' Then 'Y' Else 'N' End as 'IsPrimary',
GetDate(),
@PeriodEnding
/*drs.is_local,
drs.is_commit_participant,
drs.recovery_lsn,
drs.truncation_lsn,
drs.last_sent_lsn,
drs.last_sent_time,
drs.last_received_lsn,
drs.last_received_time,
drs.last_hardened_lsn,
drs.last_hardened_time,
drs.last_redone_lsn,
drs.last_redone_time,
drs.filestream_send_rate,
drs.end_of_log_lsn,
drs.last_commit_lsn,
drs.last_commit_time*/
FROM
sys.availability_replicas AS ar with (nolock)
INNER JOIN sys.dm_hadr_database_replica_states AS drs with (nolock) ON drs.group_id = ar.group_id AND drs.replica_id = ar.replica_id
INNER JOIN sys.availability_databases_cluster AS adc with (nolock) ON drs.group_id = adc.group_id AND drs.group_database_id = adc.group_database_id
INNER JOIN sys.availability_groups AS ag with (nolock) ON ag.group_id = drs.group_id
ORDER BY
ag.name, ar.replica_server_name, adc.database_name;


--Begin Warnings:
IF EXISTS(
Select 1 from [dbo].[LOG_ALWAYSON] where PeriodEnding = @PeriodEnding and ([RPO] > @AlertThresholdValue and [RPO] < @WarningThresholdValue)
)
Begin

Set @IsWarnings = 1

Select 'Warning[!] RPO [Btw:' + Convert(varchar(25),@AlertThresholdValue) + ' and ' + Convert(varchar(25),@WarningThresholdValue) + ']' as 'Message',
[RPO], [Availability Group], [Database] from [dbo].[LOG_ALWAYSON] where PeriodEnding = @PeriodEnding
and ([RPO] > @AlertThresholdValue and [RPO] < @WarningThresholdValue) order by [RPO] asc
End
Else
Begin
Select 'No Warnings' as 'Message Warnings:'
End


--Begin Alerts:
IF EXISTS(
Select 1 from [dbo].[LOG_ALWAYSON] where PeriodEnding = @PeriodEnding and ([RPO] < @AlertThresholdValue)
)
Begin

Set @IsAlerts = 1

Select 'Alerts[!] RPO [> ' + Convert(varchar(25),@AlertThresholdValue) + ']' as 'Message',
[RPO], [Availability Group], [Database] from [dbo].[LOG_ALWAYSON] where PeriodEnding = @PeriodEnding
and ([RPO] < @AlertThresholdValue) order by [RPO] asc
End
Else
Begin
Select 'No Alerts' as 'Message Alerts:'
End

Declare @dayNumber int
Set @dayNumber = DATEPART(DW, GETDATE())
Declare @isMonday int = 0

IF(@dayNumber = 2) --Monday
Begin
Set @isMonday = 1
End


--Begin Notifications:
If (@IsWarnings = 1 or @IsAlerts = 1 or @isMonday = 1) and @EmailReport = 1
Begin

SET @tableHTML =
N'<H4><Font Color=black><font face="Arial, Verdana, sans-serif;"><u>REPORT TITLE:</u> ALWAYS-ON HIGH AVAILABILITY MONITOR - WARNINGS & ALERTS</H4>' +
N'<H4><Font Color=black><font face="Arial, Verdana, sans-serif;">' + @@SERVERNAME + '</H4></font>' +
N'<H4><Font Color=black><font face="Arial, Verdana, sans-serif;">' + convert(nvarchar(25),@PeriodEnding) + '</H4></font>' +
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;">AlwaysON Node</th><th align="left" bgcolor="black" style="color:white;">AlwaysON AG</th>
<th align="left" bgcolor="black" style="color:white;">AlwaysON DB</th> <th align="left" bgcolor="black" style="color:white;">Synch State</th>
<th align="left" bgcolor="black" style="color:white;">Synch Health</th><th align="left" bgcolor="black" style="color:white;">RPO(ms)</th>
<th align="left" bgcolor="black" style="color:white;">PRI Log SendQueue(Records KB)</th><th align="left" bgcolor="black" style="color:white;">PRI Log SendRate(KB/sec)</th>
<th align="left" bgcolor="black" style="color:white;">SEC RedoQueue(Records KB)</th><th align="left" bgcolor="black" style="color:white;">SEC RedoRate(KB/sec)</th>
<th align="left" bgcolor="black" style="color:white;">IsPrimary</th><th align="left" bgcolor="black" style="color:white;">Run Date</th>
</tr>' +
CAST ((

Select
Case
When [RPO] > @AlertThresholdValue and [RPO] < @WarningThresholdValue then '#ffff33' --yellow
When [RPO] < @AlertThresholdValue then '#ff3333'
Else '#d9d9d9'
end AS [@bgcolor],
td = [AlwaysON Node], '',
td = [Availability Group], '',
td = [Database], '',
td = [Synchronization State], '',
td = [Synchronization Health], '',
td = [RPO], '',
td = [PRI_LogSendQueueSize_RecordsUnsent_KB], '',
td = [PRI_LogSendRate_KB_sec], '',
td = [SEC_RedoQueueSize_RecordsNotDone_KB], '',
td = [SEC_RedoRate_KB_sec], '',
td = [IsPrimary], '',
td = [InsertedDate], ''--,
--td = [PeriodEnding], ''
From
(
Select
[AlwaysON Node], [Availability Group], [Database], [Synchronization State], [Synchronization Health], [RPO],
[PRI_LogSendQueueSize_RecordsUnsent_KB], [PRI_LogSendRate_KB_sec], [SEC_RedoQueueSize_RecordsNotDone_KB], [SEC_RedoRate_KB_sec],
[IsPrimary], [InsertedDate], [PeriodEnding]
from [dbo].[LOG_ALWAYSON]
where PeriodEnding = @PeriodEnding
) t

FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ;

Set @tableHTML = '</br><font size=2>' + @tableHTML + '</br></br>' + '- Report Warning Run Values: RPO: Between ' + Convert(varchar(10),@AlertThresholdValue) + ' - ' + Convert(varchar(5),@WarningThresholdValue)

Set @tableHTML = @tableHTML + '</br></br>' + '- Report Alert Run Values: RPO: Less Than ' + Convert(varchar(10),@AlertThresholdValue)

Set @tableHTML = @tableHTML + '</br></br>' + '<font size=2>- Source: Admin_AlwaysON_Monitor'

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 dbo.config where configid = 1)
Declare @Recipients as varchar(100)
Set @Recipients = (Select value from dbo.config where configid = 2)

EXEC msdb.dbo.sp_send_dbmail
@profile_name = @ProfileName,
@recipients= @Recipients,
@subject = 'MSSQL - Always-On High Availability Monitor - SRV-NYC-01',
@body = @tableHTML,
@body_format = 'HTML'
End

End