2.1.4 Setting up 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.

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

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