Translations of this page:

Osmius

MSSQL Osmius agent
Agent name: osm_ag_MSSQL001 Agent code: MSSQL001
Subject: Microsoft SQL server Osmius agent User manual
Date: 24/05/2008 Revision date: 26/05/2008

General Information

This agent can monitor several parameters of MSSQL server. It have been tested with MSSQL 2005 server running over Microsoft Windows 2003 Server. However, we recommend checking its functionality before implanting it in a production environment.

MSSQL agent has been developed using functionalities and enhancements of Osmius framework and ACE libraries, so it is necessary to install the ACE libraries for the proper deployment and operation of MSSQL Osmius agent. See chapter: installation.

MSSQL001 agent provides up to 9 basic events with configuration parameters to allow scaling of a very simple way.

All events are remote, so not intrusive. Monitoring is done through the network connection, by this reason you have to provide some connection information, see connection information. They are based on queries against “master” data base views of MSSQL server.

MSSQL Instance

As a general rule each Osmius agent can monitor one instance type. If you are not familiar with these concepts check out the glossary. Each instance is individually defined in the configuration file (if you want further information go to agents and instances); depending on agent type is the instance type and depending on instance is the connection info.

CONNECTION_INFO

The connection information or connection_info is data that the agent needs to know to connect to the instance. (See more about the connection_info)

To MSSQL the connection_info prototype would be:

CONNECTION_INFO= -u USER -p PASSWORD -s IP_ADDR_SERVER

Replace the following:

  • USER: Privileged user name with grants to connect and “SELECT” on “master” data base and views. See chapter: prerequisites. Mandatory.
  • PASSWORD: User's password. Mandatory.
  • IP_ADDR_SERVER: Microsoft SQL Server IP address. Mandatory.

Examples:

CONECTION_INFO = -u admin -p password -s 211.23.45.67

OBSERVATION: We recommend creating a unique user with restricted permissions to be used by agent.

TYPE

The type defines the instance type to be monitored. Every declared instance must be associated with a type as you can see here

In this case:

TYPE= MSSQL001

Event summary table for MSSQL

Here is briefly the capabilities of this agent, further down on this page each event is described in more detail.

EVENT DESCRIPTION c w a tseconds Extra parameters / Remarks
MSSELCNT User defined table records counter 0 Admin Admin 600 -U “user_defined_table”
Silent mode ( -s) recommmended
MSFREPAG Free pages counter 1 Admin Admin 600 Silent mode ( -s) recommmended
MSUSDMEM MB used by MSSQL server 0 Admin Admin 600 Silent mode ( -s) recommmended
MSPAGSPL Splitted pages counter 0 Admin Admin 600 Silent mode ( -s) recommmended
MSNUMUSU MSSQL user connections counter 0 Admin Admin 600 Silent mode ( -s) recommmended
MSNUMLCK Locks counter 0 Admin Admin 600 Silent mode ( -s) recommmended
MSMEMDAT Used MB to store data files 0 Admin Admin 600 Silent mode ( -s) recommmended
MSMEMLOG Used MB to store log files 0 Admin Admin 600 Silent mode ( -s) recommmended
MSBATREQ Batch requests counter 0 Admin Admin 600 Silent mode ( -s) recommmended

MSSQL001 agent events

MSSELCNT

MSSELCNT event returns the number of records in “dbo.spt_values” table or in a user defined table.

Extra parameters:
This event need an extra parameter to work:

PARAMETER MEANING Mandatory
-U -U “user_defined_table”.
You can define a specific table to perform the counting of records
No.

Return values:

VALUE MEANING
-1 Error
X # records

Recommended parameters:

Comparison type Direct. The higher value the higher severity (-c 0)
Monitoring interval Depends on instance importance
Warning threshold Consult on administrator
Alert threshold Consult on administrator

Parameter setting example:

MSSELCNT = -t 5 -c 0 -w 200 -a 300 -T "records in monitor table" -U "dbo.spt_monitor"

Remarks: None

MSFREPAG

MSFREPAG returns available free pages.

Return values:

VALUE MEANING
-1 Error
X # free pages

Recommended parameters:

Comparison type Inverse. The lower value the higher severity (-c 1)
Monitoring interval Depends on instance importance
Warning threshold Consult on administrator
Alert threshold Consult on administrator

Parameter setting example:

MSFREPAG = -t 5 -c 1 -w 200 -a 100 -T "# free pages"

Remarks: None

MSUSDMEM

MSUSDMEM events returns the memory measured in MB used by MSSQL server.

Return values:

VALUE MEANING
-1 Error
X # used MB

Recommended parameters:

Comparison type Direct. The higher value the higher severity (-c 0)
Monitoring interval Depends on instance importance
Warning threshold Consult on administrator
Alert threshold Consult on administrator

Parameter setting example:

MSUSDMEM = -t 5 -c 0 -w 100 -a 300 -T "# MB MSSQL"

Remarks: None

MSPAGSPL

MSPAGSPL event returns the number of splitted pages in MSSQL server.

Return values:

VALUE MEANING
-1 Error
X # splitted pages

Recommended parameters:

Comparison type Direct. The higher value the higher severity (-c 0)
Monitoring interval Depends on instance importance
Warning threshold Consult on administrator
Alert threshold Consult on administrator

Parameter setting example:

MSPAGSPL = -t 5 -c 0 -w 5 -a 20 -T "MSSQL splitted pages"

Remarks: None

MSNUMUSU

MSNUMUSU event returns the number of current connection to MSSQL server.

Return values:

VALUE MEANING
-1 Error
X # connections

Recommended parameters:

Comparison type Direct. The higher value the higher severity (-c 0)
Monitoring interval Depends on instance importance
Warning threshold Consult on administrator
Alert threshold Consult on administrator

Parameter setting example:

MSNUMUSU = -t 5 -c 0 -w 100 -a 200 -T "connections MSSQL dev"

Remarks: None

MSNUMLCK

MSNUMLCK event retuns number of MSSQL locks.

Return values:

VALUE MEANING
-1 Error
X # locks

Recommended parameters:

Comparison type Direct. The higher value the higher severity (-c 0)
Monitoring interval Depends on instance importance
Warning threshold 1
Alert threshold 2

Parameter setting example:

MSNUMLCK = -t 5 -c 0 -w 1 -a 2 -T "MSSQL locks"

Remarks: None

MSMEMDAT

MSMEMDAT event returns used MB to store MSSQL data files.

Return values:

VALUE MEANING
-1 Error
X # data files used MB

Recommended parameters:

Comparison type Direct. The higher value the higher severity (-c 0)
Monitoring interval Depends on instance importance
Warning threshold Consult on administrator
Alert threshold Consult on administrator

Parameter setting example:

MSMEMDAT = -t 5 -c 0 -w 100 -a 2000 -T "MSSQL Datafiles MB"

Remarks: None

MSMEMLOG

MSMEMLOG event returns used MB to store MSSQL log files.

Return values:

VALUE MEANING
-1 Error
X # log files used MB

Recommended parameters:

Comparison type Direct. The higher value the higher severity (-c 0)
Monitoring interval Depends on instance importance
Warning threshold Consult on administrator
Alert threshold Consult on administrator

Parameter setting example:

MSMEMLOG = -t 5 -c 0 -w 10 -a 100 -T "MB MSSQL Logs"

Remarks: None

MSBATREQ

MSBATREQ events returns number of MSSQL batch requests.

Return values:

VALUE MEANING
-1 Error
X # batch requests

Recommended parameters:

Comparison type Direct. The higher value the higher severity (-c 0)
Monitoring interval Depends on instance importance
Warning threshold Consult on administrator
Alert threshold Consult on administrator

Parameter setting example:

MSBATREQ = -t 5 -c 0 -w 100000 -a 1000000 -T "# Batch Requests"

Remarks: None

MSSQL prerequisites

In order to compile, this agent requires a set of prerequisites, which are generic to compile any Osmius agent, you can see these prerequisites.

You need a user with special permissions to monitor MSSQL using Osmius:

  • Ensure SELECT privilege on the database “master” and view “sysperfinfo”.
  • Grant “View server state” permissions.
  • If you want to monitor user-defined tables you should apply SELECT permissions on these tables.

MSSQL makefiles and compilation

  • Make Project Creator (MPC) is used by Osmius, so creating Makefiles is a trivial task. If you want to learn more about MPC and Osmius check out the section of Makefiles on Osmius.
Due to MSSQL Osmius agent uses Microsoft Windows API is only possible to compile and run it on Microsoft Windows
  • You can generate Makefile in this way:

From the agent directory using console or terminal.

Windows and Visual C 8:

%ACE_ROOT%\bin\mpc.pl -type vc8 osm_ag_mssql.mpc
  • Now that you have created the Makefile, agent compiling is extremely simple.

Windows:

Double click over Osm_Ag_Mssql.vcproj. In Solution Configuration select Release. Press Rebuild option to compile.

Binaries are automatically installed in the bin directory of OSM_ROOT base directory.

Run MSSQL agent

MSSQL Osmius agent have the same running features of the other Osmius agents. You can check it out int he section Start and Stop Agents.

To run MSSQL agent without Osmius web console:

osm_ag_MSSQL001.exe -c osm_ag_MSSQL001.ini -m MASTERAG -p 1950 -d [>> osm_ag_mssql001.log]1)

Running in standalone mode

MSSQL Osmius agent, like the others Osmius agents, allows the execution in standalone mode. This option may be particularly useful when developing a new agent or to perform specific agent tests.

Basically you have to add a new value, called SNDCMD, to Osmius agent configuration file (osm_ag_MSSQL001.ini) as shown here.

The you have to execute MSSQL Osmius agentsetting Master Agent communications port to zero, for example:

osm_ag_MSSQL001.exe -c osm_ag_MSSQL001.ini -m 00000000 -p 0 -d

Tests list

Test performed to MSSQL Osmius agent.

Date: 24/05/2008
Test Result Remarks
Creating an instance with all its events in silent mode OK N/A
Creating an instance with all its events with custom text OK N/A
Creating an instance with all its events but no custom text OK N/A
Declaration of 3 instances with all its events to 5 seconds and keep it
running for 48 hours
OK N/A
Declare 2 instances, cause a disconnect and then reconnectOK N/A
Declare 1 instance and test each event OK N/A
Elimination of general parameter and check unbootable OK N/A
Elimination of instance CONN_INFO and check unbootable OK N/A
1) Optional, to store agent messages in a file
 
en/agentes/mssql001.txt · Last modified: 2012/12/05 18:57 by osmius
 
Recent changes RSS feed Creative Commons License Donate Powered by PHP Valid XHTML 1.0 Valid CSS Driven by DokuWiki