Installing and Configuring Agents

To start monitoring SAP HANA with Foglight, perform the following installation instructions in the given order:

• Configuring the Environment
  • Database User
  • Resource Tracking
• Creating Agents
• Setting Agent Properties
  • System Database Connection
  • Landscape
  • Collection Periods
  • SQL Options
  • Table Options

Configuring the Environment

Database User

To configure the monitoring of an SAP HANA system, an identical user must be created on the SYSTEMDB and on each tenant database for which monitoring is desired. The user requires specific privileges, given below. The username and password must be identical on all databases, both the SYSTEMDB and all tenant databases.

Create an ordinary database user on each database in the SAP HANA system as follows:

CREATE USER <username> PASSWORD <password> NO FORCE_FIRST_PASSWORD_CHANGE;

GRANT CATALOG READ TO <username>;

GRANT SELECT ON SCHEMA _SYS_STATISTICS TO <username>;

Resource Tracking

Some features of the Foglight for SAP Hana depend on resource tracking within SAP HANA. These include SQL Query Plan memory monitoring and Expensive Statement tracking. The following steps are necessary to enable this functionality.

Set the global [resource_tracking] properties ’enable_tracking’ and ‘memory_tracking’ to ‘on’.

CPU consumption estimation is on by default (for all SAP HANA versions greater than SAP HANA 1.0 SPS 11). Check that the global [resource_tracking] property ‘cpu_time_measurement_mode’ is still set to ‘on’.

To enable expensive statement tracking, set the global [expensive_statement] property ’enable’ to ’true’.

The following SQL commands show how these settings can be changed globally:

ALTER SYSTEM ALTER CONFIGURATION ('global.ini', 'SYSTEM') SET ('resource_tracking', 'enable_tracking') = 'on';

ALTER SYSTEM ALTER CONFIGURATION ('global.ini', 'SYSTEM') SET ('resource_tracking', 'memory_tracking') = 'on';

ALTER SYSTEM ALTER CONFIGURATION ('global.ini', 'SYSTEM') SET ('resource_tracking', 'cpu_time_measurement_mode') = 'on';

ALTER SYSTEM ALTER CONFIGURATION ('global.ini', 'SYSTEM') SET ('expensive_statement', 'enable') = 'true';

Optionally change the following parameters from their defaults to customize the thresholds that define which statements are considered expensive:

• ’threshold_duration’ in microseconds,
• ’threshold_memory’ in bytes (only effective if resource and memory tracking are also enabled),
• ’threshold_cpu_time’ in microseconds (only effective if resource and CPU time tracking are also enabled).

For SAP HANA express edition, execute the following to enable collection of SQL Plan statistics:

ALTER SYSTEM ALTER CONFIGURATION ('indexserver.ini','SYSTEM') SET('sql', 'plan_cache_statistics_enabled') = 'True';

Creating Agents

The Agent Status dashboard can be used to create new agents and configure and manage existing agents. To access the page from the navigation pane, select Administration > Agents > Agent Status.

To create a new agent instance:

1. Deploy the SAP HANA agent package to the FglAM hosting the agent before creating an agent. You can click Deploy Agent Package on the Agent Status or Agent Managers page to perform this.
2. Click Create Agent and follow the instructions on the wizard:
   a. Host Selector - Select the Agent Manager on which the agent should run. Considerations for this may include physical or virtual locality to the monitored instance, allocated resources, or grouping with other agents of the same type or monitored environment.
   b. Agent Type and Instance Name – Select the SAPHANAAgent type. Then, select the Specify Name radio button and enter the agent instance name to be created in the Name field. This is representative of the database instance that the agent will monitor, rather than canonical.
   c. Summary – Click Finish.
3. Once the agent gets created, select the checkbox against its name.
4. Click Edit Properties.
5. Select Modify the default properties for this agent.
6. Edit the agent properties.
7. Click Activate.

To modify the properties of an existing agent, skip to step 3, deactivate the agent, update the configuration, and then reactivate it.

Setting Agent Properties

Below is a list of the configurable properties of the Foglight for SAP HANA agent and an explanation of each property.

System Database Connection

The agent requires a connection to the SYSTEMDB database to collect system properties and metrics.

• Host Address - Hostname or IP on which the SAP HANA system node is running.
• Port - The SQL port for the system database.
• Username - The system database username.
• Password - The system database user’s password.
• Use Compression? - Whether the database connection should be compressed.

Note that Foglight for SAP HANA currently supports database connections using only username and password authentication. The SAP HANA Cartridge does not support certificate-based database connections at this time.

If the SAP HANA instance is set up to allow connections via certificate-based authentication, ensure that the sslEnforce parameter in the communication section of the global.ini configuration file is set to false, as it is by default. This enables clients utilizing username/password authentication to continue to connect.

Landscape

An arbitrarily chosen name for the monitored system. It is used exclusively to differentiate two or more SAP HANA Systems with the same SID. The default value may be used when there is only one system with the given SID, and only one agent monitoring the system.

Collection Periods

The collection period fields in the agent properties set the sample frequencies and are in seconds. Any collection can be turned off by setting its period to zero. The default collection periods vary based on the type and volatility of data collected.

• Availability - Controls the query interval for the SAP HANA system and database availability collections.
• Databases and Tables - Controls the query interval for collection of data pertaining to databases present on the system and table memory and persistence metrics.
• Services, Volumes, and Load - Controls the query interval for collection of the service, volume, and host load metrics.
• Query Plans - Controls the query interval for collection of the SQL query plans and expensive statements. Note that resource tracking must be enabled in the SAP HANA system for expensive statement data to be available.
• Transactions - Controls the query interval for blocked transaction monitoring.
• Alerts - Controls the query interval for native alert collection.
• Configuration - Controls the query interval for system configuration tracking.

SQL Options

• Max SQL Plans Per Period - The maximum number of SQL plans to retrieve per collection period.
• Select Top SQL By - The metric used to determine which SQL plans are collected in the usual case when the SQL plan cache contains more plans than can be collected based on “Max SQL Plans Per Period”.
• Max Expensive Statements Per Period - The maximum number of expensive statements to retrieve per collection period. Expensive Statement tracking is only possible if resource tracking has been configured in SAP HANA.
• Expensive Statement Threshold Duration - The minimum duration of an expensive statement to qualify for retrieval. Note that only “Max Expensive Statements Per Period” expensive statements will be retrieved per period, regardless. Hence it is possible that additional expensive statements above this duration exist but are not collected by Foglight.

Table Options

• Max Top Tables Per Database - The maximum number of tables collected per period per database.
• Min Table Disk Size (KiB) - The minimum table disk size for its persistence metrics to be collected.
• Min Table Memory Size (KiB) - The minimum table memory size for its memory metrics to be collected.