Hitachi

JP1 Version 12 JP1/Performance Management - Agent Option for Microsoft(R) SQL Server


2.1.4 Setting up PFM - Agent for Microsoft SQL Server

This section explains the setup that is required before you can use PFM - Agent 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 - Agent for Microsoft SQL Server[Figure]

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

If PFM - Agent 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 - Agent for Microsoft SQL Server is not registered yet, manually register PFM - Agent for Microsoft SQL Server according to the procedure.

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

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

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

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

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

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

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

  • The PFM - Agent 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 - Agent 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 - Agent for Microsoft SQL Server.

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

[Figure]

Important
  • Register PFM - Agent 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 - Agent for Microsoft SQL Server when you add it to the Performance Management system.

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

  • When you install PFM - Agent 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.

  • You must enable shared memory on the Microsoft SQL Server side because PFM - Agent for Microsoft SQL Server uses shared memory to communicate with the Microsoft SQL Server. For a cluster environment, enable TCP/IP on the Microsoft SQL Server side because PFM - Agent for Microsoft SQL Server uses TCP/IP to communicate with Microsoft SQL Server.

  • For details about how to enable shared memory and for other detailed information, refer to the Microsoft SQL Server documentation.

  • 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 - Agent for Microsoft SQL Server creates the SQL folder in Reports window and Alarms window of PFM - Web Console. If the user has already created a folder or file named the SQL on the Reports window, rename the file or folder before registering PFM - Agent for Microsoft SQL Server.

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

Copy the setup files from the host on which you installed PFM - Agent 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 - Agent 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 - Agent for Microsoft SQL Server setup file

    Destination

    PFM program name

    OS

    Destination folder

    installation-folder\setup\jpcagtqw.EXE

    PFM - Manager

    Windows

    installation-folder\setup\

    installation-folder\setup\jpcagtqu.Z

    UNIX

    /opt/jp1pc/setup/

    installation-folder\setup\jpcagtqw.EXE

    PFM - Web Console

    Windows

    installation-folder\setup\

    installation-folder\setup\jpcagtqu.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 - Agent for Microsoft SQL Server:

jpcconf agent setup -key SQL

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 - Agent 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 - Agent for Microsoft SQL Server:

jpcwagtsetup

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

(2) Setting up an instance environment

PFM - Agent for Microsoft SQL Server requires configurations of the instance environment. You can set up multiple instance environments by repeating the procedure for each instance.

This section describes how to set up the instance environment.

(a) Set up instance information

You must specify instance information for the Microsoft SQL Server that is to be monitored by the PFM - Agent for Microsoft SQL Server. Specify instance information on the PFM - Agent 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. For details about how to install Microsoft SQL Server, instance information, and user authentication, see the Microsoft SQL Server documentation.

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

Item

Description

Specifiable value

Default

Microsoft SQL Server instance name

Displays the instance name of the monitored Microsoft SQL Server.

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]

For detail, see about setting up an instance environment.

--

SQL_HOST

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

--

The name of the host on which PFM - Agent for Microsoft SQL Server installed.

SQL_USER#1

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 (c) Login permissions of the Microsoft SQL Server.

Specify the Microsoft SQL Server user name, by using single-byte characters.

sa

SQL_PASSWORD#1

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

The password for SQL_USER.

--

DRIVER_NAME#2

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

SQL_ERRORLOG

Specify the absolute path of the error log file for the monitored Microsoft SQL Server instance in the PD_EE record and PD_ES record.

When no error log file is to be monitored, specify the default value. (Even though the specified value will not be used, this is required because a blank is not allowed.)

Even when the file name or path includes single-byte space characters, do not surround it with quotation marks (").

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

  • Tabs

  • The following symbols:

    / , ; * ? " < > |

C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\ERRORLOG

STARTUP_PENDING#3

Specify the pending time from when PFM - Agent for Microsoft SQL Server starts until it transfers to normal processing.

For details about pending function, see 3.2.1(2) ending function during failover

0 to 3,600 (seconds)

Pending is not performed when 0 is specified.

0

LOG_PATH#3#4

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\agtq\agent\instance-name\log

LOG_SIZE#3

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

1 to 32 (megabytes).

The minimum recommended value is 16.

16

TIMEOUT#3

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

1 to 3,600 (seconds)

60

LOGIN_TIMEOUT#3

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

1 to 3,600 (seconds)

20

Store Version#6

Specify the version of the Store database to be used. For details about the version of the Store database, see 2.4.3 Updating the Store version to 2.0.

1.0 or 2.0

2.0

DB_FREE_PERC_OPTION#7#9

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#8#9

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#10

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

LIMIT_PD_EE_NUMBER#11

Specify the maximum number of collection records for the PD_EE record. This item takes effect when log data is collected and when monitoring is performed by using alarms. Specify the maximum number of occurrences that can be collected during the collection interval set for Collection Interval. For detail about PD_EE record, see Errorlog Error Detail (PD_EE) of 5. Records.

Note:

For real-time reports, this item does not take effect, and all error records output to the Microsoft SQL Server error log are collected.

100 to 100,000

1000

Legend:

--: None

#1

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‒4: 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 - Agent for Microsoft SQL Server uses its service account to obtain authentication.

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

#2

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‒5: 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:

Control Panel - Administrative Tools - ODBC Data Source Administrator (64-bit) - Drivers tab

Furthermore, encryption of the communication between Microsoft SQL Server and the client (including PFM - Agent 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 KAVF21400-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.

KAVF21810-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.

#3

When PFM - Agent for Microsoft SQL Server is upgraded from a version earlier than 08-00, the default values are set.

#4

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.

#5

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.

#6

You can specify the Store Version item only when setting up a new instance environment. You cannot specify this item when updating an existing instance environment.

#7

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.

#8

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.

#9

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

#10

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:

    KAVF21807-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.

#11

If a failure occurs in Microsoft SQL Server, many records about the same error might be output to the Microsoft SQL Server error log continuously.

If an attempt is made to collect the Errorlog Error Detail (PD_EE) record in this situation, the following problems 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.

By using this item to set the upper limit, you can reduce the load on the system. We recommend that you set 1,000 as the upper limit.

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

  • When using the jpcinssetup command to create an instance environment, the command ends normally even if you specify an instance name that does not exist on the Microsoft SQL Server. However, when you start collecting records, the KAVF21400-W message is output to the common message log and the Tuning Manager server cannot connect to the monitored Microsoft SQL Server. In this case, specify the correct instance name, then run the jpcinssetup command again.

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

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 SQL -inst instance-name

    The instance-name to be specified should be as follows, depending on the installation method for Microsoft SQL Server:

    • When default installation of Microsoft SQL Server was performed:

      You must specify default for the instance-name (to monitor default instances), as follows:

      jpcconf inst setup -key SQL -inst default

      Note that names other than default cannot be specified.

    • When installation of Microsoft SQL Server was performed with a specified instance name:

      You must specify the instance-name so that it can be recognized as the instance of the Microsoft SQL Server. Specify the instance name that was entered at installation of the Microsoft SQL Server (the instance name of the Microsoft SQL Server to be monitored). If Microsoft SQL Server was installed with the instance named SQL1, specify the instance-name as follows:

      jpcconf inst setup -key SQL -inst SQL1

    For details about the jpcconf inst setup command, see the chapter that describes commands in the manual JP1/Performance Management Reference.

  2. Set up the instance information for PFM - Agent 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 instance information, re-execute the jpcconf inst setup command to update the instance environment. For details about updating an instance environment, see 2.4.2 Updating an instance environment.

The instance environment that is built is described as follows:

  • The organization of the instance environment folder

    The following folder configuration of the instance environment that is built.

    For physical host: installation-folder\agtq

    For logical host: environment-folder#\jp1pc\agtq

    #

    Environment-folder is the folder that is created on the shared disk when setting up the logical host.

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

    Table 2‒6: Folder configuration of the instance environment

    Folder name and File name

    Descriptions

    agent

    instance-name

    jpcagt.ini

    Agent Collector service startup initialization file

    jpcagt.ini.model#

    Model file for the Agent Collector service startup initialization file

    log

    Storage folder for log files

    store

    instance-name

    jpcsto.ini

    Agent Store service startup initialization file

    jpcsto.ini.model#

    Model file for the Agent Store service startup initialization file

    *.DAT

    Data model definition file

    dump

    Export destination folder

    import

    Standard database import destination folder (for Store version 2.0)

    backup

    Backup destination folder

    log

    Storage folder for log files

    partial

    Standard database partial backup destination folder (for Store version 2.0)

    STPD

    Performance data storage folder for the PD record type (for Store version 2.0)

    STPI

    Performance data storage folder for the PI record type (for Store version 2.0)

    #

    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.

    • Agent Collector service: QAinstance-number instance-name[host-name]

    • Agent Store service: QSinstance-number instance-name[host-name]

    In PFM - Agent 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:

    • Agent Collector service: QA1default [host1]

    • Agent Store service: QS1default [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:

    • Agent Collector service: PFM - Agent for Microsoft(R) SQL Server instance-name[logical-host-name]

    • Agent Store service: PFM - Agent Store for Microsoft(R) SQL Server instance-name[logical-host-name]

    In PFM - Agent for Microsoft SQL Server, the instance name specified in the jpcconf inst setup command is displayed.

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

    • Agent Collector service: PFM - Agent for Microsoft(R) SQL Server default[lhost]

    • Agent Store service: PFM - Agent Store for Microsoft(R) SQL Server default[lhost]

    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) Using Windows authentication

When the instance environment is built up, the login account for the PFM - Agent for Microsoft SQL Server service is created as the local system account.

If you select Windows authentication for the SQL_USER item of the instance information, the logged-in account of the PFM - Agent for Microsoft SQL Server service is used for authentication.

If you prepare another account, the account needs to be able to be used to connect with the monitoring target Microsoft SQL Server. 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:

  1. From the Windows Start menu, choose Administrative Tools and then Service.

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

  3. 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 - Agent Store for Microsoft(R) SQL Server instance-name service.

(c) Login permissions of the Microsoft SQL Server

To operate PFM - Agent for Microsoft SQL Server, you need a Microsoft SQL Server login (database user) with certain permissions.

The following shows the permissions required for PFM - Agent 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.

If you want to collect PD_SQL records, the sysadmin fixed server role is required. If this role is not granted, you cannot collect PD_SQL records.

Table 2‒7: The required permissions for PFM - Agent 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-8.)

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-9.)

  • 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-10, and to connect with a database of Microsoft SQL Server version 2016 or later, see Table 2-11.)

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 - Agent for Microsoft SQL Server.

If you want to collect PD_SQL records, the sysadmin fixed server role is required. If this role is not granted, you cannot collect PD_SQL records.

Table 2‒8: Server permissions required for collecting records for PFM - Agent 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

SQL Text (PD_SQL)

Records cannot be collected.

  • Database Detail (PD_DD)

  • Database Space Detail (PD_DS)

  • Server Space Detail (PD_SS)

  • Server Space Interval (PI_SI)

  • Database Interval (PI_DI)

  • VIEW SERVER STATE

  • VIEW ANY DEFINITION

  • VIEW SERVER STATE

  • VIEW ANY DEFINITION

  • VIEW ANY DATABASE

  • Lock Detail (PD_LD)

  • Process Detail (PD_PDET)

  • User Process Detail (PI_USER)

  • VIEW SERVER STATE

  • VIEW ANY DEFINITION

  • Server Detail (PD)

  • Database Replication Detail (PD_RD)

  • Replication Summary Detail (PD_RS)

  • VIEW SERVER STATE

  • VIEW SERVER STATE

  • VIEW ANY DATABASE

  • Global Server Summary (PI)

  • Global Server Summary 2 (PI_PI2)

  • Licensing Detail (PD_LIC)

  • User-Defined Counter Overview (PI_UCTR)

  • Server Overview (PI_SERV)

  • Server Overview 2 (PI_SRV2)

  • Server Locks Detail (PD_LOCK)

  • Transaction Log Overview (PI_TLOG)

  • VIEW SERVER STATE

  • Replication Published Database Overview (PI_RPDB)

  • Procedure Cache Detail (PD_PCAC)

  • Config Detail (PD_CD)

  • Job History Detail (PD_JH)

  • Errorlog Error Detail (PD_EE)

  • Errorlog Summary Detail (PD_ES)

  • 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‒9: Database permissions required for collecting records for PFM - Agent for Microsoft SQL Server

Records

Database permissions required for collecting records

PD_SQL

Records cannot be collected.

PD_PCAC

Grant the db_owner database role for the master database.

PI_RPDB

  • Grant the db_owner database role for the master database.

  • Grant the SELECT permission for all the databases.

PD_DS, PD_SS, PI_SI, PI_DI, PD_RS

  • Grant the SELECT permission for all the databases.

  • Grant the EXEC permission for the master database.

PD_DD, PD_RD, PD_LD

Grant the SELECT permission for all the databases.

PD_CD

Grant the SELECT permission for the master database.

PD, PI, PI_PI2, PD_LIC, PI_UCTR, PI_SERV, PI_SRV2, PD_LOCK, PD_PDET, PD_USER

Grant the SELECT permission for the master database.

PD_JH

Grant the SELECT permission for the msdb database.

PD_EE, PD_ES, 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-10, and if you want to connect with Microsoft SQL Server version 2016 or later, see Table 2-11.

Note that if you want to collect PD_PCAC or PI_RPDB records, the db_owner database role for the master database is required. If this role is not granted, you cannot collect PD_PCAC or PI_RPDB records.

Table 2‒10: The required permissions for each object which PFM - Agent 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_SQL, PD_PCAC, PI_RPDB

Records cannot be collected.

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_inst.sql script )

    - A4QHITACHIPROCSPDATABASES (In case of executing the sp_inst.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_inst.sql script)

    - A4QHITACHIPROCSPDATABASES (In case of executing the sp_inst.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

PI_DI

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

    - sysdatabases

    - sysprocesses

    - syslogins

    - sysusers

    - spt_values

    - sysfiles

    - sysperfinfo

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

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

    - A4QHITACHIPROCSPDATABASES (In case of executing the sp_inst.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_USER

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

    - sysprocesses

    - syslogins

    - sysusers

    - syslockinfo

PD

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

    - sysdatabases

    - sysprocesses

    - syslogins

    - sysusers

    - spt_values

    - syslockinfo

    - sysperfinfo

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

    - backupset

PD_RD

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

    - sysdatabases

    - spt_values

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

    - backupset

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

    - MSdistribution_history

    - sys.indexes

    - sysarticles

    - syspublications

    - syssubscriptions

    - all_objects

PD_RS

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

    - sysdatabases

    - spt_values

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

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

    - A4QHITACHIPROCSPDATABASES (In case of executing the sp_inst.sql script)

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

    - backupset

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

    - MSdistribution_history

    - sys.indexes

    - sysarticles

    - syspublications

    - syssubscriptions

    - all_objects

PI, PI_PI2, PD_LIC, PI_UCTR, 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

PD_JH

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

    - sysjobhistory

    - sysjobsteps

    - sysjobs

    - all_objects

PI_TLOG, PD_EE, PD_ES, PD_IA

All the users can execute the object.

Table 2‒11: The required permissions for each object which PFM - Agent 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_SQL, PD_PCAC, PI_RPDB

Records cannot be collected.

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 objects in the msdb database:

    - backupset

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

    - 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 objects in the msdb database:

    - backupset

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

    - sys.indexes

    - sys.allocation_units

    - sys.partitions

    - sys.internal_tables

PI_DI

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

    - sys.databases

    - spt_values

    - sys.dm_exec_requests

    - sys.dm_exec_sessions

    - sys.dm_os_tasks

    - sys.dm_os_waiting_tasks

    - sys.server_principals

    - sys.database_principals

    - sys.database_files

    - sys.dm_os_performance_counters

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

    - backupset

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

    - 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 objects in all the database:

    - 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_USER

  • 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

    - sys.server_role_members

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 objects in the msdb database:

    - backupset

PD_RD

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

    - sys.databases

    - spt_values

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

    - MSdistribution_history

    - sys.indexes

    - sysarticles

    - syspublications

    - syssubscriptions

    - sys.all_objects

PD_RS

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

    - sys.databases

    - spt_values

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

    - MSdistribution_history

    - sys.indexes

    - sysarticles

    - syspublications

    - syssubscriptions

    - sys.all_objects

PI, PI_PI2, PD_LIC, PI_UCTR, 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

PD_JH

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

    - sysjobhistory

    - sysjobsteps

    - sysjobs

    - sys.all_objects

PI_TLOG, PD_EE, PD_ES, PD_IA

All ther users can execute the object.

(3) Registering the A4QHITACHIPROCSPDATABASES Stored Procedure [Figure]

Register the A4QHITACHIPROCSPDATABASES stored procedure in the master database of the monitored Microsoft SQL Server instance.

The A4QHITACHIPROCSPDATABASES stored procedure can be registered by using the sp_inst.sql script provided by PFM - Agent 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_inst.sql script is located.

    The script is located in the following folder:

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

    The command line for executing the sp_inst.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_inst.sql #

      Windows

      splcmd -S host-name -E -d master -i sp_inst.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_inst.sql #

      Windows

      splcmd -S host-name\instance-name -E -d master -i sp_inst.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 A4QHITACHIPROCSPDATABASES Stored Procedure, see I. Deleting the A4QHITACHIPROCSPDATABASES 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 - Agent 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\.

#

This folder can be set only when the Store version is 2.0.

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

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

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

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

Important

Notes:

  • When multiple instances of PFM - Agent 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 - Agent instances.

  • If PFM - Agent 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.

To specify the connection-target PFM - Manager:

  1. Stop all Performance Management programs and services.

    Before executing a setup operation, stop all Performance Management programs and services on the local host if they are active. For details about how to stop services, see the chapter explaining how to start and stop Performance Management in the JP1/Performance Management User's Guide.

    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 - Agent 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.