Guidance for Monitoring Databases

Introduction

Prerequisite Details for Database Monitoring

    Azure SQL

    MySQL/MariaDB

    Microsoft SQL

    MongoDB

    Oracle

    PostgreSQL

    SAP HANA

Matrix of Metrics per Monitoring Type

Introduction:

Entuity supports management of the following on-prem database types:

• Azure SQL 
• MariaDB 10.3 upwards
• Microsoft SQL 2025, 2022, and 2019 
• MongoDB 6.3 upwards  
• MySQL 8.0 upwards
• Oracle Database 23, 21, and 19  
• PostgreSQL 16 upwards  
• SAP HANA 2.0 SPS 04 upwards  

Some of the database types have specific requirements for being able to poll performance metrics. Prerequisite instructions for each database type follow, along with a section that contains a matrix of the metrics provided by each monitoring type.

Prerequisite Details for Database Monitoring:

Azure SQL

To monitor the database, configure a user so that the database can connect to and from an external host with privileges, allowing the user to query performance metrics.

1. Connect to the master database on the Azure SQL instance.

2. Run the following commands, supplying the username and password to create a new user with privileges for monitoring.

   CREATE LOGIN username WITH PASSWORD = 'password'; 
   CREATE USER username FOR LOGIN username; 
   ALTER SERVER ROLE ##MS_DefinitionReader## ADD MEMBER username; 
   ALTER SERVER ROLE ##MS_ServerStateReader## ADD MEMBER username;

3. To optionally monitor database jobs, connect to the database containing the Elastic Jobs catalog, and run the following commands.

   CREATE USER username FOR LOGIN username;
   GRANT SELECT ON SCHEMA::jobs TO username;

MySQL/MariaDB:

To monitor the database, a user needs to be configured so that the database can be connected to from an external host with privileges allowing the user to query performance metrics.

1. Navigate to the directory containing the mysql.exe executable.
   The mysql.exe executable is located in the entuity_home/database/bin directory on Entuity servers.

2. Run the following command to connect to the database through the command-line utility.

   ./mysql -u [username|root] -P 3306 -h 127.0.0.1

3. At the command line, run the following commands as a root user. 
   This creates a new user account, allowing connections from an external host, and grant additional privileges to the user..

   CREATE USER username@'%' IDENTIFIED BY 'password';
   GRANT SELECT, PROCESS ON * . * TO username@'%';
   GRANT SELECT, EXECUTE ON sys.* TO username@'%';
   GRANT SELECT ON sys.schema_table_statistics_with_buffer TO username@'%';

4. Run the following command to add the changes you just made.

   FLUSH PRIVILEGES;

To Enable the Performance Schema on a Database:

By default, the performance schema is enabled on most MySQL and MariaDB databases, but this can be disabled, which is done on the Entuity MariaDB database. Database monitoring will work without the performance schema enabled; however, certain performance metrics cannot be polled without it. Follow the steps below to enable the performance schema.  

1. Navigate to the directory containing the my.cnf configuration file, and edit the file, as shown in the following example.
   Note, for Entuity servers, the file is named my_eye.cnf and is located in the entuity_home/install/template/etc directory.

   performance_schema=1

2. Restart the database for the changes to take effect.
   Note, for Entuity servers, you must run configure for the changes to take effect..
3. If the performance metrics are still not all being gathered, you might need to run ‘mysql-upgrade.exe’ on the database.
   The mysql-upgrade.exe file is located within the entuity_home/database/bin directory on Entuity servers.

Microsoft SQL:

To monitor the database, configure a user so that the database can connect to and from an external host with privileges, allowing the user to query performance metrics.

1. Connect to the Microsoft SQL server using SQL Server Management Studio (SSMS).
2. In Object Explorer, right-click the MS SQL server instance that you want to monitor, and then select properties.
3. Open the Security tab, set "Server authentication" to "SQL Server and Windows Authentication mode", and then click OK to save.
4. Open the Connections tab, ensure "Allow remote connections to this server" is enabled, and then click OK to close the window.
5. In the Object Explorer pane, expand the Security folder, and then right-click the Logins folder.
6. Select the New Login... option, and then click SQL authentication.
7. Provide a username in the Login name field, create a password, and reenter the password in the Confirm password field.
8. Clear the User must change password at next login checkbox, select master as the Default database, and select English from the default language drop-down menu, if it differs from the default.
9. Navigate to the Server Roles tab, and select the following:
   • ##MS_DatabaseConnector##
   • ##MS_DefinitionReader##
   • ##MS_PerformanceDefinitionReader##
   • ##MS_ServerPerformanceStateReader##
   • ##MS_ServerSecurityStateReader##
   • ##MS_ServerStateReader##
   • public
10. Navigate to the User Mapping tab, select msdb, and select the following options under Database role membership:
    1. SQLAgentReaderRole
    2. db_datareader
11. Click OK to save.
12. Open SQL Server Configuration Manager, expand the SQL Server Network Configuration folder on the left, and select the Protocols for MSSQLSERVER option.
13. Right-click TCP/IP, and select properties.
14. Ensure Enabled is set to Yes on the Protocol tab, and the IP Addresses tab displays the correct TCP port (the default is 1433).
15. Open Windows Defender Firewall, and create an inbound rule to allow TCP traffic on the TCP port that was listed in the TCP/IP properties of SQL Server Configuration Manager.
16. Complete the configuration by restarting the MS SQL Server service for the changes to take effect.

To configure Kerberos authentication:

Kerberos authentication can optionally be used for the monitoring of Microsoft SQL Server Databases. Kerberos authentication configuration settings in Entuity applies to both database monitoring and WinRM connections which requires configuration edits on the Entuity server.

The host and Entuity server must be able to resolve the domain controller. To test and verify this, use the following command: 

nltest /dsgetdc: <Domain>

1. On the Active Directory (AD) server, run the following commands to create a Service Principal Name (SPN) for the domain account that will be used for database monitoring. 

   setspn -A MSSQLSvc/<FQDN_of_MS_SQL_Server>:1433 <Domain>\<username>
   setspn -A HTTP/<FQDN_of_MS_SQL_Server>:1433 <Domain>\<username>
   setspn -A HTTPS/<FQDN_of_MS_SQL_Server>:1433 <Domain>\<username>
   

2. In the entuity.cfg file, located in entuity_home/etc, add the following section supplying the realm and kdc for your domain.

   • Note, realm is the domain (AD Server) in all capitals, and kdc is the domain as is (either in all lower-case letters or mixed).

   [winrm]
   realm=ENTUITY.COM
   kdc=Entuity.com

3. Edit the kerberosAssetStore.txt file, located in entuity_home/etc/CollectionEngine, by adding the IP addresses of the Microsoft SQL Server(s).
4. Save and close both the entuity.cfg and kerberosAssetStore.txt files.
5. Run configure followed by restarting the Entuity server.

MongoDB:

To monitor the database, configure a user so that the database can connect to and from an external host with privileges, allowing the user to query the database.

1. Add the following required JAR files to the entuity_home/lib/collectionEngine directory: 
   • mongodb-bson.jar
   • mongodb-driver-core.jar
   • mongodb-java-driver.jar
2. Navigate to the entuity_home\etc directory and open the startup_[UNIX|WIN32].cfg file.

3. In the [collectionEngine] section of the file, after the line starting with '-cp', add the following:

     =${EPS}${ENTUITY_HOME}${FPS}lib${FPS}collectionEngine${FPS}mongodb-bson.jar
     =${EPS}${ENTUITY_HOME}${FPS}lib${FPS}collectionEngine${FPS}mongodb-driver-core.jar
     =${EPS}${ENTUITY_HOME}${FPS}lib${FPS}collectionEngine${FPS}mongodb-java-driver.jar

4. Save and close the startup_[UNIX|WIN32].cfg file.
5. Run configure, followed by restarting the Entuity server.
6. Connect to the MongoDB database server and bind the IP address of the server in the mongod.conf (net.bindIp) configuration file.

7. Within the MongoDB shell, run the following command, supplying the role name to create a role for the database user that will be used for monitoring. 
   Note, dbAdmin role can be used instead of creating a custom role.

   db.createRole({
       role: "role name",
       privileges: [
           { resource: { db: "", collection: "" }, actions: ["listCollections"] },
           { resource: { db: "admin", collection: "system.version" }, actions: ["find", "collStats"] },
           { resource: { db: "admin", collection: "system.users" }, actions: ["find", "collStats"] },
           { resource: { db: "admin", collection: "system.roles" }, actions: ["find", "collStats"] },
           { resource: { db: "", collection: "system.profile" }, actions: ["find", "collStats"] }
       ],
       roles: [
           { role: "clusterMonitor", db: "admin" }
       ]
   })

8. Run the following command, supplying the username and password to create a new user for monitoring that uses the role created in the previous step.

   db.createUser({
       user: "username",
       pwd: "password",
       roles: [ "role name" ],
       mechanisms: [ "SCRAM-SHA-256" ]
   })
   

Oracle:

To monitor the database, a user needs to be configured so that the database can be connected to from an external host with privileges allowing the user to query the database.

1. Connect to the Oracle database with an account that has the CREATE USER system privilege.

2. Run the command below supplying the username and password to create a new user for monitoring.

   Note, if a common user is created the “C##” is required as a prefix before the username.

   CREATE USER username IDENTIFIED BY password;

3. To grant permissions to the new user account, execute one of the following:

   • To grant SELECT access (read-only) to everything, execute the SQL statement below.

     GRANT CREATE SESSION, SELECT ANY DICTIONARY, SELECT ANY TABLE, UNDER ANY VIEW, UNLIMITED TABLESPACE to username;

   • To grant minimal SELECT permissions, execute the statements below.

     GRANT CREATE SESSION to username;
     GRANT SELECT on sys.dba_data_files to username;
     GRANT SELECT on sys.dba_temp_files to username;
     GRANT SELECT on sys.dba_tablespaces to username;
     GRANT SELECT on sys.dba_free_space to username;
     GRANT SELECT on sys.dba_indexes to username;
     GRANT SELECT on sys.v_$datafile to username;
     GRANT SELECT on sys.v_$instance to username;
     GRANT SELECT on sys.v_$sysstat to username;
     GRANT SELECT on sys.gv_$session to username;
     GRANT SELECT on sys.v_$resource_limit to username;
     GRANT SELECT on sys.v_$parameter to username;
     GRANT SELECT on sys.v_$log to username;
     GRANT SELECT on sys.v_$filestat to username;
     GRANT SELECT on sys.dba_scheduler_jobs to username;
     GRANT SELECT on sys.dba_schedular_running_jobs to username;
     GRANT SELECT on sys.v$version to username;
     GRANT SELECT on sys.v$sql to username;
     GRANT SELECT on sys.v$sga to username;
     GRANT SELECT on sys.v$process to username;
     GRANT SELECT on sys.v$osstat to username;
     GRANT SELECT on sys.v$temp_space_header to username;
     

PostgreSQL:

To monitor the database, configure a user needs so that the database can connect to and from an external host with privileges, allowing the user to query the database.

1. Add the postgresql-jdbc-driver.jarrequired JAR file to the entuity_home/lib/collectionEngine directory. 
2. Navigate to the entuity_home\etc directory and open the startup_[UNIX|WIN32].cfg file.

3. In the [collectionEngine] section of the file, after the line starting with '-cp', add the following:

     =${EPS}${ENTUITY_HOME}${FPS}lib${FPS}collectionEngine${FPS}postgresql-jdbc-driver.jar

4. Save and close the startup_[UNIX|WIN32].cfg file.
5. Run configure followed by restarting the Entuity server.

6. To allow the database to listen for all IP addresses, or for the IP address of the Entuity server, modify the postgresql.conf configuration file. The following example allows the database to listen for all IP addresses: 

   listen_addresses = '*'

7. Add an entry in the pg_hba.conf file to configure client authentication. For example, this entry allows a host connection to any database, using any user from any IP address, and using md5 for authentication:

   host    all             username       0.0.0.0/0               scram-sha-256

8. Create a user by connecting to the psql command-line interface, and then running the following command.

   CREATE USER username WITH PASSWORD 'password';
   

9. Grant the monitoring role to the newly created user.

   GRANT pg_monitor TO username;

10. Create and enable the pg_stat_statements extension to monitor query metrics across the database and schemas:

    1.  Navigate to the postgresql.conf configuration file, and add the following line to the file.

       shared_preload_libraries = 'pg_stat_statements'

    2. If the extension doesn't exist, create the extension by running the following command through the psql command-line interface.

       CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

    3. Optionally add these additional lines to further fine-tune the aggregated query metrics that are collected:
       • pg_stat_statements.track = ALL (includes statements from functions and stored procedures in aggregating query metrics)
       • pg_stat_statements.max = 10000 (increases the number of normalized queries used for aggregated query metrics)
       • pg_stat_statements.track_utility = off (disables prepare and explain commands from being tracked for aggregated query metrics)
    4. Restart the database.

11. Create and enable the pg_buffercache extension to monitor schema buffer metrics by connecting to the psql command-line interface and run the following command.

    CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
    

12. To optionally monitor database jobs, install and/or enable the pgagent extension.

SAP HANA:

To monitor the database, a user needs to be configured so that the database can be connected to from an external host with privileges allowing the user to query the database.

1. Add the saphana-jdbc-driver.jar required JAR files to the entuity_home/lib/collectionEngine directory. 
2. Navigate to the entuity_home\etc directory and open the startup_[UNIX|WIN32].cfg file.

3. In the [collectionEngine] section of the file, after the line starting with '-cp', add the following:

     =${EPS}${ENTUITY_HOME}${FPS}lib${FPS}collectionEngine${FPS}saphana-jdbc-driver.jar

4. Save and close the startup_[UNIX|WIN32].cfg file.
5. Run configure followed by restarting the Entuity server.

6. Create a user by connecting to the hdbsql command-line interface, and then running the following command.

   • Note, you can optionally add 'NO FORCE_FIRST_PASSWORD_CHANGE' after the password to avoid needing to log in as the user and set a new password.

   CREATE USER username PASSWORD "password";

7. Grant the monitoring role to the newly created user.

   GRANT MONITORING TO username;

8. To optionally disable password expiration, run the following command.

   ALTER USER username DISABLE PASSWORD LIFETIME;

Matrix of Metrics per Monitoring Type:

The following table lists the metrics available for each type of monitoring.