Appendix: Administering PeopleSoft Databases on Microsoft SQL Server

This appendix discusses:

Click to jump to parent topicServer Options

This section discusses:

Click to jump to top of pageClick to jump to parent topicDelivered Configuration

The PeopleSoft server configuration parameters are initially set to Microsoft SQL Server defaults. It's a good practice to review the parameters and modify them to your site requirements if necessary. Use the file PS_HOME\scripts\spconfig.sql on your database server to keep track of your changes. This file is used by the database configuration wizard when installing a PeopleSoft database.

Note. Don’t use “priority boost” when running additional applications like PeopleSoft Process Scheduler on your database server machine.

See Also

Microsoft SQL Server documentation

Click to jump to top of pageClick to jump to parent topicAccess ID

PeopleSoft does not recommend the use of the SQL Server system administrator login sa as an access ID.

Click to jump to top of pageClick to jump to parent topicService Packs and Quick Fix Enhancements (QFE)

PeopleSoft always runs certifications on the latest SQL Server service packs as they become available. Service packs contain large number of improvements and have been tested extensively by Microsoft.

A QFE is a fix intended to solve a specific problem that's usually documented in a Microsoft Knowledge Base (KB) article. PeopleSoft doesn't run certification tests for any particular SQL Server QFE, but considers them to be supported when they're recommended by Microsoft to solve specific problems. However, to install a QFE, PeopleSoft recommends appropriate testing before applying it to a production environment. It's important to take into consideration that a QFE is an enhancement targeted to solve a specific problem. “Secondary effects” as a result of its installation can be determined only with proper testing.

PeopleSoft does not distribute SQL Server QFE software; please contact Microsoft to determine if a QFE is required, and for instructions on how to download the software.

Click to jump to parent topicRequired Database Configuration

PeopleSoft applications require a standard database configuration that's not optional and should not be changed. This section discusses the options that you must enable:

Click to jump to top of pageClick to jump to parent topicANSI Nullability

Make sure your database uses ansi nulls by default. This is a database option that will be set up at installation time. The configuration occurs automatically when using the database configuration wizard and is enabled by the SQL script addobj.sql when installed manually.

The following line shows how to enable this parameter at SQL Server 2000 using query analyzer:

EXEC sp_dboption databasename, 'ansi null default', true

Click to jump to top of pageClick to jump to parent topicQuoted Identifier, Arithabort, and Functional Index

PeopleSoft uses computed columns that allow the creation of functional indexes. A functional index is an index created to keep uniqueness in a table when the number of keys exceeds the SQL Server limits of 16 key columns for an index.

PeopleSoft implements the functional index creating an index over a computed column. The computed column MSSCONCATCOL is the result of adding up all the key columns required to keep uniqueness. What makes a functional index special is that it's required only when the number of key columns exceeds the SQL Server 2000 limits, which is 16 key columns maximum for an index.

In order to create indexes on computed columns, SQL Server 2000 requires the Quoted Identifier option to be enabled in the database. This is the default configuration, but this option could be overridden as a connection option from any client. If you are using Query Analyzer to run SQL scripts, look at Tools, Options, Connection Properties on your Query Analyzer menu and make sure the Quoted Identifier option is selected, which will activate it for that particular connection.

Another important option that needs to be enabled to operate computed columns is the database property Arithabort. Make sure this option is enabled for your PeopleSoft database. Both options are explicitly set during installation automatically by the database configuration wizard or when running the script createdb.sql at the database installation.

See Also

Microsoft SQL Server documentation

Click to jump to top of pageClick to jump to parent topicDatabase Collation Settings

The use of the right collation is very important for PeopleSoft applications. PeopleSoft delivers its applications with a standard collation of Latin1_General_Bin on SQL Server 2000. This collation was selected for being compatible with the binary sort order used on SQL Server 7.

However, PeopleSoft supports other sort orders with some applications. The application installation manual will point out whether this is permitted for a particular application. The sort order supported must be Kana sensitive, case sensitive and accent sensitive. Therefore a collation such as Latin1_CS_AS_KS is supported. Note that the Latin1_General_Bin collation also satisfies this requirement.

Please consult your Enterprise PeopleTools installation guide and the application installation manual for further details on the collation configuration required for your database server.

For environments running English-only databases and languages covered by the Latin1 character set (such as Western European languages), PeopleSoft recommends the collation delivered as default in the PeopleSoft installation scripts. The database collation is set when running the creatdb.sql script at installation time. The script runs automatically when you use the database configuration wizard. It is a requirement to run the script when installing the database manually.

See Enterprise PeopleTools 8.49 Installation for Microsoft SQL Server, PREPARING FOR INSTALLATION.

Click to jump to parent topicOther Considerations

This section discusses:

Click to jump to top of pageClick to jump to parent topicRecovery Model

PeopleSoft recommends using the Full recovery model on SQL Server databases. All production databases should use this model for better reliability. The PeopleSoft applications do not require any particular recovery model but using the Full recovery model is considered the best practice.

See Also

Microsoft SQL Server documentation

Click to jump to top of pageClick to jump to parent topicNested Triggers

Some PeopleSoft applications take advantage of database triggers. Make sure that the nested triggers option is enabled for the database server hosting the PeopleSoft databases. You can use sp_dboption or Enterprise Manager to enable this option on the server.

Click to jump to top of pageClick to jump to parent topicAuto Create Statistics and Auto Update Statistics

Microsoft SQL Server 2000 enables you to create statistics and update them automatically. It’s recommended that you leave this feature enabled for PeopleSoft applications.

However, sometimes you should disable these features for a particular table; for example, if you want to modify the sample size used to create the statistics, you need to do so manually.

Another example is when the data varies considerably, and the statistics that are created are not accurate. For this you might want to disable auto create statistics and auto update statistics manually, and adjust the statistics as needed.

In general, auto create statistics and auto update statistics should be enabled for most of the tables in your database unless you need to disable the feature for specific reasons.

See Also

Microsoft SQL Server documentation.

Click to jump to top of pageClick to jump to parent topicAutomatic File Growth

SQL Server 2000 enables you to let a database file grow automatically when it's full. PeopleSoft recommends that you leave this feature enabled; however, it should be used with caution. When the database server is in the process of increasing the size of a data file, all other activities in the server stop, which can cause server performance problems. Ideally, in a well-tuned environment this won’t occur — properly sizing the data files eliminates the performance problem.

When installing PeopleSoft applications using the database configuration wizard, you have the option to let the data files grow until there's no more space on the storage devices. When installing the database manually, it's necessary to manually review and modify the file PS_HOME\scripts\createdb.sql; it includes the following lines that the database administrator should review and update with appropriate values: 

-- ALTER DATABASE <DBNAME> MODIFY FILE (NAME = <DATANAME>, MAXSIZE = UNLIMITED)
-- go
-- ALTER DATABASE <DBNAME> MODIFY FILE (NAME = <LOGDATANAME>, MAXSIZE = UNLIMITED)
-- go

Click to jump to top of pageClick to jump to parent topicAutoshrink

For PeopleSoft databases, make sure the autoshrink option is disabled. In very specific scenarios it will be necessary to “shrink” a database file. This should be done with caution; in general, it's a better practice to do it manually.

Click to jump to top of pageClick to jump to parent topicRead Committed Snapshot Isolation

PeopleSoft applications use a pessimistic implementation of the READ COMMITTED isolation level, which is the default for SQL Server 2000. SQL Server 2005 supports optimistic concurrency control with its new implementation of the READ COMMITTED isolation level, which is called READ COMMITTED SNAPSHOT.

Optimistic concurrency control has these benefits:

Disabling Read Committed Snapshot Isolation

Under normal circumstances, this feature should always remain enabled. You can disable it if a critical problem is identified.

Before disabling the feature:

The command to disable the feature is:

ALTER DATABASE dbname
     SET READ_COMMITTED_SNAPSHOT  OFF

The command to change the database to single user mode is:

ALTER DATABASE dbname
     SET SINGLE_USER  ON

See Also

Microsoft SQL Server documentation.

Click to jump to top of pageClick to jump to parent topicFile Management

PeopleSoft recommends the use of separate physical disks for the Microsoft SQL Server data files. Ideally, databases like master, tempdb, and application databases should be on separate disks, as should the operating system paging file (in case you run some additional applications other than the database software). As a general rule, the more spindles the better; always choose more smaller-size disks over fewer larger-size disks. If you don't have separate physical disks for each of the datafiles, you should at least place your tempdb, data, and log files on separate physical devices. Make sure that your log device is using its own disk controller and is not accessed by any other device.

Note. You should always consider disk fault tolerance when deciding how you want the database server configured.

Using Filegroups

Microsoft SQL Server 2000 maps each database using a set of operating system files. All database objects and data are stored within these files. A database can have one or more data files (.mdf and .ndf extensions) and transaction log files (.ldf extension).

Filegroups are logical containers that enable the database files (.mdf, .ndf, and .ldf) to be grouped together for administrative and data placement purposes. While a filegroup can contain more than one database file, each database file can be a member of only one filegroup.

Note. While the number and placement of data files may have an impact on system performance, the number and organization of filegroups has no direct correlation to performance.

Because of the large number of tables and the complex IO patterns of a PeopleSoft database, you must consider the placement of the data files carefully to maximize performance. The best approach is to use a RAID-10 disk configuration and spread the data over as many disks as possible. Use a large number of smaller sized disks, rather than a small number of larger disks.

In addition to the main database, give careful consideration to the configuration and placement of the SQL Server Tempdb database, because PeopleSoft applications use it heavily. Given the unusual input/output characteristics of this database (on average, 50% read, 50% write), you should create your Tempdb database on a separate RAID-10 disk with multiple database files. Generally, it's appropriate to make the number of data files equal to the number of processors used.

See Also

Microsoft SQL Server documentation

Microsoft Windows documentation

Click to jump to top of pageClick to jump to parent topicTempdb

PeopleSoft heavily uses the tempdb database; consider moving tempdb to its own set of disks or disk array. Also, the size of tempdb should be adjusted to be approximately 15% to 20% of the total size of your PeopleSoft database.

Another good practice is to distribute tempdb into several data files of the same size; as a guideline you might want to have one file for each processor assigned for SQL Server. If possible, spread these datafiles on a high performance disk array.

Moving Tempdb

During installation of Microsoft SQL Server, tempdb is put in the default data directory. If you wish to move it to a separate disk and resize it, the following scripts are an example of how this can be accomplished: 

-- To find out where tempdb resides:
-- The following stored procedure will show on which drive tempdb
-- data and log files reside.
sp_helpdb tempdb

-- This example script moves tempdb to drive f:
alter database tempdb
modify file ( name = ’tempdev’ , filename = ’f:\data\tempdb.mdf’ )
go
alter database tempdb
modify file ( name = ’templog’ , filename = ’f:\log\tempdblog.ldf’ )
go

-- This example script resizes the tempdb data file to 500MB
-- and the tempdb log file to 500MB
alter database tempdb
modify file ( name = ’tempdev’ , size = 500MB )
go
alter database tempdb
modify file ( name = ’templog’ , size = 500MB )
go

Click to jump to top of pageClick to jump to parent topicTrace Flags

When reporting problems to customer support, it is advisable to generate files with traces of the problem that you want to report. Use the trace flags incorporated in PeopleSoft tools to generate these files. The trace flags are accessible through the configuration files for the Process Scheduler and the Application Server and through the selection of several flags when using the PeopleSoft Configuration Manager on your developer workstation.

Use “TRACESQL=63” to display the SQL statements executed when using PeopleSoft applications. This trace flag is very useful to identify problems in the SQL being executed against a database that hosts a PeopleSoft application.

The trace flag will show the details about the execution of a sql statement such as: if the statement was recompiled or if it’s using an old query plan, the time it took to execute, the time between executions, if the SQL was parametrized, among other things.

Once you find the SQL with problems, you can use the SQL Server profiler to reproduce this outside of your PeopleSoft application.

Note. Keep in mind that tracing could affect performance considerably, and you won't be able to reproduce some problems with tracing enabled.

See Also

Setting Application Server Domain Parameters

Using the PSADMIN Utility

Using PeopleSoft Configuration Manager

Click to jump to top of pageClick to jump to parent topicDatabase Monitoring

Available through the configuration files for the Process Scheduler and the Application Server, the activation of the EnableDBMonitoring option allows you to populate context information of the query executed against the database. This is particularly useful to gather information about the PeopleSoft user running a particular SQL statement.

Examples of SQL Statements

The following are examples of SQL statements that will display the context information of a user once EnableDBMonitoring is selected. Modify the scripts according to your needs. 

--SQL to get OPRID only
select (substring(cast(context_info as varchar(128)),0,PATINDEX('%,%',cast(context_info as varchar(128)))))
from master..sysprocesses where spid=<spid>


--SQL to select the network id if it is there
select substring(cast(context_info as varchar(128)),
 len(substring(cast(context_info as varchar(128)),0,PATINDEX('%,%',cast(context_info as varchar(128)))))+2,
 PATINDEX('%,%',substring(cast(context_info as varchar(128)),len(substring(cast(context_info as varchar(128)),0,PATINDEX('%,%',cast(context_info
as varchar(128)))))+2,128))-1)
from master..sysprocesses where spid=<spid>

--SQL to select network host
select 
substring(substring(cast(context_info as varchar(128)),
len(substring(cast(context_info as varchar(128)),0,PATINDEX('%,%',cast(context_info as varchar(128)))))+2
+PATINDEX('%,%',substring(cast(context_info as varchar(128)),len(substring(cast(context_info as varchar(128)),0,PATINDEX('%,%',cast(context_info
as varchar(128)))))+2,128))
,128),0,PATINDEX('%,%',substring(cast(context_info as varchar(128)),
len(substring(cast(context_info as varchar(128)),0,PATINDEX('%,%',cast(context_info as varchar(128)))))+2
+PATINDEX('%,%',substring(cast(context_info as varchar(128)),len(substring(cast(context_info as varchar(128)),0,PATINDEX('%,%',cast(context_info
as varchar(128)))))+2,128))
,128))) from master..sysprocesses where spid=<spid>

--SQL to select App server domain
select reverse(substring(reverse(cast(context_info as varchar(128))),0,PATINDEX('%,%',reverse(cast(context_info as varchar(128))))))
from master..sysprocesses where spid=<spid>

--SQL to select all the information trimming blanks
select 
substring(cast(context_info as varchar(128)),0,128-PATINDEX('%,%',reverse(cast(context_info as varchar(128))))+10)
from master..sysprocesses where spid=<spid>