February 4, 2015 at 2:49 pm
We currently use Zabbix to monitor our SQL Servers, generally we use Perf_Counters to gather performance information (CPU Usage, Memory, Blocked process etc.), Zabbix also lets us monitor services as to whether they are up or down. I have searched google looking for any information regarding the monitoring of AlwaysOn/Mirroring within Zabbix and to date ave found nothing. What I am looking to alert on is an indication that Mirroring has stopped/been interrupted with say something like "Not Synchronized"...Does anyone have any experience with Zabbix and monitoring SQL Server 2012 and specifically monitoring AlwaysOn...Any help would be appreciated
February 4, 2015 at 2:56 pm
Until you mentioned it I had never heard of Zabbix. That isn't a good sign for you BTW, as I have been dealing with SQL Server for ~20 years.
The product is open-source, so you are free to code your own extensions to monitor/alert on Always On.
Another very simple possibility is to set up Alerts in SQL Agent to notify you when certain things happen. Easy-peasy, lemon-squeezy. 🙂
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 4, 2015 at 3:06 pm
Thanks for the reply Kevin...Zabbix is big in the Linux world and we have about 50% Linux servers and 50% Windows...It is Open Source and works quite well but can be somewhat complicated to set up...
The reason we don't use Alerts within SQL Server is we have many of our servers behind firewalls and they restrict email. Also we are responsible for 100 or more servers and the number grows evey week and setting up the jobs/alerts/Operators gets tedious...So using Zabbix would allow us to have one alert and simplify maintenance...
February 6, 2015 at 10:23 am
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 Server:Database 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
February 6, 2015 at 11:25 am
Thanks Wilbur...
I deided t use the "UserParameter= " approach and setup a query to Sql Server fom Zabbix and look at the result set and then trigger off that (i.e. 0 = success). Here is what I added to the Zabbix Agent Conf file minus real user/pasword...Once the UserParameter is added to the .conf file I restarted the agent and went into Zabbix and added an Item called MSSQL.SyncHealth...Set up the trigger and I can now monitor when my servers have a Mirror/AlwaysOn Partner failure...
UserParameter=MSSQL.SyncHealth,"D:\Program Files\Microsoft SQL Server\110\Tools\Binn\SQLCMD.EXE" -d Master -U SQLServerUser -P ABC123 -h -1 -W -Q "SELECTCOUNT (*) as NotHealtyCount
FROM[master].[sys].[dm_hadr_database_replica_states]
WHERE[synchronization_health_desc] != N''HEALTHY''
Union
SELECTCount(*) as NotHealtyCount
FROMsys.database_mirroring
WHEREmirroring_role_desc IN (''PRINCIPAL'',''MIRROR'')
ANDmirroring_state_desc NOT IN (''SYNCHRONIZED'',''SYNCHRONIZING'')"
This gave me the ability to look at severs that are using Mirroring and those that are using AlwaysOn..
Note for every sever you want to monitor you will need to add the UserParameter= to the .conf file for that server, the Item/Trigger only has to be added to he Template file once but any new servers will need to be added to the Host group that you are monitoring..
Thanks again for all the help
February 6, 2015 at 1:26 pm
You might consider putting the logic into a stored procedure. You could restrict the permissions of the SQLServerUser login and not allow it to run arbitrary T-SQL. In the event you need to adjust it, you would update the stored procedure instead of editing the Zabbix configuration file.
If the Zabbix agent runs as a Windows account, you should be able to use Windows authentication for the connection. Then you would not need a login/pw in the configuration file.
Also, the path for SQLCMD.EXE should be in the path environment variable, you might be able to run it without specifying the full path. Advantage, would eliminate version specific path of SQL Server 2012 (in you example), possible disadvantage of dependence on the path environment variable.
February 9, 2015 at 3:29 am
I use the following code to monitor our Alwayson group.
SELECT ar.replica_server_name,
DB_NAME(dbr.database_id) AS [DBName] ,
dbr.database_state_desc,
dbr.synchronization_state_desc,
dbr.synchronization_health_desc,
ISNULL(CASE dbr.redo_rate
WHEN 0 THEN -1
ELSE CAST(dbr.redo_queue_size AS FLOAT) / dbr.redo_rate
END, -1) AS [LogRedoDelaySeconds] ,
ISNULL(CASE dbr.log_send_rate
WHEN 0 THEN -1
ELSE CAST(dbr.log_send_queue_size AS FLOAT)
/ dbr.log_send_rate
END, -1) AS [LogSendDelaySeconds] ,
dbr.redo_queue_size ,
dbr.redo_rate ,
dbr.log_send_queue_size ,
dbr.log_send_rate
FROM [master].sys.availability_replicas AS AR
INNER JOIN [master].sys.dm_hadr_database_replica_states AS dbr
ON ar.replica_id = dbr.replica_id
--WHERE dbr.redo_queue_size IS NOT NULL
February 9, 2015 at 3:35 am
TheSQLGuru (2/4/2015)
Until you mentioned it I had never heard of Zabbix. That isn't a good sign for you BTW, as I have been dealing with SQL Server for ~20 years.The product is open-source, so you are free to code your own extensions to monitor/alert on Always On.
Another very simple possibility is to set up Alerts in SQL Agent to notify you when certain things happen. Easy-peasy, lemon-squeezy. 🙂
Could I know which tool you used to monitor your server, I'm going to develop a tool for monitor sql server including perfomace counter/ DB State/ Mirrror State/ Replication latency/ backup and so on, so I want to know what the other people do?
Thanks.
February 9, 2015 at 5:54 am
gaojia2004 (2/9/2015)
TheSQLGuru (2/4/2015)
Until you mentioned it I had never heard of Zabbix. That isn't a good sign for you BTW, as I have been dealing with SQL Server for ~20 years.The product is open-source, so you are free to code your own extensions to monitor/alert on Always On.
Another very simple possibility is to set up Alerts in SQL Agent to notify you when certain things happen. Easy-peasy, lemon-squeezy. 🙂
Could I know which tool you used to monitor your server, I'm going to develop a tool for monitor sql server including perfomace counter/ DB State/ Mirrror State/ Replication latency/ backup and so on, so I want to know what the other people do?
Thanks.
I am a consultant, so I don't have servers to monitor - I help my clients monitor theirs. 🙂 They use a wide variety of products: SCOM, Confio, Red Gate, Idera. There are other low-end products out there. Many roll their own scripts and use SQL Agent for monitoring/alerting. MOST don't do anything at all, which is sad.
Personally I think you have MUCH better things you can do with your time than reinvent the wheel, but if you are just doing it because you like it then have a blast!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 17, 2015 at 4:59 am
Hi Reg_Mayfield
Hope you received my message
Appreciate your response.
Regards
August 17, 2015 at 9:46 am
Yes I did and I asked a co-worker to respond as he will be taking over this functin in our shop...I am retiring in two weeks and thought it would b better for him to pick up any followup questions such as yours...We have implemented a number of custom USERParameters entries that have worked quite well for us...We are currently using SQL Server 2012 and 2014 monitored by Zabbix..
August 21, 2015 at 12:59 am
okay thanks.
Take care
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply