Guidance for Monitoring Databases

Introduction

Prerequisite Details for Database Monitoring

MySQL/MariaDB

Microsoft SQL

Oracle

Matrix of Metrics per Monitoring Type

Introduction:

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

• MariaDB
• Microsoft SQL
• MySQL
• Oracle

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

Prerequisite Details for Database Monitoring:

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
   • This is located within the [Entuity Home]/database/bin directory on Entuity servers.

2. Run the command below to connect to the database through the command line utility.

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

3. If you are using a non-root account, run the commands below as a root user. This will create a new user account and allow external hosts to connect to the database 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 below command FLUSH PRIVILEGES; to add the changes.

   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 and edit the file to match below.  

   • Note, for Entuity servers the file is my_eye.cnf located in the [Entuity Home]/install/template/etc directory.

     performance_schema=1

2. After the file is edited, the changes should take effect after restarting the database.
   • Note, for Entuity servers, configure must be run for the changes to take effect.
3. If the performance metrics are still not all being gathered after doing the above instructions, then you may need to run ‘mysql-upgrade.exe’ on the database.
   • This is located within the [Entuity Home]/database/bin directory on Entuity servers.

Microsoft SQL:

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. Connect to the Microsoft SQL server using SQL Server Management Studio (SSMS).
2. In Object Explorer, right click on the MS SQL server instance that is to be monitored and select properties.
3. Open the Security tab and set "Server authentication" to "SQL Server and Windows Authentication mode" followed by clicking OK to save.
4. Open the Connections tab and ensure "Allow remote connections to this server" is enabled then click OK to close the window.
5. In the Object Explorer pane, expand the Security folder then right-click on the Logins folder.
6. Select the New Login... option then click on the SQL authentication option.
7. Provide a username in the Login name field, create a password and reenter it into the Confirm password field.
8. Uncheck the User must change password at next login option, 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, then select the following:
   • ##MS_DatabaseConnector##
   • ##MS_DefinitionReader##
   • ##MS_PerformanceDefinitionReader##
   • ##MS_ServerPerformanceStateReader##
   • ##MS_ServerSecurityStateReader##
   • ##MS_ServerStateReader##
   • public
10. Navigate to the User Mapping tab, check msdb, and check the following options under Database role membership.
    • SQLAgentReaderRole
    • db_datareader
11. After configuring the user, 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 on 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 configuration by restarting the MS SQL Server service so the changes take effect.

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;
     

Matrix of Metrics per Monitoring Type:

Please find below the metrics available for each type of monitoring: