45 private links
Hey Reg,
I am far from an expert on Zabbix but have a couple of suggestions.
One approach would be to use a performance counter in the “SQL ServerBigGrinatabase Mirroring” group as an indicator. You could try the size of the Log Send Queue to see if it grows too high, or maybe the Transaction delay time. Advantage, built-in counter, disadvantage, difficult to correlate with mirror status you want.
Another approach would be to utilize one of the performance counters in the “SQL Server :User Settable” category. You would have to use sp_user_counter to set the value. This could be done with a scheduled job. You could query the mirror/AlwaysOn status and set the value to a flag you would use as the health indicator for Zabbix. For example 0 for mirror/AlwaysOn status OK, 1 or greater for a problem. Advantage, uses ability of Zabbix to leverage the the perf counter infrastructure, disadvantage, relies on SQL job to update value and uses one out of only 10 available user defined counters.
Example Mirroring query:
SELECT count (*)
FROM sys.database_mirroring
WHERE mirroring_role_desc IN ('PRINCIPAL','MIRROR')
AND mirroring_state_desc NOT IN ('SYNCHRONIZED','SYNCHRONIZING');
Example AlwaysOn Query:
-- 0 is good, any number greater than 0 is bad
SELECT COUNT (*) FROM [master].[sys].[dm_hadr_database_replica_states]
WHERE [synchronization_health_desc] != N'HEALTHY' ;
A third approach would be to embed a T-SQL query in the Zabbix agent configuration file that would set a value that would correspond to an Item in Zabbix. Similar to the User Settable approach but uses the Zabbix agent to refresh the value of the Zabbix Item instead of a SQL job to set the built in performance counter. Advantage, leverages Zabbix functionality, does not need separate SQL job, disadvantage, need to embed SQL connection into Zabbix.
https://msdn.microsoft.com/en-us/library/ms187480.aspx
https://www.zabbix.com/forum/showthread.php?t=44517&page=2
https://www.zabbix.com/forum/showthread.php?t=15865
[http://serverfault.com/questions/275517/zabbix-populate-item-with-sql-server-query
http://serverfault.com/questions/512521/zabbix-monitoring-custom-values
http://serverfault.com/questions/128090/how-to-query-custom-performance-counters-using-zabbix-agent