Microsoft SQL Server: template with Named Instances

[scharlesworth]

Here is what I am trying to do:

Almost all Microsoft SQL Server templates I have seen, items like the following are defined:

perf_counter[\SQLServer:Memory Manager\Optimizer Memory (KB)]

This is great if you only have the default instance. Almost all SQL Servers I have seen in the wild used named instances, so for example for a Server with instance 'SCRATCH' will have this as the equivalent for the above:

perf_counter[\MSSQL$SCRATCH:Memory Manager\Optimizer Memory (KB)]

So ideally I'd be able to define templates w/ items like this:

perf_counter[\{$DB_INSTANCE}:AccessMethods\Extent Allocated/sec]

I tried this w/ Macros and defining {$DB_INSTANCE} at the host level. Didn't work. I'm not sure if that's me defining it wrong or macros not working for this case. I haven't had much luck searching for a solution via Google or here. Has anybody encountered/handled this?

[Clifra Jones]

SQL Macros

If your setting your macro value in your host configuration to:

MacroName = {$DB_INSTANCE}
MacroValue = MSSQL$SCRATCH

It should work. I did mine a bit different I set I seg my values as

MacroName = INSTANCENAME
MacroValue = SQLEXPRESS

And set the item as:
perf_counter[\MSSQL${$INSTANCENAME}:Memory Manager\Optimizer Memory (KB)]

[seemach1]

Hello,

I was able get all the counters working using the solution below. My question is how to I modify the check for the SQL Server and Agent states for my non-default instances?

Regards,

Chris

[SQLSteinar]

Template available for SQL Server with multiple instances

I posted one at https://www.zabbix.org/wiki/Zabbix_Templates a few weeks ago. Have a look and see what you think!

[irvined]

Hi,

That looks really useful. Do you have an installation/configuration document anywhere?

Regards,

Doug

[SQLSteinar]

SQL server monitoring

Sorry, no dox available apart from the little text in the link .. Hopefully I get around to updating it a bit more, and then I might put a document together. But please ask if you have specific questions or problems!

[eric11]

Great job

I posted one at https://www.zabbix.org/wiki/Zabbix_Templates a few weeks ago. Have a look and see what you think!

Hi, I think this template is very useful! But I have a question: if I have only four istances on my host what in I discover in the "item" field? Four item enable (with my istances) and five not supported? I like to have my items very ordered . Thanks for great job!

[SQLSteinar]

Instances

Hello! Yes, unfortunately you will have a lot of "Not Supported" items on you hosts with this template. But in regular use you don't see them, only when you edit the host :-)

And thanks for the praise :-)

regards
Steinar

[Heilig]

SQLSteinar, thank you for the templates. I corrected it a bit. Perhaps will be useful for someone - LLD for MS SQL Server.

[Ranjith.Rokkam]

Hi Team - Greetings!

I need your help in montiroing the Named instances level monitroing in MS SQL through templates.

MS SQL services are running under named instance and MS SQL services need to be in monitoring.

Please help me on this.

Regards,
Ranjith R

[Clifra Jones]

Hi Team - Greetings!

I need your help in montiroing the Named instances level monitroing in MS SQL through templates.

MS SQL services are running under named instance and MS SQL services need to be in monitoring.

Please help me on this.

Regards,
Ranjith R

Just use MSSQL by ODBC it will work fine.

https://git.zabbix.com/projects/ZBX/repos/zabbix/browse/templates/db/mssql_odbc?at=release/6.4

Follow the instruction. You will need to install the Microsoft ODBC driver and set up the ODBC.INI to create the DSN entries for your server.

example:
[SQLSERVER]
Driver = ODBC Driver 17 for SQL Server
Server = tcp:SQLSERVER,1433
Encrypt = no


create the SQL user used to monitor the server i.e. zbx_monitor
Then configure the macros.
{$MSSQL.DSN} = your DSN name
{$MSSQL.INSTANCE} = your instance name
{$MSSQL.USER} = zbx_monitor
{$MSSQL.PASSWORD} = user password (make sure you set this to secret.

[Ranjith.Rokkam]

Thank You Clifra Jones

[aseques]

Hi, after reading the docs, I understand that when using this plugin I will have to create an ODBC for each instance (there doesn't seem to be any instance discovery mentioned on the readme), could someone clarify me?

Regards,

Joan

[nagaraj]

Hello, I created a testuser in MSSQL and tested it using * isql sqltestsrv testuser testpwd * in the CLI. It appears to be connected and functioning, but the front end is displaying an error data source not found and no default driver specified error




Experts kindly advise.