Update - 2019-05-28

Moved to another company with no Zabbix and/or MS Sql in its environment, hence archiving the repo.

TODO

-Add port number.

-Test with MS SQL 2017 on Windows and Linux.

zbx-mssql

Zabbix Template for monitoring and collecting Microsoft SQL Server statistics. Most requests are done via Windows performance counters (perf_counter), some via PowerShell and, optionaly, via ODBC.

System requirements

Optional, ODBC driver installed and configured on Zabbix Server (see below).

Features

State of MS SQL services
Multiple instances on one server
Global Server Statistics (70 items in total):
- Memory
- Cache
- Buffer Manager
- Access Methods
- Locks
- Errors and Failed Jobs
- Log Size
Database discovery via Powershell (Zabbix Agent) or ODBC directly
Statistics for discovered DB's (per DB):
- State
- Trasactions
- Log File size, utilization, flushes, growths, shrinks, etc.
18 graphs
Version information
{$ODBC}, {$MSSQL_USER} and {$MSSQL_PASSWORD} macros for customization
Tested on MS SQL 2008, 2012, 2014 & 2016.

Installation

Zabbix Configuration

Copy userparameter_mssql.conf to your Zabbix Agent folder, e.g. C:\srv\ZabbixAgent\conf\conf.d.
Copy PowerShell scripts (mssql_*.ps1) to the scripts folder, e.g. C:\srv\ZabbixAgent\scripts folder. IMPORTANT: if you use another folder for agent scripts, then update userparameter file in the previous step!
Import XML template file (zbx_template_mssql.xml) into Zabbix via Web GUI (Configuration -> Templates -> Import). Optional, if you want to use ODBC for discovering databases, then update XML file before importing, as all item prototypes are under a discovery rule that utilizes PowerShell via Zabbix Agent.
Configure regular expression in "Administration -> General -> Regular Expressions (dropdown on the right)":

Name: MS SQL Databases for discovery
Expression: ^(master|model|msdb|ReportServer|ReportServerTempDB|tempdb)$
Type: Result is FALSE

Import "MS SQL Server database state" value mapping (zbx_valuemaps_mssql.xml) in "Administration -> General -> Value mapping (dropdown on the right)". Or add it manually:

 0 -> ONLINE
 1 -> RESTORING
 2 -> RECOVERING
 3 -> RECOVERY PENDING
 4 -> SUSPECT
 5 -> EMERGENCY
 6 -> OFFLINE
 7 -> Database Does Not Exist on Server

Assign the imported template to a host.
Optional, if using ODBC, follow instructions below.
Restart Zabbix Agent and enjoy... or, actually, good luck in tuning MS SQL ;)

ODBC Configuration

In MS SQL, create a read-only user for accessing master DB, e.g. zbx-maint.
On Zabbix Server, install required packages. The following works for Debian 8 (jessie) and 9 (stretch): apt-get install odbcinst tdsodbc unixodbc
Create ODBC driver configuration file:

sudo vi /etc/odbc.ini
[srv01-mssql]
Driver = FreeTDS
Server = <SQL Server FQDN or IP>
PORT = 1433
TDS_Version = 8.0

Update macros for the monitored host under "Configuration -> Hosts -> hostname -> Macros":

{$MSSQL_PASSWORD} - <your generated pass>
{$MSSQL_USER}	  - zbx-maint
{$ODBC}		  - srv01-mssql

Troubleshooting

Check ODBC settings and credentials from console on Zabbix Server:

> isql -v srv01-mssql zbx-maint <password>
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL>

Kellett/zbx-mssql