Zabbix v7 Plugin MSSQL - is it possible to monitor multiple MSSQL instances ?

[guidem]

Hey folks,

I recently upgraded my Zabbix infrastructure to v7 and have been very happy to see that there was now a zabbix agent 2 plugin to collect metric from MSSQL server without DSN.

I configured it with no issue (zabbix plugin installation + configuration of the template) BUT I am now trying to make it work on a SQL server where there are multiples instances.

when you install the plugin, you "unlock" a new key called mssql which is then called in the differents items of the template but I have no way to configure an extra one like mssql2 in order to provide a different Macro for {$MSSQL.URI}.

Has anywone been able to achieve something similar ?


Thanks for your help!

[d0N91]

Hi,

I join the topic. Also I would like to monitor two MS SQL instances on one host. Is there any way to do this?

[guidem]

Hello,

Since then,I was able to make it work. It took some extra work though. I duplicated the original MSSQL by Zabbix agent 2 (by using clone button in the template interface so it generated new UUID). Then I exported the template as a XML file and use a File Editor to modify it.
I created new MACROS for all the required fields that need to change in this case :

• {$MSSQL.PORT2} : listening port of the second instance
• {$MSSQL.URI2} : ConnectionString of the second instance
• {$MSSQL.USER2} : sql user to connect the second instance (could be harmonized on all SQL instances to avoid multiple MACROS)
• {$MSSQL.PASSWORD2} : sql user to connect the second instance (could be harmonized on all SQL instances to avoid multiple MACROS)

I also created a new one called {$MSSQL.INSTANCENAME2} in order to make it appear on each item / trigger.

In the xml file I had to edit following lines:

• All the items configured with MACROS in the name like mssql.db.get["{$MSSQL.URI}","{$MSSQL.USER}","{$MSSQL.PASSWO RD}"] has to be edited to mssql.db.get["{$MSSQL.URI2}","{$MSSQL.USER2}","{$MSSQL.PASSWORD 2}"]
• All the items that are "generic" like mssql.db_info.raw. Since we cannot have duplicated item name, we have to edit the name eg. mssql2.db_info.raw (be caGreful with the reference in other item/graph/triggers)
• I added {$INSTANCENAME2} at the end of all the items/triggers/graph name

If you have more than 2 instances , you will have to do that multiples times , each time starting by cloning the MSSQL Agent 2 original template

[CanutaOS]

Hello,

I was following your post on monitoring multiple SQL Server Instances but I am having troubles with reaching the final goal, I am also new to Zabbix.

I have created all the macros accordingly to your post (created them in the yaml file format I decided to use, not in the interface).

Then I have tried numerous combinations of the following:

- first I edited all the items (master or non master) with the string like ["{$MSSQL.URI}","{$MSSQL.USER}","{$MSSQL.PASSWO RD}"] in their key, tried to import the file but got stuck on an error like the one below:

Incorrect value for field "master item id": value "perf counter .get["{$MSSQL.URISQLSHEPHERD}","{$MSSQL.USERSQLSHEP HERD }","{$MSSQL.PASSWORDSQLSHEPHERD}"]" not found.

- then I tried editing the key only for the master items but got the same error

- then I tried renaming only the non master items

- also tried various combinations to fix the above error until I get stuck on this error which gives no hints:

Invalid parameter "/1/master item id": a number is expected.

Could please detail the steps you took for dummies or, if it is not too much, send me a copy of your successful xml file? I just can't figure it out.

Thank you.

[guidem]

Hello, here are the export of the templates I used. There are 4 MSSQL by agent 2 as a root template and then 1 child per root template. So basically you have to link these templates on your SQL server depending on how much instances there are on your host.

[CanutaOS]

Great job at figuring it out! Thank you!

[kluvosk2]

@guidem I have followed your instructions (as now there is newer version of template so I wanted to use the latest one), fixed all errors which were reported when I was importing the template....so the template is imported successfully, but it has problem with mssql2.* item name, as it reports error 'Unknown metric mssql2.*' , as I am new in zabbix, am I missing something, some configuration on the instance, or the plugins?
Thank you for your reply.

[aseques]

I just opened a Feature Request mentioning this thread here at zabbix support https://support.zabbix.com/browse/ZB...2?filter=22768

Thanks for your work