Knowledge Base

Setting up SQL Server Sensors for MS-SQL, MySQL and Oracle with IPCheck Server Monitor

Diese Seite steht leider noch nicht auf Deutsch zur Verfügung. Wir bitten um Ihr Verständnis!

Using the SQL Server sensors you can natively monitor the most used SQL servers: MySQL, Microsoft SQL, and Oracle SQL. Basically, the sensors monitor whether the database server process

  • A: accepts connections and
  • B: processes requests and
  • C: returns an expected result when executing a custom SQL command.

The sensors support the following server versions:

  • MS-SQL: SQL Server 2005, SQL Server 2000, SQL Server 7 and MSDE (requires OLE DB installed on the machine running the IPCheck Server and/or Remote Probe that accesses the server).
  • Oracle: Oracle 10g, 9i, 8i and Oracle 7
  • MySQL: MySQL server 5.0, 4.1, 4.0 and 3.23.

Configuring the sensor consists of two steps:

  • Setting connection relevant properties. This is mandatory to get a working sensor.
  • Optional configuration of a SQL expression which the sensor should execute over the existing connection to the database server.

The following fields are particular to these sensors. Some of them may not appear with all SQL Sensor types:

  • Database Name – in this field, the name of the database can be entered in order to access the database information. Do not confuse this with the name of the database server (which is set in the corresponding server setting in IPcheck). See the example below for further information regarding this property.
  • User and Password – provide the username and password to log into the database.
  • SQL Expression – provide an expression for querying or modifying database objects like tables, views, roles. When a cursor is returned (i.e. with a SELECT statement), only the first row of data will be fetched.
  • Result Set – check this checkbox if your SQL expression returns a cursor / result set. Then the value of the first column of the first row of the result set is used as value of the monitoring request (e.g. will be compared to the limits). Otherwise the “number of affected rows” is regarded to be the value of a monitoring request. The latter usually makes sense with a data modification statement like DELETE.
  • Instance (MS-SQL) – This holds the name of the instance if you want to connect to a ‘named instance’, otherwise it remains empty. Please check the MS-SQL specific section below for detailed information.
  • Interface (Oracle) – Oracle offers 2 possibilities for connection to the server, either via direct TCP/IP communication (SQL-NET) or via the Oracle Client Interface (OCI). Select the one you want to use for this sensor. Please check the Oracle specific section below for detailed information.
  • Port (Oracle/MS-SQL) – You can supply a TCP port number with MS-SQL Server or when using Oracle SQL-NET as connection interface. Please check the MS_SQL/Oracle specific section below for detailed information. Note: It is not possible to set the port numbers for MYSQL sensors!

Sample configuration to ensure a connection with the database server

General steps

  • Identify the IP address or the DNS name of your database server
  • Create a new server object in IPCheck. You can do this with the context menu of a group. If you already have an adequate server object created you can skip this step.
  • In the configuration page of this server object locate the ‘DNS Name’ property. Enter the IP address / DNS name from the first step. If you already have a server object with the correct address information you can use it and skip this step
  • Open the context menu of the server object from above and select ‘Add Sensor’
  • If your database server is not reachable from the IPCheck server location (i.e. due to firewalls) you can install a remote probe on a system “near your server” (in a network topography perspective) that can reach the database server (see the IPCheck manual for details on Remote Probes). In this case you can have to select the probe from the appropriate drop down on the upper left side of the sensor select page. Make sure that IP address / DNS name of the server object (as configured in the steps above) is correct for the Remote Probe.
  • Select the appropriate SQL Sensor type (MS-SQL, Oracle, MySQL) and continue to the sensor setup page after clicking the ‘OK’ button
  • In the username / password fields of the sensor setup page enter the credentials for the database user that has the necessary rights to connect to the selected database and, if the sensor should execute a SQL statement, take care that the database user has enough rights to access the affected database objects and enough rights to execute that type of SQL statement.

MS-SQL Specific Connection Settings

  • Identify the MS SQL Server Version you use. If it is SQL Server 2005 then identify the instance name. You can get the instance name from the SQL Server Configuration manger (shown in brackets after the Servername), often this is SQLEXPRESS.
  • With earlier SQL Server versions there is by default no instance name. But it is possible to create named instances there as well. In this case either you should know the name as you created the instance or the database admin should be able to tell you the instance name.
  • Note: Sometimes you see connection strings like SQLSERVER\SQLINSTANCE in database clients. The first part is the server name that was configured in the ‘General steps’ section of this document (setting of IPCheck’s server object). The second part is the instance name mentioned here. Do NEVER enter the string in this form in the instance field of the sensor setup page, only the second part (and without the backslash)
  • In SQL Server 2005 there is always an instance name so enter this name located in the previous step now in the ‘Instance Name’ field of the sensor setup page. With other SQL Server versions you should usually leave this property empty unless you are sure that the instance you want to monitor was created as a ‘named instance’
  • If your SQL Server runs the instance at a different static port than 1433 you can enter the port number in the ‘Port’ field of the sensor setup page. If your SQL Server uses the default value of 1433 or is configured for dynamic port setting then leave the ‘Port’ field empty.
  • The ‘database’ setting is a logical entity on the database server where database objects like tables, users, roles exist. Set the appropriate database that contains the table(s) which are used in the SQL statement of the sensor. Look at a ‘databases’ folder when browsing through the database objects with an SQL server management tool

Note: There may be restrictions which computers can log on to the sql server, so check the appropriate server settings if you have problems connecting to the server. Oracle Specific Connection Settings

  • You can select between an SQL-NET or and Oracle Client connection. The Oracle Client (OCI) connection has the advantage to support advanced features like load balancing but it requires the installation of the oracle client on the IPCheck server machine or in case of a remote probe on the location of the remote probe machine
  • With SQL-NET you have to supply the TCP/IP Port for the connection in the corresponding ‘Port’ Field of the sensor setup page. Usually the default value of 1521 is correct. With an OCI connection the setting of the port property is ignored
  • The ‘database’ setting depends on the connection type. With an OCI connection you have to enter the TNS alias of the database you want to connect to. Consult the Oracle Client software for a list of available aliases and use the one where the database tables you want to monitor exist. With a SQL-NET connection you have to supply the SID of the oracle database instance you want to connect to. The SID is a short string and can either be found in the properties of the corresponding alias in the oracle client software or experienced users can look directly in the tnsnames.ora file which can be found on every machine where an oracle client is installed.

My SQL Specific Connection settings Note: IPCheck doesn’t require nor supports connection though an explicit MY-SQL client.

  • The ‘database’ setting is a logical entity on the database server where database objects like tables or stored procedures exist. In case of the MY-SQL server it also reflects a physical directory structure where your database objects are stored. Enter the appropriate string which is the same as you would supply when invoking the mysql.exe admin tool (with the command line switch –p) or after the login with mysql.exe with the command ‘use’.

By Category