1.23.4 Configuring SQL exporter

(a) Setting up JP1/IM - Manager

If JP1/IM - Manager 13-11 or later is newly installed, it does not need to be executed.

If you upgraded from a version earlier than JP1/IM - Manager 13-11, perform the following steps:

(b) Obtaining setup archive files

Retrieve the setup archive file to use SQL exporter. The setup archive file is located in the folder where JP1/IM - Agent is installed.

The file names for the setup archive files are as follows:

JP1/IM-Agent-installation-destination/jp1ima/options/

• sql_exporter_windows_VVRRSS.zip

(c) Setting up JP1/IM - Agent

This step is not required if you are installing JP1/IM - Agent 13-11 or later.

If you upgraded from a version earlier than JP1/IM - Agent 13-11, perform the following steps:

Note that you do not need to install Microsoft SQL Server modules on JP1/IM - Agent hosts.

(Omitted)
   :
scrape_configs:
   :
  - job_name: 'jpc_sql'
    scrape_interval: 60s
    scrape_timeout: 30s
 
    metrics_path: /metrics
 
    file_sd_configs:
      - files:
        - 'jpc_file_sd_config_mssql.yml'
 
    relabel_configs:
      - target_label: jp1_pc_nodelabel
        replacement: MSSQL metric collector(SQL exporter)
 
    metric_relabel_configs:
      - source_labels: ['__name__']
        regex: 'mssql_local_time_seconds|mssql_connections|mssql_deadlocks|mssql_user_errors|mssql_kill_connection_errors|mssql_page_life_expectancy_seconds|mssql_batch_requests|mssql_log_growths|mssql_buffer_cache_hit_ratio|mssql_checkpoint_pages_sec|mssql_io_stall_seconds|mssql_io_stall_total_seconds|mssql_resident_memory_bytes|mssql_virtual_memory_bytes|mssql_memory_utilization_percentage|mssql_page_fault_count|mssql_os_memory|mssql_os_page_file|mssql_database_detail_process_count|mssql_global_server_summary_perc_busy|mssql_global_server_summary_packet_errors|mssql_server_detail_blocked_processes|mssql_server_overview_cache_hit|mssql_transaction_log_overview_log_space_used'
        action: 'keep'
      - source_labels: [exported_job]
        target_label: instance
        replacement: ${1}
      - source_labels: [target] 
        target_label: jp1_pc_nodelabel
        replacement: ${1}

promtool check config jpc_prometheus_server.yml

WARNING: file "../conf/jpc_file_sd_config_windows.yml" for file_sd in scrape job "jpc_windows" does not exist

(a) Preparing for Installation

1. Confirm the setting of JP1IMADIR.

   You must set JP1IMADIR in service definition file for SQL exporter. Depending on the type of host in the JP1/IM - Agent, the following values will occur.

   Host type	Environment variable JP1IMADIR
   For physical host operation	JP1/IM-Agent-installation-folder\jp1ima
   For logical host operation	shared-folder\jp1ima

2. Confirm whether to operate on a physical host or a logical host.

   SQL exporter is also a physical host operation if scrape of the newly added JP1/IM - Agent is a physical host operation.

   SQL exporter is also logical host operation if scrape of the newly added JP1/IM - Agent is logical host operation.

(b) Installing SQL exporter

1. Place SQL exporter.

   Extract the setup archive file^# obtained in 1.23.4(1)(b) Obtaining setup archive files to a folder of your choice. Do not specify the folder where JP1/IM - Manager or JP1/IM - Agent will be installed in the destination folder.

   #

   sql_exporter_windows_VVRRSS.zip

   To operate SQL exporter on a logical host, place it in shared-folder.

2. Create a defaulted metric definition-file.

   Copies the model file of the following default metric definition file and renames it to the definition file name of the copy destination.

   Source filename: SQL exporter location\sql_exporter_windows\jp1ima\conf\mssql_standard.collector.yml.model

   Destination filename: SQL exporter location\sql_exporter_windows\jp1ima\conf\mssql_standard.collector.yml

3. Updating service definition file.

   Verify that the following files exist:

   SQL exporter location\sql_exporter_windows\jp1ima\bin\sql_exporter_service.exe

   SQL exporter location\sql_exporter_windows\jp1ima\bin\sql_exporter_service.xml.model

   Modify the following service definition file settings:

   Value to be changed	Value to specify
   @@sql_exporter_installdir@@	Replace with SQL exporter location.
   @@autostart@@	To start automatically at OS startup Replace with "Automatic". To not start automatically Replace with "Manual". For logical hosts, substitute "Manual".

4. Register the service.

   Run the following command to register SQL exporter service with Windows:

   • For physical host operation

     sql_exporter_service.exe install

   • For logical host operation

     sql_exporter_logical-host-name_service.exe install

     For logical host operation, execute the command on both nodes that make up the cluster.

5. Set the monitoring target in SQL exporter configuration file.

   The target of SQL exporter is listed in SQL exporter configuration file (jpc_sql_exporter.yml).

   For details about SQL exporter configuration file, see SQL exporter configuration file (jpc_sql_exporter) in Chapter 2. Definition Files in the JP1/Integrated Management 3 - Manager Command, Definition File and API Reference.

6. Add a target.

   For details on adding a target, see 1.23.4(4)(a) Adding monitoring targets (required).

7. Register the service with the cluster software.

   For a cluster configuration, register the service with the cluster software.

(c) Configuring Prometheus

1. Configure monitoring targets for SQL exporter discovery configuration file.

   SQL exporter targets are listed in SQL exporter Discovery configuration file (jpc_file_sd_config_mssql) targets.

   For details about SQL exporter discovery configuration file, see SQL exporter discovery configuration file (jpc_file_sd_config_mssql) in Chapter 2. Definition Files in the JP1/Integrated Management 3 - Manager Command, Definition File and API Reference.

2. Checking the settings with promtool command.

   Verify that the settings are correct.

   promtool command is located in JP1/IM-Agent-installation-folder\jp1ima\tools folder.

   promtool check config jpc_prometheus_server.yml

   If an error is displayed, review the error.

   Note that there is no problem even if the following warning is displayed.

     WARNING: file "../conf/jpc_file_sd_config_windows.yml" for file_sd in scrape job "jpc_windows" does not exist

3. Restart Prometheus.

   Restart Prometheus for the changes to take effect.

(a) Adding monitoring targets (required)

(b) Change password for connection (optional)

If you change the password for Microsoft SQL Server during operation, you must refresh the password that SQL exporter uses to connect.

1. Shut down SQL exporter.

2. Update the password.

   Use jimasecret command to refresh the password of the user used to connect to Microsoft SQL Server.

   • For physical host operation

   jimasecret-add-key MSSQL.hostname.instance-name.username -s password

   • For logical host operation

   jimasecret-add-key MSSQL.hostname.instance-name.username -s password -l shared-folder

   For details about jimasecret command, see jimasecret in Chapter 1. Commands in the JP1/Integrated Management 3 - Manager Command, Definition File and API Reference.

3. Start SQL exporter.

(c) Delete a monitored target (optional)

(d) Configure SQL exporter alive monitoring (optional)

You can monitor SQL exporter service-alive monitoring with up metric on Prometheus server. The following is an example of how to monitor SQL exporter in the alert-definition.

groups:
  - name: exporter_healthcheck
    rules:
    - alert: jp1_pc_exporter_healthcheck
      expr: up{jp1_pc_remote_monitor_instance=""} == 0 or label_replace(up{jp1_pc_exporter="JPC SQL exporter"}, "jp1_pc_nodelabel", "${1}", "jp1_pc_remote_monitor_instance", "^[^:]*:([^:]*)$") == 0 or label_replace(up{jp1_pc_remote_monitor_instance!="", jp1_pc_exporter!="JPC SQL exporter"}, "jp1_pc_nodelabel", "${1}", "jp1_pc_remote_monitor_instance", "^[^:]*:([^:]*)$") == 0
      for: 3m
      labels:
        jp1_pc_product_name: "/HITACHI/JP1/JPCCS2"
        jp1_pc_component: "/HITACHI/JP1/JPCCS/CONFINFO"
        jp1_pc_severity: "Error"
        jp1_pc_metricname: "up"
      annotations:
        jp1_pc_firing_description: "Communication to Exporter failed. instance={{ $labels.instance }}"
        jp1_pc_resolved_description: "Communication to Exporter successful. instance={{ $labels.instance }}"

(e) Modifying Users for Connections (Optional)

1. Add a new person to Microsoft SQL Server.

   Perform this procedure only if a new monitoring user is required and not registered.

2. Shut down SQL exporter.

3. Delete the password of the user before the change.

   Use jimasecret command to remove the password of the user who was connecting to Microsoft SQL Server.

   • For physical host operation

   jimasecret-rm-key MSSQL.hostname.instance-name.old-username

   • For logical host operation

   jimasecret-rm-key MSSQL.hostname.instance-name.old-username -l shared-folder

   For details about jimasecret command, see jimasecret in Chapter 1. Commands in the JP1/Integrated Management 3 - Manager Command, Definition File and API Reference.

4. Register the password of the user after change.

   Use jimasecret command. Register the password of the user used to connect to Microsoft SQL Server.

   • For physical host operation

   jimasecret-add-key MSSQL.hostname.instance-name.new-username -s password

   • For logical host operation

   jimasecret-add-key MSSQL.hostname.instance-name.new-username -s password -l shared-folder

   For details about jimasecret command, see jimasecret in Chapter 1. Commands in the JP1/Integrated Management 3 - Manager Command, Definition File and API Reference.

5. Change the username of the connection-information described in SQL exporter configuration file.

   Removes the username for SQL exporter connectivity from SQL exporter configuration file (jpc_sql_exporter).

   For details about SQL exporter configuration file, see SQL exporter configuration file (jpc_sql_exporter) in Chapter 2. Definition Files in the JP1/Integrated Management 3 - Manager Command, Definition File and API Reference.

6. Start SQL exporter.

7. Deletes the previous person from Microsoft SQL Server.

   If the old user is not needed, remove the old user from Microsoft SQL Server.