DBMS Installation and Configuration Hints
Usually your DBA already installed the DBMS software for you and he is responsible to make sure that all load requirements
can be met by the configuration of the system. However, we wanted to share our experiences with the installation and especially
the configuration of the DBMS system so you experience the best performance possible.
Microsoft SQL Server
When installing MS SQL Server the following settings must be considered.
Server Settings
Maximum Degree of Parallelism (MAXDOP)
It is strongly recommended to set MAXDOP to 1 for all Product 360 Schemas/Databases since Product 360 is a high transaction multi-user OLTP application.
Number of TempDB Files
Since all Product 360 Schemas/Databases use the SNAPSHOT isolation mode of SQL Server a high temp DB throughput is required. We recommend to have a tempdb file for each physical CPU core (till up to 8 files).
See also the corresponding knowledge base article from Microsoft: https://technet.microsoft.com/en-us/library/ms175527(v=sql.105).aspx
Server Collation
While setup the Microsoft SQL Server for the "Server Collation" setting use Latin1_General and additional the options Case sensitive and Accent sensitive (Latin1_General_CS_AS).
Screenshot: Setup SQL Server 2012
Database User Settings
Database setup user
The users which install the Product 360 schemas must have set the roles DBCreator and Public.
Screenshot: SQL Server Management Studio 2012 login properties dialog
Database application users
All database users which are used for the database access of the Product 360 applications must always have English as standard language. Additionally uncheck the Enforce password policy checkbox.
Note: If integrated authentication should be used (see property "db.integrated.security" in server.properties file) "Windows authentication" has to be enabled for the database user.
Screenshot: SQL Server Management Studio 2012 login properties dialog
Pay attention that the maximum size of memory of the SQL Server process is fixed. Otherwise the system may freeze possibly very fast. Also check the minimum size of memory and pay attention that the value is not greater than the available central memory. More Information
Authentication Mode
In order to authenticate to the database with the created SQL Server user, you have to check if the sever authentication mode accepts SQL Server users. Therefore connect to the management studio and right click on the top node and click on properties. Select security and check the server authentication mode.
Named-Instance Support
Informatica Product 360 can connect to MS-SQL Server named instances by using their port. Make sure that the MS SQL Server Network Configuration is correct. In section IPAll the TCP Port property must be set to a free port (Default is 1433), and TCP Dynamic Ports must be disabled as shown in the screenshot.
Screenshot: MS SQL Server Configuration Manager 2012, showing the properties of the TCP/IP Network configuration
Screenshot: MS SQL Server Configuration Manager 2012, showing the properties of the TCP/IP Network configuration
Make sure that you restart the SQL service after that change.
Oracle
Consider these settings during installation of the database instance.
Server Settings
Enable at least the feature "Enterprise Manager Repository"
Set database character set to Unicode (AL32UTF8)
Set the national specific character set to AL16UTF16 - Unicode UTF-16 Universal character set
Set standard increase redo-log file size to 1024 MB (each), have at least three redo-logs
Set standard language to American.
Create a tnsnames.ora file at <OracleDBInstanceFolder>/NETWORK/ADMIN and map service names to the connect descriptors for the local naming method
Disable password expíration, otherwise ensure that passwords for Product 360 schemas never expire
Ensure have enough space for TEMP tablespace
Degree of Parallelism (DOP)
It is strongly recommended to set PARALLEL_DEGREE_LIMIT to 1 for all Product 360 Schemas/Databases since Product 360 is a high transaction multi-user OLTP application.
Recommended initialization parameters
Recommended init.ora settings for Microsoft Windows (assuming 24G RAM with 8 Core CPU Oracle server).
|
Name |
Value |
Description |
cursor_sharing |
EXACT |
EXACT ensures that Oracle computes the most efficient execution plans. This causes a higher CPU consumption but a better performance on the execution. If the CPU consumption is to high, and the data size doesn't change significantly during the time, it may be a good idea to change it to FORCE. This ensures that the execution plans are reused and the CPU consumption remains low. |
db_block_checking |
FALSE |
To avoid additional overhead. |
db_block_size |
8192 |
Default |
db_domain |
||
db_writer_processes |
CPU cores / 8 |
Oracle guideline. |
dynamic_sampling |
2 |
To enable dynamic sampling on tables without statistics (ReportStore/ReportStoreTemp). |
filesystemio_options |
SETALL |
|
job_queue_processes |
10 |
|
nls_language |
AMERICAN |
|
nls_territory |
AMERICA |
|
open_cursors |
3000 |
|
optimizer_capture_sql_plan_baselines |
FALSE |
Setting this parameter to false will make SQL plan management to not recalculate the execution plan for each repeatable SQL statement. |
optimizer_use_sql_plan_baselines |
FALSE |
Setting this parameter to false will make SQL plan management not to capture the history for the SQL statements being parsed or reparsed. |
parallel_adaptive_multi_user |
TRUE |
|
parallel_degree_limit |
1 |
|
processes |
1000 |
Sufficient Oracle processes are allocated to support connection, parallel thread, internal process and other usage. |
recyclebin |
OFF |
|
remote_login_passwordfile |
EXCLUSIVE |
|
sessions |
1000 |
|
undo_management |
AUTO |
|
tns_listener |
TCP Protocol |
|
workarea_size_policy |
AUTO |
To be determined automatically by Oracle. |
Database User Settings
During Product 360 schema installation you will need the SYSTEM user.
DBA Tasks
Your DBA must take care of the following topics:
Always monitor I/O waits
Redo Log Checkpoints/Switching
Frequent log switching decreases performance
Redo log size needs to be sized appropriately
Archive Logs
When archive area is full, all processes in the DB stops until archive logs are backup and the archive backed up logs deleted to free up space
Cache Hit Ratio
Should be at least a 95% cache hit