PRTG Manual: Monitoring Databases

Monitoring your databases lets you make sure that database queries are processed in time, and that the database itself performs within the defined parameters. Furthermore, database monitoring with PRTG can alert you via a corresponding sensor status if database queries return unexpected result values.

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

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

It is also possible to monitor many other database servers. For this case, PRTG uses the ActiveX Data Objects (ADO) interface.

There are two types of database sensors:

  • Sensors that monitor 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 that monitor 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 that can monitor 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 sensors are available for this kind of monitoring:

For these sensors, you can define valid Structured Query Language (SQL) statements that the sensors send to the database server. Define the queries in an SQL script file and store it in the respective \Custom Sensors\sql subfolder of the PRTG program directory.

You can select this SQL script when you add the sensor. 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 channel settings to define limits for specific values.

i_round_redThese sensors need .NET 4.7.2 or later installed on the probe system.

Alternatively, you can monitor almost all available database servers with the ADO SQL v2 sensor via an 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 must reconstruct your data table.

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

Sample Channel Value Selection

Description

Channel Value Selection by Column Number

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

Possible return values for this option:

  • 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 shows 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:

  • 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 shows the value in column 0 of the row you specify. Consider you define "1" as row number. Then the channel value is "01" because it is the cell in row 1 and column 0.

Possible return values for this option:

  • 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 shows 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 channel value is "678" because it is the cell in the same row in column 1 as the key in column 0.

Possible return values for this option:

  • 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 User-defined Function (UDF) 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 might 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

  • Create a new SQL v2 sensor and select Data Processing during sensor creation.
  • In the settings of your SQL sensor, select Column name under Select Channel Value by.
  • Enter row_count into the Channel #x 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 observe performance externally. They do not read out any values of the database, neither do they send SQL queries to databases. This sensor is only available for Microsoft SQL.

The Microsoft SQL server sensors monitor performance via 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, 2016, 2017, and 2019: