Database Agent configuration

It is important for configuration to differentiate between the database monitoring on the DBMS instance level, overarching a whole AppMon installation described here, and the JDBC, ADO.Net, and PHP DB sensors on the application level. The sensors on the application level capture SQL statements and bind values, as well as their count and timing. They have the Java, .NET, and PHP Agents respectively.

In case of issues, see the Database Agent FAQ.

Initial configuration

You can create a Database Agent the following ways:

  • The Database Agent Configuration dialog box, while working in the Application Environment Configuration wizard.
    1. In the AppMon Client, click the gear icon in the top right corner of the Monitoring dashboard, and select Configure Environment to access the wizard.
    2. Select your application type, and click Configure in the Databases section of the step 2.
  • The Create Database Agent dialog box.
    1. In the AppMon Client, click Settings > Dynatrace Server > Databases.
    2. Click Create.

The same information is needed whether you are creating and configuring a new database agents, or creating an agent as part of configuring an application environment. Both dialog boxes are essentially the same. See Databases for instructions on creating and configuring database agents.

Automatic cluster discovery

You can use automatic cluster discovery to configure Oracle Databases with Real Application Clusters (Oracle RAC). To do this, select Oracle Database with Real Application Clusters (RAC) in the Database type field of the Create Database Agent or Database Agent Configuration dialog box and fill other data for any of your RAC node. After testing connection, confirm any discovered nodes.

For any issues with cluster automatic discovery, you can discover all cluster nodes one by one, as they are correlated with each other automatically. After adding Agents for cluster, they are visible as separate agents in all configuration screens and can be edited manually.

Oracle RAC 12c leaf nodes

With Oracle Database with Real Application Clusters (RAC) 12c, new type of base platform was introduced: Oracle Flex Clusters. It contains two types of nodes: Hub Nodes and Leaf Nodes. AppMon is able to monitor only nodes, where actual database instance is running — and it can run only on Hub nodes. Because of that, monitoring Leaf nodes with Database Agent is not possible. However, Host Agent can be installed on machine, in order to collect data like memory & CPU usage and more.

Edit Database Agent

You can edit Database Agent properties in the Client by selecting Settings > Dynatrace Server > Databases and clicking Edit. Note that Database type, Agent name, and Collector properties are not editable.

When specifying database user for the Agent, make sure to grant necessary permissions for it.

You can also configure JDBC, ADO.NET, and PHP DB sensors in tiers > Sensor Configuration for a specific System Profile sensor.

Grant necessary permissions for DB monitoring

The user that connects to the DB instance needs to be granted the following permissions:

Database Platform Permissions needed
Oracle
  • CREATE SESSION
  • SELECT_CATALOG_ROLE
MS SQL Server Database login needs the following permissions:
  • CONNECT SQL
  • VIEW SERVER STATE
  • VIEW ANY DATABASE (to see tablespaces)

    User in the master database needs the following permissions:
  • CONNECT
  • VIEW DATABASE STATE (to see locks, tablespaces, wait states)

    For each database that should be included in the Live View tablespaces report and execution plans for its queries:
  • CONNECT
  • VIEW DATABASE STATE
To view execution plans for stored procedures user needs access to stored procedures (at least VIEW DEFINITION). This can be granted on the whole database scope, server, schema or per individual stored procedure.
See example
MySQL
  • SHOW DATABASES
  • PROCESS
  • SELECT
  • EXECUTE
AppMon 2018 February Aurora
  • SHOW DATABASES
  • PROCESS
  • SELECT
  • EXECUTE
DB2
  • SQLADM (or wider: DBADM),
  • SYSMON (or wider: SYSMAINT, SYSCTRL, SYSADM),
  • DATAACCESS (or per object permissions, see DB2 section below)

Refer to the release notes for more details about supported versions.

SQL Server 2012 & 2014

CREATE LOGIN <dtagent> WITH PASSWORD = 'password';
GRANT CONNECT SQL TO <dtagent>;	 
GRANT VIEW SERVER STATE TO <dtagent>;
GRANT VIEW ANY DATABASE TO <dtagent>;

USE master;
CREATE USER <dtagent> FOR LOGIN <dtagent>;
GRANT CONNECT TO <dtagent>;
GRANT VIEW DATABASE STATE TO <dtagent>;

-- for each monitored database
USE <monitored_database>;
CREATE USER <dtagent> FOR LOGIN <dtagent>;
GRANT CONNECT TO <dtagent>;
GRANT VIEW DATABASE STATE TO <dtagent>;
GRANT VIEW DEFINITION TO <dtagent>;

Oracle Database 11g/12c

CREATE USER dtagent IDENTIFIED BY password
default tablespace users
temporary tablespace temp;

GRANT CREATE SESSION, SELECT_CATALOG_ROLE TO <dtagent>;

DBMS_LOB package with granted EXECUTE permission is required to fetch queries' explain plans.

Access to Dynamic Performance Views need to be granted for Oracle monitoring.

MySQL 5.6/5.7

CREATE USER 'dtagent'@'%' IDENTIFIED BY 'password';
GRANT SELECT, EXECUTE, PROCESS, SHOW DATABASES on *.* to 'dtagent'@'%';

MySQL User created for the database agent must use default password encryption (mysql_native authentication plugin). Other password encryption methods, like SHA256 (sha256_password authentication plugin) are not supported.

To view SQL Execution plans when using MySQL Database Agent, the user must also have the SELECT privilege on the schema containing tables specified in the query

To use the database agent with MySQL releases prior to 5.7.7, the sys schema (documentation) must be installed, see sys schema project page for installation instructions. Additionally, Performance Schema (documentation) must be enabled.

AppMon 2018 February Aurora

GRANT SELECT, EXECUTE, PROCESS, SHOW DATABASES on *.* to 'dtagent'@'%';

To view SQL Execution plans when using Aurora Database Agent, the user must have the SELECT privilege on the schema containing tables specified in the query

To use the database agent with Aurora the sys schema (documentation) must be installed, see sys schema project page for installation instructions. Additionally, Performance Schema (documentation) must be enabled.

DB2 9.7.0.11, 10.1, 10.5, 11.1

See Database Agent configuration - DB2