Hitachi

JP1 Version 12 JP1/Performance Management - Remote Monitor for Microsoft(R) SQL Server Description, User's Guide and Reference


2.1.4 Setting up PFM - RM for Microsoft SQL Server

This section explains the setup that is required before you can use PFM - RM for Microsoft SQL Server.

[Figure] indicates an item that might be required depending on your operating environment, or an optional item that you can set if you do not wish to use the default.

Organization of this subsection

(1) Registering PFM - RM for Microsoft SQL Server[Figure]

To perform integrated management of PFM - RM for Microsoft SQL Server by using PFM - Manager and PFM - Web Console, you must register PFM - RM for Microsoft SQL Server in PFM - Manager and PFM - Web Console.

If PFM - RM for Microsoft SQL Server is already registered in PFM - Manager and PFM - Web Console, you do not have to follow the procedure described below. If PFM - RM for Microsoft SQL Server is not registered yet, manually register PFM - RM for Microsoft SQL Server according to the procedure.

You can determine whether manual registration of PFM - RM for Microsoft SQL Server is necessary by referring to the conditions described below.

Manually registering PFM - RM for Microsoft SQL Server in PFM - Manager

When all of the following conditions apply, manually register PFM - RM for Microsoft SQL Server in PFM - Manager:

  • The PFM - RM for Microsoft SQL Server to be installed is of a product version that is not specified in the Release Notes for PFM - Manager.

  • PFM - RM for Microsoft SQL Server is installed on a host other than PFM - Manager.

Manually registering PFM - RM for Microsoft SQL Server in PFM - Web Console

When the following condition applies, manually register PFM - RM for Microsoft SQL Server in PFM - Web Console:

  • The PFM - RM for Microsoft SQL Server to be installed is of a product version that is not specified in the Release Notes for PFM - Web Console.

If, however, the Release Notes for PFM - RM for Microsoft SQL Server state that it is necessary to execute the setup command, execute the setup command.

The following figure shows the procedure for registering PFM - RM for Microsoft SQL Server.

Figure 2‒4: Procedure for registering PFM - RM for Microsoft SQL Server

[Figure]

Important
  • Register PFM - RM for Microsoft SQL Server before setting up an instance environment.

  • You do not need to register a new instance of an already registered version of PFM - RM for Microsoft SQL Server when you add it to the Performance Management system.

  • When you have installed different versions of PFM - RM for Microsoft SQL Server on different hosts, set up the older version before the newer version.

  • When you install PFM - RM for Microsoft SQL Server on the same host as PFM - Manager, the jpcconf agent setup command is executed automatically, and the following message is output to the common message log: KAVE05908-I New agent setup (Pfm-Agent-service-key) ended successfully. (version=version). Check the result of command execution in the log file. If execution was unsuccessful, try it again. For details about how to execute commands, see the chapter on commands in the manual JP1/Performance Management Reference.

  • In the Microsoft SQL Server memory settings, do not set the minimum query memory size to less than 1,024 KB (the initial value). If the minimum query memory size is smaller than 1,024 KB, queries for Microsoft SQL Server fail and no data can be collected.

  • Registration of PFM - RM for Microsoft SQL Server creates the RM SQLServer folder in Reports window and Alarms window of PFM - Web Console. If the user has already created a folder or file named the RM SQLServer on the Reports window, rename the file or folder before registering PFM - RM for Microsoft SQL Server.

(a) Copy the PFM - RM for Microsoft SQL Server setup files

Copy the setup files from the host on which you installed PFM - RM for Microsoft SQL Server to the hosts on which PFM - Manager and PFM - Web Console are installed.

To copy the files:

  1. If PFM - Web Console is running, stop it before copying the files.

  2. Copy the PFM - RM for Microsoft SQL Server setup files, in binary mode.

    The following table shows the location of the setup files and where they should be copied.

    Table 2‒2: Setup files to be copied

    PFM - RM for Microsoft SQL Server setup file

    Destination

    PFM program name

    OS

    Destination folder

    installation-folder\setup\jpcagt4w.EXE

    PFM - Manager

    Windows

    installation-folder\setup\

    installation-folder\setup\jpcagt4u.Z

    UNIX

    /opt/jp1pc/setup/

    installation-folder\setup\jpcagt4w.EXE

    PFM - Web Console

    Windows

    installation-folder\setup\

    installation-folder\setup\jpcagt4u.Z

    UNIX

    /opt/jp1pcwebcon/setup/

(b) Execute the setup command on the PFM - Manager host

On the PFM - Manager host, execute the following command to set up PFM - RM for Microsoft SQL Server:

jpcconf agent setup -key RMSQL

Although an example of interactive command execution is shown here, the jpcconf agent setup command can be also executed non-interactively. For details about the jpcconf agent setup command, see the chapter that describes commands in the manual JP1/Performance Management Reference.

Important

If any Performance Management programs or services are still running on the local host when you execute the jpcconf agent setup command, an error might occur. If an error occurs, make sure that all Performance Management programs and services have completely stopped, and then execute the jpcconf agent setup command again.

You can then delete the PFM - RM for Microsoft SQL Server setup files remaining on the PFM - Manager host.

(c) Execute the setup command on the PFM - Web Console host

On the PFM - Web Console host, execute the following command to set up PFM - RM for Microsoft SQL Server:

jpcwagtsetup

You can then delete the PFM - RM for Microsoft SQL Server setup files remaining on the PFM - Web Console host.

(2) Setting up an instance environment

PFM - RM for Microsoft SQL Server requires configurations of the instance environment and the monitoring target. There is a one-to-one match between the configuration of the instance environment and the configurations of the monitoring target.Note that in PFM - RM for Microsoft SQL Server you can associate one instance environment with only one monitoring target. You can set up multiple instance environments and monitoring targets by repeating the procedure for each instance.

For example, if you monitor three instances of Microsoft SQL server, repeat these procedures three times.

When you create an environment where there are multiple instances, the number of instances depends on the system configuration. As a guide, use three to five instances for the number of instances. You can increase the number of instances by reducing the number of records to be collected or lengthening the collection interval. Consider this carefully before operation.

Notes on setting up an instance environment:

This section describes how to set up the instance environment and the monitoring target.

(a) Set up instance information

You must specify instance information for the Microsoft SQL Server that is to be monitored by the PFM - RM for Microsoft SQL Server. Specify instance information on the PFM - RM host.The following table lists the instance information items that are to be specified. You should check this information before you start the setup procedure.

Table 2‒3: Instance information of PFM - RM for Microsoft SQL Server

Item

Description

Specifiable value

Default

LOG_PATH#1

Specifies the full path name of the folder for storing agent log information.

If you specify a path containing space characters, do not enclose the path in quotation marks (").

A character string of 245 or fewer bytes that does not include the following characters:

  • Tabs

  • The following symbols:

    / , ; * ? " < > |

installation-folder\agt4\agent\instance-name\log

LOG_SIZE

Specifies the maximum size of one agent log file. #2

1 to 32 (megabytes).

The minimum recommended value is 16.

16

#1

If you want to change the output folder for agent logs from the default folder, make sure to choose a folder for which write permission is enabled.

You cannot delete or change any resource that is under a folder for which WRP resource protection is enabled. Therefore, do not specify the agent log output folder under a folder for which WRP is enabled.

#2

A maximum of 4 agent log files are collected for one instance. Before specifying the LOG_SIZE value, make sure that the value satisfies the following condition (this condition also applies when LOG_PATH is set to the default):

Amount of free space on the drive containing the folder specified in LOG_PATH (MB) > LOG_SIZE x 4

If the free disk space is insufficient, the agent log cannot be output. For details about the agent log, see 7.3 Log information.

Note:

If no instance environment has been set up, the service of PFM - RM for Microsoft SQL Server does not start.

To build an instance environment using the jpcconf inst setup command. The procedure for configuring an instance environment is described below.

This example shows execution in the interactive mode, but you can also execute the jpcconf inst setup command in the non-interactive mode. For details about this command, see the chapter that describes commands in the manual JP1/Performance Management Reference.

To configure an instance environment:

  1. Specify a service key and an instance name, and then execute the jpcconf inst setup command.

    Execute the command specified as follows:

    jpcconf inst setup -key RMSQL -inst instance-name
  2. Set up the instance information for PFM - RM for Microsoft SQL Server.

    Enter the values for the items listed in Table 2-3 as prompted by the command. All items are mandatory. To use the default value displayed for an item, only press the Enter key.

When all values have been entered, the instance environment is created. If you want to change the agent log output destination or the agent log file size, re-execute the jpcconf inst setup command to update the instance environment. For details about updating an instance environment, see 2.4.3 Updating an instance environment.

The instance environment that is built is described as follows:

  • The organization of the instance environment folder

    The following table lists and describes the folder configuration of the instance environment that is built.

    Table 2‒4: Folder configuration of the instance environment

    Installation folder

    File name

    Descriptions

    Installation-folder#1\agt4\agent\instance-name

    jpcagt.ini

    Remote Monitor Collector service startup initialization file

    jpcagt.ini.model#

    Model file for the Remote Monitor Collector service startup initialization file

    status.dat

    Relay file for internal processing

    tstatuses.dat

    Status information file for virtual agent

    targetlist.ini

    List file for monitoring target

    grouplist.ini

    List file for monitoring group

    GARULES.DAT

    List file of gathering rule descriptions

    targets

    Storage folder for remote agent

    groups

    Storage folder for group agent

    log

    Storage folder for log files

    Installation-folder#1\agt4\store\instance-name

    jpcsto.ini

    Remote Monitor Store service startup initialization file

    jpcsto.ini.model#2

    Model file for the Remote Monitor Store service startup initialization file

    *.DB

    Performance data file

    *.IDX

    Index file for performance data file

    *.LCK

    Lock file for performance data file

    status.dat

    Relay file for internal processing

    *.DAT

    Data model definition file

    dump

    Export destination folder

    import

    Standard database import destination folder

    backup

    Backup destination folder

    log

    Storage folder for log files

    partial

    Standard database partial backup destination folder

    #1

    For the default storage location when PFM - RM for Microsoft SQL Server runs on a logical host, replace installation-folder with environment-folder\jp1pc.

    #2

    This file is used to reset all values to the initial values set when the instance environment was created.

  • Service ID for the instance environment

    The service ID for the instance environment is shown as follows.

    • Remote Monitor Collector service:

      4Ainstance-number instance-name[host-name]

    • Remote Monitor Store service:

      4Sinstance-number instance-name[host-name]

    • Group Agent service:

      4Ainstance-number instance-name[All@host-name]

    In PFM - RM for Microsoft SQL Server, the instance name specified in the jpcconf inst setup command is displayed. For example, if you execute the command with host name host1 and instance name default, the service IDs will be as follows:

    • Remote Monitor Collector service:

      4A1 default[host1]

    • Remote Monitor Store service:

      4S1 default[host1]

    • Group Agent service:

      4A1 default[All@host1]

    For details about the service ID, see the naming rules described in Appendix in the JP1/Performance Management Planning and Configuration Guide.

  • Windows service name for the instance environment

    The Windows service name for the instance environment is described as follows:

    • Remote Monitor Collector service:

      PFM - RM for Microsoft(R) SQL Server instance-name[logical-host-name]

    • Remote Monitor Store Service:

      PFM - RM Store for Microsoft(R) SQL Server instance-name[logical-host-name]

    For example, when the logical host name is lhost and the instance name is default, the service name will be:

    • Remote Monitor Collector service:

      PFM - RM for Microsoft(R) SQL Server default[lhost]

    • Remote Monitor Store Service:

      PFM - RM Store for Microsoft(R) SQL Server default[lhost]

    For details about Windows service names, see the naming rules described in the Appendix in the JP1/Performance Management Planning and Configuration Guide.

    For details about the Windows service names for logical host operation, in the JP1/Performance Management User's Guide, see the chapters explaining setup and operation for cluster systems.

(b) Set the monitoring target

When you set the monitoring target, you associate the instance that you specify in (a) Set up instance information with the information about the monitoring target host. You associate the instance with the monitoring target on the PFM - RM host

You must specify the information shown in the following table. Before you set the monitoring target, check the information in advance.

Table 2‒5: Configuration for the monitoring target of PFM - RM for Microsoft SQL Server

Item

Description

Specifiable value

Default value

TARGET_HOST

Microsoft SQL Server host name for a monitoring target. If the Microsoft SQL Server host is a logical host, specify the logical host.

Host names can consist of 1 to 32 alphanumeric characters and hyphen. Note that you cannot specify a (logical) host name beginning with a hyphen.

Physical and logical host names must be unique within the system. #1

--

SQL_INSTANCE

Instance name for monitoring target.

A character string of 16 or fewer bytes that does not include the multi-byte characters.

Specifiable value varies depending on how you install Microsoft SQL Server:

If you install Microsoft SQL Server by default:

[default]

If you install Microsoft SQL Server and specify the instance name:

[the instance name you specified]

default

SQL_USER#2

Specifies the user name of Microsoft SQL Server used for Microsoft SQL Server authentication.

A character string of 128 or fewer bytes that does not include the multi-byte characters.

The user account that has permissions equivalent to sa (the account of a member who has the sysadmin fixed server role).

If you specify other user name, see (d) Login permissions of the Microsoft SQL Server.

sa

SQL_PASSWORD#2

Specifies the password of the Microsoft SQL Server user used for Microsoft SQL Server authentication.

The password for SQL_USER.

--

DRIVER_NAME#3

Specifies the driver name used for communication with Microsoft SQL Server.

Change the name when monitoring Microsoft SQL Server version 2016 or later and encrypting communication using the monitored Microsoft SQL Server settings.

Do not change the name from the default SQL Server (Windows standard driver) when monitoring Microsoft SQL Server 2014 or earlier, or when monitoring Microsoft SQL Server 2016 or later and communicating in plaintext.

{SQL Server | SQL Server Native Client 11.0 | ODBC Driver 17 for SQL Server}

SQL Server

TIMEOUT

Specifies the query timeout value for database access. The value is in seconds.

1 to 3,600 (seconds)

60

LOGIN_TIMEOUT

Specifies the access timeout value for database access. The value is in seconds.

1 to 3,600 (seconds)

20

DB_FREE_PERC_OPTION#4,#5

Specify how to display the values of the fields related to the percentage of free space in the PD_DS record (the Free % field and the Data Unallocate % field) when its value is negative.

{Y | N}

If Y is specified, the value specified for DB_FREE_PERC_NUMBER is displayed.

If N is specified, the value of the Free % field and the Data Unallocate % field is displayed as it is when its value is negative.

Y

DB_FREE_PERC_NUMBER#5,#6

Specify the value to replace the values of the fields related to the percentage of free space in the PD_DS record (the Free % field and the Data Unallocate % field) when its value is negative.

The replacement is enabled only when the value of DB_FREE_PERC_OPTION is Y.

-1 to 999

0

LIMIT_PD_LD_NUMBER#7

Specify the maximum number of collection records for the PD_LD record.

0 to 900,000

If you specify 0, no upper limit is set, and all data is collected.

1000

Legend:

--: None

#1

You cannot specify "ALL" because "ALL" is a reserved word for group agent.

#2

Authentication method varies according to the value of SQL_USER you specified. The following table shows the variation on the authentication method.For details about how to install Microsoft SQL Server, instance information, and user authentication, see the Microsoft SQL Server documentation.

Table 2‒6: Authentication methods for each user name specified in SQL_USER field

The value of SQL_USER

The value of SQL_PASSWORD

Authentication method

sa

The password for the user sa.

SQL Server Authentication

None (when no password is set).

Any alphabetic string

Password for the specified user.

Blank (when no password is set).

A space character

Unnecessary (ignored if specified).

Windows Authentication

Note that when the method is Windows Authentication, PFM - RM for Microsoft SQL Server uses its service account to obtain authentication. For details about using windows authentication, see 2.1.4(2)(c) Using Windows authentication.

When you use Windows Authentication to monitor Microsoft SQL Server 2012 or later, the server role sysadmin is not added to the local system account. Accordingly, add sysadmin or prepare another account.

#3

DRIVER_NAME specifies the driver name used for communication with Microsoft SQL Server. Change the driver name when monitoring Microsoft SQL Server 2016 or later and encrypting communication using the monitored Microsoft SQL Server settings. Do not change it from the default SQL Server when monitoring Microsoft SQL Server 2014 or earlier or when communicating in plaintext. The following table shows the different operations for each value.

Table 2‒7: Values and operations of each DRIVER_NAME

Values

Operations

SQL Server (default)

Uses the SQL Server that is the Windows standard driver to communicate. Changing this is unnecessary when communicating in plaintext.

SQL Server Native Client 11.0

Uses SQL Server Native Client 11 to communicate. Specify this value when using encrypted communication methods such as TLS 1.2.

ODBC Driver 17 for SQL Server

Uses Microsoft ODBC Driver 17 for SQL Server to communicate. Specify this value when using encrypted communication methods such as TLS 1.2.

When communicating under encryption with the monitored Microsoft SQL Server, install the driver to be used as a prerequisite product and specify the installed driver name.

Check the driver in the following window:

Furthermore, encryption of the communication between Microsoft SQL Server and the client (including PFM - RM for Microsoft SQL Server) is set using Microsoft SQL Server. For details about setup, see your Microsoft SQL Server documentation.

If the specified DRIVER_NAME is incorrect, connection to the monitored Microsoft SQL Server will fail. When this occurs, the KAVL19400-W message is output to the common message log. If this message is issued in an environment where DRIVER_NAME is specified, check whether the following message is output to the agent log error file.

KAVL19810-E An attempt to connect to SQL Server failed. (rc = IM002 Description = [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified)

If it is output, check the DRIVER_NAME value.

#4

Microsoft SQL Server sometimes defers allocating data, possibly resulting in the update of performance data not to take effect immediately. For this reason, the Free % field and the Data Unallocate % field in the PD_DS record may have a negative value. The item specifies whether this value is replaced or not.

#5

If the value of the Free % field is replaced when its value is negative, the "KAVL19847-I" message is output to the agent log, whereas if the value of the Data Unallocate % field is replaced, the "KAVL19857-I" message is output to the agent log.

#6

If you set the Free % field and the Data Unallocate % field as a measured parameter of the alarm condition, specify the value depending on the operation environment.

  • If you want to give the alarm:

    The value you specify in this field must be above the threshold of the abnormal and warning condition.

  • If you do not want to give the alarm:

    The value you specify in the field must be below the threshold of the abnormal and warning condition.

#7

If you collect the Lock Details (PD_LD) record when a large number of transactions or access locks have occurred in the Microsoft SQL Server, the following events might occur.

  • The space in the Store database increases.

  • Collection takes time, and until the collection has been completed, the collection of other records is skipped.

  • A large amount of memory is consumed.

For example, if the number of locks temporarily increases during backup of the Microsoft SQL Server databases, you can set an upper limit for the Lock Detail (PD_LD) record to reduce system load.

The setting value must be greater than the maximum number of locks occurring during normal operation. You can check the number of locks by using either of the following methods:

  • Check the normal log in the agent logs.

    The number of locks is provided by the following message:

    KAVL19807-I Getting record ended = LD -> Storing started [ count = number-of-records ]

  • Execute an SQL statement to check the current number of locks.

    You can check the number of locks by executing the SQL statement described in the PD_LD row in Table A-1 in A.4 Calculating the number of instances for records.

Notes:
  • Even if you set the invalid value(s) to each item listed in the Table 2-5, jpcconf target setup command terminates successfully. Yet, even after PFM - RM for Microsoft SQL Server starts collecting the records, PFM - RM for Microsoft SQL Server does not collect the performance data. For details about actions to take when no performance data is collected, see 7.2.1(1) Performance data is not collected after PFM - RM for Microsoft SQL Server startup.

  • Do not use the multiple PFM - RM for Microsoft SQL Server to monitor the same Microsoft SQL Server instance.

  • Do not use PFM - RM for Microsoft SQL Server and PFM - Agent Option for Microsoft SQL Server to monitor the same instance of the Microsoft SQL Server.

  • If you are running a firewall environment on the host of the monitoring target Microsoft SQL Server instance, set up the firewall environment so that the PFM - RM for Microsoft SQL Server can successfully connect to the Microsoft SQL Server host. For details, see your Microsoft SQL Server documentation.

  • Do not specify values containing double quotation marks (") and spaces in the instance information of PFM - RM for Microsoft SQL Server.

In order to set up the monitoring target environment, execute the jpcconf target setup command. To set up the monitoring target environment:

Although an example of interactive command execution is shown here, the jpcconf target setup command can be also executed non-interactively. For details about the jpcconf target setup command, see the chapter that describes commands in the manual JP1/Performance Management Reference.

  1. Execute the jpcconf target setup command specified with the service key, the instance name, and the monitoring target.

    The jpcconf target setup command is executed in the following format:

    jpcconf target setup -key RMSQL -inst instance-name -target monitoring-target-name 
  2. Specify the monitoring target information of PFM - RM for Microsoft SQL Server

    Enter the information shown in the Table 2-5 in accordance with the command's instructions. You must enter all of the information items. To use the displayed default value, press the Enter key.

After you have finished entering the information, the monitoring target environment is set up. If you want to change the monitoring target information, re-execute the jpcconf target setup command and update the monitoring target environment. For details about updating the monitoring target environment, see 2.4.2 Updating a monitoring target.You can change some of the specified information by editing properties in PFM - Web Console. For details, see E.3 List of remote agent and group agent properties.

The monitoring target environment that is built up is described below:

  • The organization of the monitoring target environment folder

    After you have entered all items, the monitoring target environment is created. The following table shows the organization of the folders for the created monitoring target environment.

    Table 2‒8: Organization of the monitoring target environment folder

    Installation folder

    file name

    Description

    Installation-folder#\agt4\agent\instance-name\targets

    Monitoring-target-name.ini

    Configuration file for the monitoring target

    Monitoring-target-name.ini.model

    Model configuration file for the monitoring target

    #

    For the default storage location when PFM - RM for Microsoft SQL Server runs on a logical host, replace installation-folder with environment-folder\jp1pc.

The service ID added by setting the monitoring target is as follows:

  • Service ID to be added

    • Remote Agent service

      4A instance-number instance-name[monitoring-target-name@host-name]

      The instance name and monitoring target name are the values that are specified in the jpcconf target setup command. When host1 is specified as the host name of the PFM - RM host, inst1 is specified as the instance name, and targethost1 is specified as the monitoring target name, the service ID is set as follows:

      4A1inst1[targethost1@host1]

      For details about service IDs, see the naming rules described in the appendix in the JP1/Performance Management Planning and Configuration Guide.

(c) Using Windows authentication

When the instance environment is built up, the login account for the PFM - RM for Microsoft SQL Server service is created as the local system account. If you choose to use the Windows authentication when you set the monitoring target, you need to change the login account for the PFM - RM for Microsoft SQL Server service to the account that is accessible to the monitoring target host and can log in to the host as the service. To check whether the account you specify can log in as the service to the monitoring target host, use Microsoft SQL Server Management Studio or sqlcmd command. For details, see your Microsoft SQL Server documentation.

To change the account:
  • From the Windows Start menu, choose Administrative Tools and then Service.

  • In the Detail pane, right-click the PFM - RM for Microsoft(R) SQL Server instance-name Service window and then click Properties.

  • Click the Log On tab, specify the Account, and type the password.

For details, see the documentation for your OS.

Note:

Do not change the account of PFM - RM Store for Microsoft(R) SQL Server instance-name service.

(d) Login permissions of the Microsoft SQL Server

To operate PFM - RM for Microsoft SQL Server, you need a Microsoft SQL Server login (database user) with certain permissions.The following shows the permissions required for PFM - RM for Microsoft SQL Server to collect the performance information of the Microsoft SQL Server Database:

If the sysadmin fixed server role can be granted:

You can use the user account with the sysadmin fixed server role granted to collect all the records.

If the sysadmin fixed server role cannot be granted:

The user account used for monitoring needs the CONNECT permission for all the databases, and also requires the permissions shown in the table below.

Table 2‒9: The required permissions for PFM - RM for Microsoft SQL Server to collect performance information of Microsoft SQL Server Database

Permissions

Description

server role

The server permission that each object used for collecting records requires. (For details, see Table 2-10.)

Database permissions or object permissions

Either of the following permissions is required:

  • Database permissions

    Some database permissions are required for each record to be collected. (For details, see Table 2-11.)

  • Object permissions

    Some object permissions are required for each object used for collecting records. (To connect with a database of Microsoft SQL Server version 2014 or earlier, see Table 2-12, and to connect with a database of Microsoft SQL Server version 2016 or later, see Table 2-13.)

If you can grant permissions to the database, see If you want to grant database permissions in Database permissions or object permissions to assign the required permissions. If you want to grant more detailed object-based permissions than database permissions, see If you want to grant object permissions in Database permissions or object permissions to grant the required permissions.

■ server role

The following table shows the server permissions required for user accounts used for monitoring to collect records for PFM - RM for Microsoft SQL Server.

Table 2‒10: Server permissions required for collecting records for PFM - RM for Microsoft SQL Server

Records

Server permissions required for collecting records

If Microsoft SQL Server version 2014 or earlier is monitored

If Microsoft SQL Server version 2016 or later is monitored

  • Database Detail (PD_DD)

  • Database Space Detail (PD_DS)

  • Server Space Detail (PD_SS)

  • Server Space Interval (PI_SI)

  • VIEW SERVER STATE

  • VIEW ANY DEFINITION

  • VIEW SERVER STATE

  • VIEW ANY DEFINITION

  • VIEW ANY DATABASE

  • Lock Detail (PD_LD)

  • Process Detail (PD_PDET)

  • VIEW SERVER STATE

  • VIEW ANY DEFINITION

Server Detail (PD)

  • VIEW SERVER STATE

  • VIEW SERVER STATE

  • VIEW ANY DATABASE

  • Global Server Summary (PI)

  • Global Server Summary 2 (PI_PI2)

  • Server Overview (PI_SERV)

  • Server Overview 2 (PI_SRV2)

  • Server Locks Detail (PD_LOCK)

  • Transaction Log Overview (PI_TLOG)

  • VIEW SERVER STATE

  • Config Detail (PD_CD)

  • Instance Availability (PD_IA)

No server role permission is required.

■ Database permissions or object permissions

• If you want to grant database permissions

Grant necessary database permissions to the user account used for monitoring for each record as shown in the following table.

Table 2‒11: Database permissions required for collecting records for PFM - RM for Microsoft SQL Server

Records

Database permissions required for collecting records

PD_DS, PD_SS, PI_SI

  • Grant the SELECT permission for all the databases.

  • Grant the EXEC permission for the master database.

PD_DD, PD_LD

Grant the SELECT permission for all the databases.

PD_CD

Grant the EXEC permission for the master database.

PD, PI, PI_PI2, PI_SERV, PI_SRV2, PD_LOCK, PD_PDET

Grant the SELECT permission for the master database.

PD_IA, PI_TLOG

No database permission is required.

• If you want to grant object permissions

Grant necessary object permissions to the user account used for monitoring for each record as shown in the following table.

If you want to connect with the database of Microsoft SQL Server version 2014 or earlier, see Table 2-12, and if you want to connect with Microsoft SQL Server version 2016 or later, see Table 2-13.

Table 2‒12: The required permissions for each object which PFM - RM for Microsoft SQL Server uses when collecting records(Microsoft SQL Server version 2014 or earlier)

Records

Object permissions granted to users required for collecting records

PD_DD

  • Grant the SELECT permission for the following objects in the master database:

    - sysdatabases

    - sysprocesses

    - syslogins

    - sysusers

    - spt_values

    - sysfiles

    - syslockinfo

    - sysperfinfo

  • Grant the EXEC permission for the following objects in the master database:

    - sp_databases (In case of not executing the sp_rist.sql script)

    - R4QHITACHIPROCSPDATABASES (In case of executing the sp_rist.sql script)

  • Grant the SELECT permission for the following object in the msdb database:

    - backupset

  • Grant the SELECT permission for the following object in all the databases:

    - sys.indexes

PD_DS, PD_SS, PI_SI

  • Grant the SELECT permission for the following objects in the master database:

    - sysdatabases

    - spt_values

    - sysfiles

  • Grant the EXEC permission for the following objects in the master database:

    - sp_databases (In case of not executing the sp_rist.sql script)

    - R4QHITACHIPROCSPDATABASES (In case of executing the sp_rist.sql script)

  • Grant the SELECT permission for the following object in the msdb database:

    - backupset

  • Grant the SELECT permission for the following object in all the databases:

    - sys.indexes

    - sys.allocation_units

    - sys.partitions

    - sys.internal_tables

PD_LD

  • Grant the SELECT permission for the following objects in the master database:

    - sysprocesses

    - syslogins

    - sysusers

    - sys.dm_tran_locks

    - syslockinfo

  • Grant the SELECT permission for the following object in all the databases:

    - sys.all_objects

PD_PDET

  • Grant the SELECT permission for the following objects in the master database:

    - syslockinfo

    - sysprocesses

    - syslogins

    - sysusers

PD

  • Grant the SELECT permission for the following objects in the master database:

    - sysdatabases

    - sysprocesses

    - syslogins

    - sysusers

    - spt_values

    - syslockinfo

    - sysperfinfo

  • Grant the EXEC permission for the following objects in the master database:

    - xp_msver

  • Grant the SELECT permission for the following object in the msdb database:

    - backupset

PI, PI_PI2, PI_SERV, PI_SRV2

  • Grant the SELECT permission for the following objects in the master database:

    - sysperfinfo

PD_LOCK

  • Grant the SELECT permission for the following objects in the master database:

    - syslockinfo

    - sysprocesses

    - syslogins

    - sysusers

PD_CD

  • Grant the EXEC permission for the following objects in the master database:

    - sp_configure

PI_TLOG, PD_IA

All the users can execute the object.

Table 2‒13: The required permissions for each object which PFM - RM for Microsoft SQL Server uses when collecting records(Microsoft SQL Server version 2016 or later)

Records

Object permissions granted to users required for collecting records

PD_DD

  • Grant the SELECT permission for the following objects in the master database:

    - sys.databases

    - sys.dm_tran_locks

    - sys.dm_exec_requests

    - sys.dm_exec_sessions

    - sys.dm_os_tasks

    - sys.dm_os_waiting_tasks

    - sys.server_principals

    - sys.database_principals

    - spt_values

    - sys.dm_os_performance_counters

  • Grant the SELECT permission for the following object in the msdb database:

    - backupset

  • Grant the SELECT permission for the following object in all the databases:

    - sys.indexes

PD_DS, PD_SS, PI_SI

  • Grant the SELECT permission for the following objects in the master database:

    - sys.databases

    - sys.database_files

    - spt_values

  • Grant the SELECT permission for the following object in the msdb database:

    - backupset

  • Grant the SELECT permission for the following object in all the databases:

    - sys.indexes

    - sys.allocation_units

    - sys.partitions

    - sys.internal_tables

PD_LD

  • Grant the SELECT permission for the following objects in the master database:

    - sys.dm_tran_locks

    - sys.dm_exec_requests

    - sys.dm_exec_sessions

    - sys.dm_os_tasks

    - sys.dm_os_waiting_tasks

    - sys.server_principals

    - sys.database_principals

  • Grant the SELECT permission for the following object in all the databases:

    - sys.all_objects

    - sys.partitions

PD_PDET

  • Grant the SELECT permission for the following objects in the master database:

    - sys.dm_tran_locks

    - sys.dm_exec_requests

    - sys.dm_exec_sessions

    - sys.dm_os_tasks

    - sys.dm_os_waiting_tasks

    - sys.server_principals

    - sys.database_principals

PD

  • Grant the SELECT permission for the following objects in the master database:

    - sys.databases

    - sys.dm_tran_locks

    - sys.dm_exec_requests

    - sys.dm_exec_sessions

    - sys.dm_os_tasks

    - sys.dm_os_waiting_tasks

    - sys.server_principals

    - sys.database_principals

    - sys.dm_os_performance_counters

    - spt_values

  • Grant the SELECT permission for the following object in the msdb database:

    - backupset

PI, PI_PI2, PI_SERV, PI_SRV2

  • Grant the SELECT permission for the following objects in the master database:

    - sys.dm_os_performance_counters

PD_LOCK

  • Grant the SELECT permission for the following objects in the master database:

    - sys.dm_tran_locks

    - sys.dm_exec_requests

    - sys.dm_exec_sessions

    - sys.dm_os_tasks

    - sys.dm_os_waiting_tasks

    - sys.server_principals

    - sys.database_principals

PD_CD

  • Grant the EXEC permission for the following objects in the master database:

    - sp_configure

PI_TLOG, PD_IA

All the users can execute the object.

(e) Notes when a non-default Microsoft SQL Server port number or the named pipe protocol is used to connect with Microsoft SQL Server

PFM - RM for Microsoft SQL Server communicates with Microsoft SQL Server through the TCP/IP or named pipe protocol.

Use SQL Server Client Network Utility or any other tool to set an alias, if:

  • TCP/IP is enabled in the protocol settings and a non-default Microsoft SQL Server port number is specified.

  • The named pipe protocol is enabled.

As PFM - RM for Microsoft SQL Server is a 64-bit application, specify a 64-bit alias. If you use SQL Server Client Network Utility, execute the following file to specify it:

%windir%\system32\cliconfg.exe

To specify an alias, select the Alias tab and enter the following values:

When a non-default port number for Microsoft SQL Server is used
  • For the default instance:

    Server name: TARGET_HOST#1

    Dynamically determine port: Clear the check box

    Network libraries: TCP/IP

    Port number: Port number for Microsoft SQL Server

    Server alias: TARGET_HOST#1

  • For the named instance:

    Server name: TARGET_HOST#1\SQL_INSTANCE#2

    Dynamically determine port: Clear the check box

    Network libraries: TCP/IP

    Port number: Port number for Microsoft SQL Server

    Server alias: TARGET_HOST#1\SQL_INSTANCE#2

When the named pipe protocol is used
  • For the default instance:

    Server name: TARGET_HOST#1

    Network libraries: Named Pipes

    Pipe name: \\TARGET_HOST#1\pipe\Pipe name for Microsoft SQL Server

    Server alias: TARGET_HOST#1

  • For the named instance:

    Server name: TARGET_HOST#1\SQL_INSTANCE#2

    Network libraries: Named Pipes

    Pipe name: \\TARGET_HOST#1\pipe\MSSQL$SQL_INSTANCE#2\Pipe name for Microsoft SQL Server

    Server alias: TARGET_HOST#1\SQL_INSTANCE#2

#1

TARGET_HOST value for PFM - RM for Microsoft SQL Server

#2

SQL_INSTANCE value for PFM - RM for Microsoft SQL Server

To communicate with Microsoft SQL Server through the named pipe protocol, specify a user commonly used in the PFM - RM host and the monitoring target host (user name and password are the same) as an account for PFM - RM for Microsoft SQL Server service.

Note that depending on your version, the settings may look different. In that case, replace them to suit your own environment.

To check if the specified alias works for connecting with Microsoft SQL Server, use the sqlcmd command. For details, see your Microsoft SQL Server documentation.

(3) Registering the R4QHITACHIPROCSPDATABASES Stored Procedure[Figure]

Register the stored procedure shown below with the master database in the monitored Microsoft SQL Server instance.

Register the stored procedure on the monitoring target Microsoft SQL Server host.

If you install Microsoft SQL Server on the PFM - RM host, use osql utility or sqlcmd utility to register the stored procedure to the Microsoft SQL Server.

The R4QHITACHIPROCSPDATABASES stored procedure can be registered by using the sp_rist.sql script provided by PFM - RM for Microsoft SQL Server. To use the splcmd utility to register the stored procedure:

  1. Set up an environment in which the splcmd utility of Microsoft SQL Server can be executed.

    The splcmd utility is provided by Microsoft. For details about the environment settings for Microsoft SQL Server, see your Microsoft SQL Server documentation.

  2. Move to the folder in which the sp_rist.sql script is located.

    The script is located in the following folder:

    installation-folder\agt4\sql
  3. Execute the script with the monitored Microsoft SQL Server instance specified.

    The command line for executing the sp_rist.sql script depends on the type of the monitored Microsoft SQL Server instance and the method of authentication used for connecting to Microsoft SQL Server. The following shows the command line for each condition.

    • When the default instance of Microsoft SQL Server is being monitored

      Authentication method

      Command line for executing the sp_inst.sql script

      SQL Server

      splcmd -S host-name -U user-name -P password -d master -i sp_rist.sql #

      Windows

      splcmd -S host-name -E -d master -i sp_rist.sql #

    • When a named instance of Microsoft SQL Server is being monitored

      Authentication method

      Command line for executing the sp_inst.sql script

      SQL Server

      splcmd -S host-name\instance-name -U user-name -P password -d master -i sp_rist.sql #

      Windows

      splcmd -S host-name\instance-name -E -d master -i sp_rist.sql #

      #

      The following describes the items that the user specifies:

      host-name: Name of the host on which the monitored Microsoft SQL Server instance operates

      instance-name: Name of the monitored Microsoft SQL Server instance

      user-name: sa or a user account that has permissions equivalent to sa (the account of a member who has the sysadmin fixed server role)

      password: Password for the specified user name

For details about how to delete the R4QHITACHIPROCSPDATABASES Stored Procedure, see I. Deleting the R4QHITACHIPROCSPDATABASES Stored Procedure.

(4) Specifying network settings[Figure]

You must specify the network settings according to the configuration in which Performance Management will be used.

You can specify the following two network settings:

(5) Changing the size of log files [Figure]

Performance Management outputs its operating status to a set of proprietary log files called the common message log. Setting is required only if you want to change the default file size.

For details, see the chapter on installation and setup in the JP1/Performance Management Planning and Configuration Guide.

(6) Changing the storage location of performance data [Figure]

Setting is required only if you want to change the folders used for saving, backing up, exporting, or importing the database of performance data managed by PFM - RM for Microsoft SQL Server.

By default, performance data is saved in the following locations:

Note:

For the default save destination for logical host operation, replace installation-folder with environment-folder\jp1pc\.

For details, see 2.4.1 Changing the storage location of performance data.

(7) Setting the connection-target PFM - Manager of PFM - RM for Microsoft SQL Server

On the host where PFM - RM for Microsoft SQL Server is installed, you must specify the PFM - Manager that manages that PFM - RM for Microsoft SQL Server.

Use the jpcconf mgrhost define command to set the connection-target PFM - Manager.

Important
  • When multiple instances of PFM - RM are installed on a single host, you can specify only one PFM - Manager as their connection target. You cannot have different instances of PFM - Manager as the connection target for the PFM - RM instances.

  • If PFM - RM for Microsoft SQL Server and PFM - Manager are installed on the same host, the PFM - Manager on the local host is the connection-target PFM - Manager. You cannot use PFM - Manager on another host as the connection target.

  • Stop all active Performance Management programs and services on the local host before beginning setup. For details about how to stop services, see the chapter on starting and stopping Performance Management in the JP1/Performance Management User's Guide.

To specify the connection-target PFM - Manager:

  1. Stop all Performance Management programs and services.

    If any Performance Management programs or services are running when you execute the jpcconf mgrhost define command, you will be prompted by a message to stop the programs or services.

  2. Execute the jpcconf mgrhost define command with the host name of the connection-target PFM - Manager host specified.

    For example, if the connection-target PFM - Manager is on host host01, specify the following:

    jpcconf mgrhost define -host host01

    Although an example of interactive command execution is shown here, the jpcconf mgrhost define command can be also executed non-interactively. For details about the jpcconf mgrhost define command, see the chapter that describes commands in the manual JP1/Performance Management Reference.

(8) Outputting the action logs [Figure]

You need to perform this setting if you want to output action logs when PFM - RM for Microsoft SQL Server gives alarms. Action logs are the historical information output with the alarms exceeding threshold values (such as the alarms concerning system load).

For details about how to set up, see J. Outputting Action Log Information.