This section explains the setup that is required before you can use PFM - RM for Microsoft SQL Server.
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.
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. You will need to register PFM - RM for Microsoft SQL Server in the following cases:
For details about the data model version of an instance of PFM - RM for Microsoft SQL Server, see H. Version Compatibility.
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
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:
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/ |
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 Job Management Partner 1/Performance Management Reference.
You can then delete the PFM - RM for Microsoft SQL Server setup files remaining on the PFM - Manager 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.
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.
This section describes how to set up the instance environment and the monitoring target.
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. | A character string of 245 or fewer bytes that does not include the following characters:
| 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 |
To build an instance environment using the jpcconf inst setup command:
jpcconf inst setup -key RMSQL -inst instance-name
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:
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 |
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. | Specifiable value varies depending on how you install Microsoft SQL Server:
| default |
SQL_USER#2 | Specifies the user name of Microsoft SQL Server used for Microsoft SQL Server authentication. | 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. | -- |
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#3,#4 | Specify how to display the value of Free % field in the PD_DS record when its value is negative. | {Y | N} If Y is specified, the value of DB_FREE_PERC_NUMBER is displayed as the value of Free % field when its value is negative. If N is specified, the value of Free % field is displayed as it is when its value is negative. | Y |
DB_FREE_PERC_NUMBER#4,#5 | Specify the value to replace the value of Free % in the PD_DS record 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#6 | 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. | 0 |
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.
When you use Windows Authentication to monitor Microsoft(R) SQL Server 2012, the server role sysadmin is not added to the local system account. Accordingly, add sysadmin or prepare another account.
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 Job Management Partner 1/Performance Management Reference.
jpcconf target setup -key RMSQL -inst instance-name -target monitoring-target-name
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:
Table 2-7 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 |
The service ID added by setting the monitoring target is as follows:
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.
For details, see the documentation for your OS.
To operate the Microsoft SQL Server, you need a Microsoft SQL Server login (database user) with certain permissions.For PFM - RM for Microsoft SQL Server to collect the performance information of the Microsoft SQL Server Database, the select permission on the sysfiles system tables of each database is required.You must grant the proper permissions in accordance with the records you collect by using PFM - RM for Microsoft SQL Server and operate PFM - RM for Microsoft SQL Server. The table 2-8 shows the required permissions for collecting each record of PFM - RM for Microsoft SQL Server.
Table 2-8 The required permissions for each record of PFM - RM for Microsoft SQL Server
Records | The required permissions |
---|---|
| The required permissions varies depending on the objects you use when collecting records (for details, see the table 2-9). |
The below table shows the required permissions for each object which PFM - RM for Microsoft SQL Server uses when collecting records.
Table 2-9 The required permissions for each object which PFM - RM for Microsoft SQL Server uses when collecting records
Records | Objects | Required permissions when collecting records |
---|---|---|
PD_CD | sp_configure | Grant the EXEC permissions for the sp_configure to the database users for the master database mapped to the login. |
PD, PI, PI_PI2 | The scalar functions whose name begins with "@@". | None (all the users can execute the objects by default) |
PD, PD_DD, PD_DS, PD_SS, PI_SI | master..sysdatabases | Grant the SELECT permissions for the sysdatabases to the database users for the master database mapped to the login. |
sp_databases | Grant the EXEC permissions for the sp_databases to the database users for the master database mapped to the login. Note that you do not need to grant the permission when you execute the sp_inst.sql script. | |
R4QHITACHIPROCSPDATABASES | Grant the EXEC permissions for the R4QHITACHIPROCSPDATABASES to the database users for the master database mapped to the login. Note that you need to grant the permission only when you execute the sp_inst.sql script. | |
master.dbo.spt_values | Grant the SELECT permissions for the spt_values to the database users for the master database mapped to the login. | |
*..sysindexes | Grant the SELECT permissions for the sysindexes to the database users for all the databases mapped to the login. | |
*.sys.indexes | Grant the SELECT permission for the sys.indexes to the database users (including the users in the mirroring configuration if you monitor the mirrored databases) for all the databases mapped to the login. | |
DBCC SQLPERF(LOGSPACE) | VIEW SERVER STATE permission is a prerequisite for all the database users to execute the object. | |
msdb.dbo.backupset | Grant the SELECT permission for the backupset to the database users for the msdb database mapped to the login. | |
*..sysarticles | Grant the SELECT permission for the sysarticles to the database users for all the databases mapped to the login. | |
*..syspublications | Grant the SELECT permission for the syspublications to the database users for all the databases mapped to the login. | |
*..syssubscriptions | Grant the SELECT permission for the syssubscriptions to the database users for all the databases mapped to the login. | |
PD, PD_DD, PD_LD, PD_LOCK | master..sysprocesses | Grant the SELECT permission for the sysprocesses and the VIEW SERVER STATE permission for the Microsoft SQL Server host to the database users for the master database mapped to the login. |
master..syslogins | Grant the SELECT permission for the syslogins to the database users for the master database mapped to the login. | |
master..sysusers | Grant the SELECT permission for the sysusers to the database users for the master database mapped to the login. | |
PD, PD_DD, PD_LD, PD_LOCK, PD_PDET | master..syslockinfo | Grant the SELECT permission for the syslockinfo and the VIEW SERVER STATE permission for the Microsoft SQL Server to the database users for the master database mapped to the login. |
*..sysobjects | Grant the SELECT permission for the sysobjects to the database users for all the databases mapped to the login. | |
PD, PI, PI_SERV, PI_PI2, PI_SRV2 | master..sysperfinfo | Grant the SELECT permission for the sysperfinfo to the database users for the master database mapped to the login. |
PD_DD, PD_DS, PD_SS, PI_SI | *..sysfiles | Grant the SELECT permission for the sysfiles to the database users for all the databases mapped to the login. |
PD_DS, PD_SS, PI_SI | *.sys.allocation_units | Grant the SELECT permission for the sys.allocation_units to the database users (including the users in the mirroring configuration if you monitor the mirrored databases) for all the databases mapped to the login. |
*.sys.partitions | Grant the SELECT permission for the sys.partitions to the database users (including the users in the mirroring configuration if you monitor the mirrored databases) for all the databases mapped to the login. | |
*.sys.internal_tables | Grant the SELECT permission for the sys.internal_tables to the database users (including the users in the mirroring configuration if you monitor the mirrored databases) for all the databases mapped to the login. | |
PD, PI, PI_SERV, PI_PI2, PI_SRV2 | DBCC SQLPERF(IOSTATS) | None (all the users can execute the objects). |
DBCC SQLPERF(LRUSTATS) | ||
DBCC SQLPERF(NETSTATS) | ||
DBCC SQLPERF(RASTATS) | ||
PI_TLOG | DBCC SQLPERF(LOGSPACE) | VIEW SERVER STATE permission is a prerequisite for all the database users to execute the object. |
PD_LD | master.sys.dm_tran.locks | Grant the SELECT permission for the sys.dm_tran_locks and the VIEW SERVER STATE permission for the Microsoft SQL Server to the database users for the master database mapped to the login. |
*.sys.all_objects | Grant the SELECT permission for the sys.all_objects to the database users for all the databases mapped to the login. |
If the size of a database in an instance is 2 terabyte or larger, PFM - RM for Microsoft SQL Server can acquire information only when this stored procedure has been Registered.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:
installation-folder\agt4\sql
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 # |
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 # |
For details about how to delete the R4QHITACHIPROCSPDATABASES Stored Procedure, see I. Deleting the R4QHITACHIPROCSPDATABASES Stored Procedure.
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:
Performance Management outputs its operating status to a set of proprietary log files called the common message log. By default, the common message log consists of two 2,048 KB files. Setting is required only if you want to change the default file size.
For details, see the chapter on installation and setup in the Job Management Partner 1/Performance Management Planning and Configuration Guide.
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:
For details, see 2.4.1 Changing the storage location of performance data.
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.
To specify the connection-target PFM - Manager:
jpcconf mgrhost define -host host01
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.