Assistance with monitoring SQL Server 2016 using Zabbix Agent

[greavette]

Hello Forum,

I've installed Zabbix 5.0 LTS on Ubuntu Server 20.04.
I have a windows server 2019 with SQL Server 2016 installed. This server is currently monitored using Zabbix-Agent2 successfully for Generic Windows OS Template

I will be monitoring SQL on this server in our Zabbix. I'm looking for advice/direction on how best to achieve this. Here is what I have achieved so far with my investigation:

This Zabbix page references monitoring SQL Server 2017 and 2019 with Zabbix 5.4:

https://www.zabbix.com/integrations/microsoft_sql

Step 1 from this post says the following:

• Create an MSSQL user for monitoring. For example, zbx_monitor.
• View Server State and View Any Definition permissions should be granted to the user.
• Grant this user read permissions to the sysjobschedules, sysjobhistory, sysjobs tables.

I've created a user in my SQL Server Management Studio with all the requested permissions.

My questions related to this task are:

1. My SQL Server is 2016 and my Zabbix Server is 5.0 LTS. Are the above instructions going to work for me with my task?
2. Do I install a SQL Template on my Zabbix 5.0 LTS server so that I can monitor my SQL Server v2016? If so which template do I download and install? I don't see a template available in my Zabbix for MS SQL?

Thank you in advance for any advice this forum can provide me.

[greavette]

Hello Forum,

I've moved ahead with setting up monitoring of my SQL Server using the Template already in Zabbix 5. Not sure how I missed that. I've installed ODBC on my Zabbix Server using the instructions from the link. I've added the template to my Server in Zabbix and I've modified the {$MSSQL.PASSWORD} and {$MSSQL.USER} macros for the database user I created.

Latest Data is not working to show my SQL data in Zabbix so it appears I have an issue with either my {$MSSQL.DSN} or {$MSSQL.INSTANCE} macros.

Question #1:
I've spoken to our DBA about the instance name. He said there is no instance name as our database server is not a named instance? I'm not sure what that means then with regards to Zabbix needing an instance. Can anyone suggest what instance macro I should use in Zabbix or can I leave that empty?

Question #2:
What is the macro for DSN looking for? Would this be the hostname that Zabbix uses to connect to the server or the name from our DNS server that resolves to our database server IP? Why is this needed if I already have a Zabbix agent installed on my Windows Server? Can anyone suggest what is needed in this DSN macro or can I leave this unchanged? Do all SQL databases use or have a DSN?

Thank you.

[Spectator]

Dear greavette,

Have you been able to find a solution to your problem since then?
I got stuck in the same place.
I've opened a topic about this, so far I've come across it:

https://www.zabbix.com/forum/zabbix-help/435221-mssql-monitoring-problem

Zabbix version is 5.0.17 LTS

[mattjan]

1. I've never used instance name

2. It doesn't use Zabbix agent, Zabbix server call directly to your SQL server, for dsn example I have one of ODBC like:

[frmcluster]
Driver = ODBC Driver 17 for SQL Server
Server = frmClusterSvc (some of them I use IP address)
Database = dba

You can create macro with value = frmcluster or just specify DSN on Key field like db.odbc.select[dbConnTotal,frmcluster]

Matt