mssql monitoring?

[mconigliaro]

im curious as to whether anyone has been able to monitor mssql server statistics with zabbix. my first thought was to use proc_info (and possibly perf_counter) items, but im interested in what other people have done. any suggestions?

[cameronsto]

I believe MS SQL Server exposes values via SNMP. You could use those to monitor it. Sorry, I can't provide more information, I was looking into this recently but got sidetracked before I found much information.

-cameron

[bytesize]

# Microsoft SQL Server 2000
PerfCounter = [\SQLServer:Buffer Manager()\Database pages]
PerfCounter = [\SQLServer:Buffer Manager()\Free pages]
PerfCounter = [\SQLServer:_Databases(DATABASENAME)\Data File(s) Size (KB)]
PerfCounter = [\SQLServer:_Databases(DATABASENAME)\Log File(s) Size (KB)]
PerfCounter = [\SQLServer:General Statistics()\User Connections]
PerfCounter = [\SQLServer:Memory Manager()\Total Server Memory (KB)]

[claytronic]

I use Bytesize's example to setup PerfCounter monitoring of our SQL 2005 server. Notice that "_SQLServer" has been edited to "SQLServer" in the two lines used for reading the database file sizes.

MSSQL = 2005 SP1
OS = Windows 2003 R2 SP1
Zabbix Agent = 1.1.3 Build of Oct 23 2006

PerfCounter = db_pages,"\SQLServer:Buffer Manager()\Database pages",60
PerfCounter = db_free_pages,"\SQLServer:Buffer Manager()\Free pages",60
PerfCounter = db_data_file_size,"\SQLServer:Databases(DATABASE_NAME)\Data File(s) Size (KB)",60
PerfCounter = db_log_file_size,"\SQLServer:Databases(DATABASE_NAME)\Log File(s) Size (KB)",60
PerfCounter = db_userconns,"\SQLServer:General Statistics()\User Connections",60
PerfCounter = db_server_mem,"\SQLServer:Memory Manager()\Total Server Memory (KB)",60
PerfCounter = db_cpu_load,"\Process(sqlservr)\% Processor Time",60

I hope this helps anyone looking to monitor their MSSQL servers.

- Claytronic

[samwise]

problem with this

Hi,

I've added these perfmons to my .conf:

PerfCounter = db_pages,"\SQLServer:Buffer Manager()\Database pages",60
PerfCounter = db_free_pages,"\SQLServer:Buffer Manager()\Free pages",60
PerfCounter = db_data_file_size,"\SQLServer:Databases(BTD)\Data File(s) Size (KB)",60
PerfCounter = db_log_file_size,"\SQLServer:Databases(BTD)\Log File(s) Size (KB)",60
PerfCounter = db_userconns,"\SQLServer:General Statistics()\User Connections",60
PerfCounter = db_server_mem,"\SQLServer:Memory Manager()\Total Server Memory (KB)",60
PerfCounter = db_cpu_load,"\Process(sqlservr)\% Processor Time",60

I've used db_pages, data_file_size and db_userconns.

data_file_size is working fine. The other two keep showing 'Not Supported' in their item status.

Data was recieved for a bit and then dropped off, then for another short time and stopped.

I set them to active, but they revert to not supported - after polling happens I assume.

I have these options set for the item

db_pages
zabbix agent
db_pages
numberic(integer 64bit)
per/sec
custom multiplier
.01
30
90
365
Not supported
as is
as is
none

Thanks for any help.
Sam

[phredbroughton]

showing 'Not Supported'

So far every time I have seen an item work and then suddenly stop, it has been because I had the data type set to numeric(integer) and the value was either returned as a numeric(float) or with some of my UserParameter definitions, I found that it would occasionally return a leading blank (whitespace). Any time the value returned doesn't match up exactly with the item definition it flags itself as not supported. It would be nice if we could change this to alert that the value received was X and X doesn't match the defined data type ;-)

[samwise]

this was the problem, thanks! This also happens with other problems in the .conf file. Missing () after object names, no leading backslash before object.

[gpostaire]

Do you know a way to monitor scheduled jobs in MSSQL ?

[claytronic]

Do you know a way to monitor scheduled jobs in MSSQL ?

That's a great idea, gpostaire. The maintenance plan success/failures are logged in the msdb.dbo.sysmaintplan_log table. An agent check script could be built around the following SQL query. I'll tool around with this today if I have some extra time.

The query below returns any jobs that have failed in the last 24 hours of the query execution time.

SELECT
s.name,
sp.subplan_name,
l.start_time,
l.end_time,
l.succeeded
FROM
msdb.dbo.sysmaintplan_log AS l
INNER JOIN msdb.dbo.sysmaintplan_plans AS s ON l.plan_id=s.id
INNER JOIN msdb.dbo.sysmaintplan_subplans AS sp ON sp.plan_id=s.id
WHERE
DATEDIFF(d, l.end_time, GETDATE()) < 1
AND
succeeded = 0

[claytronic]

I added this UserParameter to the Zabbix Agent running on the SQL server to monitor for maintenance jobs that have failed in the last 24 hours.

Set your triggers to fire for any value greater than zero.

Note Make sure to edit the ##SQL_USER## and the ##SQL_PASSWORD## to a SQL account that has select access in the MSDB database.

UserParameter = db_maint_jobs,sqlcmd -d Master -U ##SQL_USER## -P ##SQL_PASSWORD## -h -1 -W -Q "SELECT COUNT(*) FROM msdb.dbo.sysmaintplan_log AS l INNER JOIN msdb.dbo.sysmaintplan_plans AS s ON l.plan_id=s.id INNER JOIN msdb.dbo.sysmaintplan_subplans AS sp ON sp.plan_id=s.id WHERE DATEDIFF(d, l.end_time, GETDATE()) < 1 AND succeeded = 0"

[boschertjd18]

Claytronic,

I took what you suggested and put it in the userparameter, except that I removed the -U and -P options. Therefore, I believe, the user under which the zabbix windows agent runs executes the sql statement. The user does have rights to run the statement against the db

However, when I run the zabbix_get command from the linux box against that parameter, I receive a ZBX_NOTSUPPORTED error. This happens for both your command and a new one I created:

UserParameter = sql_version,sqlcmd -d Master -h -1 -W -Q "SELECT @@version"

Do you have any ideas why this may be?

Thanks,
Joseph

[Alexei]

Specify full paths to all binaries you use.

[boschertjd18]

Alexei,

Thanks for you quick response. Unfortunately, plugging in the full path for the binaries still does not work. I restarted the ZABBIX 1.4 Client for Windows and I'm still returning the ZBX_NOTSUPPORTED from the zabbix_get command in linux. The commands run fine from command prompt in Windows.

Here is what I have in the .conf file:
UserParameter = ipconfig,c:\windows\system32\ipconfig

UserParameter = sql_hello,"c:\Program Files\Microsoft SQL Server\90\Tools\Binn\sqlcmd" -d Master -h -1 -W -Q "SELECT 'hello'"

UserParameter = sql_version,"c:\Program Files\Microsoft SQL Server\90\Tools\Binn\sqlcmd" -d Master -h -1 -W -Q "SELECT @@version"

Thanks,
Joseph

[claytronic]

boschertjd18,

Your SQL commands do not have the -E switch which forces NT Authentication. Have you set the Zabbix service to run under an NT account that has user access in your MSSQL server? This is why my queries use the -U <user> and -P <password> arguments. My Zabbix service runs under the local system account which does not have access to MSSQL. I can then lock down the zabbix MSSQL user account since I'm storing the password in plain text. (encrypted config files, Alexei?)

--
Claytronic

[boschertjd18]

Claytronic,

I did indeed make sure the zabbix service is running as a user who has access to the Master db. I also updated the command to run as -E, but no luck. Finally, I created a user and tried via sql server authentication, but again no luck.

Again, all the commands run fine from the Windows command prompt. In all instances, I received a ZBX_NOTSUPPORTED from the ./zabbix_get command. The log file reads "Parameter [sql_hello2] is not supported by agent on host..."

I seem to be running out of ideas. Could this be a bug within the Zabbix Windows Agent 1.4.0.1? Here is the User-Def section:

UserParameter = ipconfig,"c:\windows\system32\ipconfig"

UserParameter = sql_hello,"c:\Program Files\Microsoft SQL Server\90\Tools\Binn\sqlcmd" -d Master -E -h -1 -W -Q "SELECT 'hello'"

UserParameter = sql_hello2,"c:\Program Files\Microsoft SQL Server\90\Tools\Binn\sqlcmd" -d Master -U Master_RO -P r0_mas+er -h -1 -W -Q "SELECT 'hello'"

UserParameter = sql_version,"c:\Program Files\Microsoft SQL Server\90\Tools\Binn\sqlcmd" -d Master -E -h -1 -W -Q "SELECT @@version"

UserParameter = db_maint_jobs,"c:\Program Files\Microsoft SQL Server\90\Tools\Binn\sqlcmd" -d Master -E -h -1 -W -Q "SELECT COUNT(*) FROM msdb.dbo.sysmaintplan_log AS l INNER JOIN msdb.dbo.sysmaintplan_plans AS s ON l.plan_id=s.id INNER JOIN msdb.dbo.sysmaintplan_subplans AS sp ON sp.plan_id=s.id WHERE DATEDIFF(d, l.end_time, GETDATE()) < 1 AND succeeded = 0"

Regards,
Joseph