<%NUMBERING1%>.<%NUMBERING2%>.<%NUMBERING3%> PRTG Manual: Monitoring Databases

Monitoring your databases enables you to ensure that, on the one hand, database queries are processed in time, and, on the other hand, that the database itself performs within the defined parameters. Furthermore, database monitoring with PRTG makes it possible to be alerted via a corresponding sensor status if database queries return an unexpected result value.

PRTG comes with built-in native sensors for the most common databases:

  • Microsoft SQL servers
  • MySQL servers
  • PostgreSQL servers
  • Oracle SQL servers

However, it is possible to monitor many other database servers. For this concern, PRTG uses the ActiveX Data Objects (ADO) interface.

There are two types of database sensors:

  • Sensors monitoring databases directly: Monitor databases from the user perspective. These sensors send a request to the database server and receive corresponding values. You can optionally process data tables and show values in individual channels or monitor transactions.
  • Sensors monitoring database performance: Monitor databases with a more abstract view on the servers. Usually, these sensors monitor performance counters via Windows Management Instrumentation (WMI).

Sensors Monitoring Databases Directly

PRTG provides several sensors which can "look into" the content of databases. Sensors of this type connect to the database server, execute a defined query, and show the execution time of the whole request and the query. You can use these sensors to process the data table and show requested values in individual channels.

The following sensor types are available for this kind of monitoring:

For these sensor types, you can define valid SQL statements that the sensors send to the database server. Define the queries in an SQL script file and store it into the respective \Custom Sensors\sql\ subfolder of your PRTG installation (see section Data Storage for details). You can select this SQL script when you add the sensor to PRTG. With every scanning interval, the sensor executes this script with the defined query against the database and the database returns corresponding values in individual channels (see the example below for sample channel value selections). Use the Sensor Channels Settings to define limits for specific values.

Note: These sensor types need .NET 4.0 or later installed on the computer running the PRTG probe.

Alternatively, you can monitor almost all available database servers with the ADO SQL v2 Sensor via an ActiveX Data Objects (ADO) connection.

Example: SQL Channel Value Selection

The SQL (v2) sensors determine their channel values by using column numbers, column names, row numbers, or key value pairs. This section shows which option you can choose to get the desired value from an SQL data table.

Consider the following data table that an SQL query might return from a database:

article_id

articles_available

first_listing

orders

00

12

2001

4

01

345

2005

56

02

678

2008

290

03

90

2012

32

This data table has four columns with the following numbering:

  • Column 0 has the name "article_id"
  • Column 1 has the name "articles_available"
  • Column 2 has the name "first_listing"
  • Column 3 has the name "orders"

The numbering of columns starts with 0, as well as the numbering for rows starts with 0. The table has four rows, each row contains the properties of one "article". The "articles" have the IDs 00, 01, 02, 03. This also illustrates the proper row numbering (0, 1, 2, 3).

With the options for channel value selection in SQL sensors, you can read out the following values:

  • All values that are in row 0 (here: 00, 12, 2001, 4)
  • All values that are in column 0 (here: 00, 01, 02, 03)
  • All values that are in column 1 (here: 12, 345, 678, 90)

It is not possible to get values from any other cell in a data table. If you need this, you have to re-construct your data table.

The following samples show possible results for channel value selections regarding this data table:

Sample Channel Value Selections

Channel Value Selection by Column Number

This channel will show the value in row 0 of the column you specify. Consider you define "1" as column number. Then the sensor channel value is "12" because it is the cell in column 1 and row 0.

Possible return values for this option are:

  • Column number "0" returns "00"
  • Column number "1" returns "12"
  • Column number "2" returns "2001"
  • Column number "3" returns "4"

Channel Value Selection by Column Name

This channel will show the value in row 0 of the column you specify. Consider you define "orders" as column name. Then the channel value is "4" because it is the cell in column "orders" and row 0.

Possible return values for this option are:

  • Column name "article_id" returns "00"
  • Column name "articles_available" returns "12"
  • Column name "first_listing" returns "2001"
  • Column name "orders" returns "4"

Channel Value Selection by Row Number

This channel will show the value in column 0 of the row you specify. Consider you define "1" as row number. Then the sensor channel value is "01" because it is the cell in row 1 and column 0.

Possible return values for this option are:

  • Row number "0" returns "00"
  • Row number "1" returns "01"
  • Row number "2" returns "02"
  • Row number "3" returns "03"

Channel Value Selection by Key Value Pair

This channel will show the value in column 1 of the same row where the key in column 0 was found. Consider you define "02" as key. Then the sensor channel value is "678" because it is the cell in the same row in column 1 as the the key in column 0.

Possible return values for this option are:

  • Key "00" returns "12"
  • Key "01" returns "345"
  • Key "02" returns "678"
  • Key "03" returns "90"

This sample channel value selection illustrates how to choose the correct option to get needed values from an SQL data table and shows which cells the SQL sensors can address.

UDF: Counting Returned Rows

If you execute a UDF (User Defined Function) on the SQL server and want to know how many rows this UDF returns, follow these steps:

  • A command to execute your UDF on the SQL server may look like this, for example:

exec myUDF

  • To get the information how many rows this UDF returns, extend the query in your SQL script:

exec myUDF;
select @@rowcount as row_count

  • In the settings of your SQL sensor, choose the option Select Channel Value by Column name
    Note: If not done yet, you have to create a new SQL v2 sensor and enable Data Processing while sensor creation.
  • Enter row_count into the Column Name field of the channel to show the value from this column in the channel.
  • To count table rows returned by a SELECT statement, choose the option Count table rows in the sensor settings section Data Processing.

Sensors Monitoring Database Performance

Performance sensors for database servers have a more "abstract" view on databases and regard performance "from the outside". They do not read out any values of the database, neither do they send SQL queries to databases. This sensor type is only available for Microsoft SQL.

The Microsoft SQL server sensors monitor performance via Windows Management Instrumentation (WMI). You can manually set up different performance counters for your server instances, for example, general statistics, access methods, buffer and memory manager, locks, and SQL statistics.

Microsoft SQL Server performance sensors are available for Microsoft SQL Server 2008, 2012, 2014, and 2016:

 

Sensor Technologies—Topics

Keywords: