1.3 Creating and Maintaining an SQL Database

1.3.1 The Database Is Your Responsibility

IMPORTANT:Your organization is entirely responsible for the Retain database.

Micro Focus strongly recommends that it be installed, configured, tuned, and maintained on one of the supported database platforms by qualified database administrators. See SQL Database in Retain 4.9.2: Planning.

Providing adequate instructions for the above tasks on the various Retain-supported database platforms is well beyond the scope of this guide.

The setup and configuration instructions in Example Instructions for Your Reference are only for proof-of-concept deployment use.

1.3.2 The Database’s Role in Retain

Retain stores header data and pointers to the Retain datastore in an SQL database.

Retain administrators are responsible to ensure that Retain databases are properly installed, configured, tuned, and maintained.

1.3.3 Never Use NFS Shares in a Database System.

NFS locking is insufficient for database requirements and results in corruption.

1.3.4 If You Need a Secure Database Connection

You can secure the connection between Retain and a remote MySQL or MariaDB database. (Securing connections to other supported database types is not currently supported.) See Securing the Database Connection in the Retain 4.9.2: Planning guide.

1.3.5 Basic Setup Overview

Setting up the Retain database involves the following general steps:

  1. Download a supported database platform. See SQL Database in Retain 4.9.2: Planning.

  2. Install it on one of the following, according to your organization’s needs:

    • A dedicated database server (recommended).

    • The Retain server itself for a small, all-in-one deployment.

  3. Create a database for Retain to use.

  4. Create a user/role with ownership rights to the database that you just created.

IMPORTANT:Deploying Retain requires that you provide:

  • The database name

  • The database user’s/role’s name

  • The user’s/role’s password

1.3.6 Example Instructions for Your Reference

The following sections contain sample instructions and tips that might be useful if you are setting up a proof-of-concept Retain deployment.

They are not adequate for deploying Retain in a production environment, which requires assistance from a qualified database administrator.

MySQL

  1. Download and install the latest MySQL server and client and configure according to local system needs.

    1. Install the server: rpm -ivh MySQL-server-version.sles11.x86_64.rpm

    2. install the client: rpm -ivh MySQL-client-version.sles11.x86_64.rpm

    3. Modify /usr/my.cnf, to move the database to a data volume, by adding the line datadir=/path/to/where/you/want/the/MySQL/data

    4. Find the root password in /root/.mysql_secret

      cat /root/.mysql_secret

    5. Start MySQL:

      rcmysql start

    6. Change the root password:

      /usr/bin/mysql_secure_installation

  2. Log in to MySQL using the client:

    mysql -u root -p

  3. Create the database: * This is critical. Not storing the database in UTF-8 format guarantees irreparable corruption for non-US-ASCII characters. *

    CREATE DATABASE retain DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_unicode_ci;

    1. If you make a mistake, enter this command:

      DROP DATABASE retain;

    2. Then repeat step 3 to create the database.

  4. This step depends on which version of MySQL you are using.

    • For MySQL 5.x: Retain needs to be configured with a user account that you grant full rights to the Retain database.

      IMPORTANT:Micro Focus strongly recommends against using the ‘root’ account.

      While logged in to MySQL, enter the following commands, replacing UserName and Password with the desired user name and password.

      • GRANT ALL PRIVILEGES ON retain.* TO 'UserName'@'%' IDENTIFIED BY 'Password';

        To restrict the connection to a specific IP address, replace the percentage sign (%) with the address.

      • GRANT ALL PRIVILEGES ON retain.* TO 'UserName'@'localhost' IDENTIFIED BY 'Password';

      For example, if the database user's name is retainuser, the password is P@ssword1, and you want database access restricted to 192.168.1.142:

      • GRANT ALL PRIVILEGES ON retain.* TO 'retainuser'@'192.168.1.142' IDENTIFIED BY 'P@ssword1';

      • GRANT ALL PRIVILEGES ON retain.* TO 'retainuser'@'localhost' IDENTIFIED BY 'P@ssword1';

    • For MySQL 8.x: You need to create the user account and then grant it full rights to the Retain database.

      While logged in to MySQL, enter the following commands, replacing UserName and Password with the desired user name and password.

      • CREATE USER ' UserName' IDENTIFIED WITH mysql_native_password BY ' Password';

      • CREATE USER ' UserName'@'localhost' IDENTIFIED WITH mysql_native_password BY ' Password';

      • GRANT ALL PRIVILEGES ON retain.* TO ' UserName'@'localhost';

      • GRANT ALL PRIVILEGES ON retain.* TO ' UserName'@'%';

      If you run the mysql-database on the same server as the Retain-Server you can add the following option to your my.cnf-configuration:

      [mysqld]default-authentication-plugin=mysql_native_password

MS SQL Server 2016, 2017

  1. Install as a default instance, with Latin1_ General_CI_AS as the encoding standard.

  2. Enable SQL Server Authentication in addition to Windows Authentication.

  3. Once installed, verify the server is listening on port 1433. At the command prompt, run:

    telnet <IP address> 1433

  4. Ensure TCP/IP is enabled - Run SQL Server Configuration Manager

  5. Expand SQL Server Network Configuration

  6. Protocols for MSSQLSERVER

  7. Enable TCP/IP

  8. Run SQL Server Management Studio, connect to the Server.

  9. Under Security/Logins, right-click and select create Login, (for example Retainuser) that uses SQL Server authentication.

  10. Assign a password.

  11. Right click Databases, create a new database named Retain and assign Retainuser as the owner.

ORACLE

Retain supports the usage of Oracle databases on all platforms. (For more information, see Database Platforms in the Retain 4.9.2: Planning guide.)

Micro Focus strongly recommends that you employ an experienced Oracle DBA to install, maintain, and tune Oracle.

The instructions below serve as a guideline, but may result in poorly performing, insecure environments.

Oracle Installation instructions vary widely depending on the version, OS, storage configuration, clustering, and so on. There is no one-size-fits-all.

This is also true of other SQL Server products, but especially for Oracle, which is why relying on a DBA is so critical.

Using Oracle with Retain requires the following tasks:

Installing the Oracle Server

Install your Oracle Server normally, according to your standards and practices. One option is to use the Universal Installer, specifying a custom installation, choose the type of Oracle Server to install, and to Install Files Only

Setting Up a TCPIP Listener

Next use the Net Configuration Assistant (or manually edit the Oracle Listener configuration) to create a TCP based listener on port 1521. This allows TCPIP clients to connect to Oracle and is required for Retain.

Creating a Database Named Retain

Next, either use the Database Configuration Assistant or manually create the database with your favorite tool. Regardless, you want to create a database and sid, named Retain, and create your SYSTEM account

An example of a create statement (do not use as is):

connect "SYS"/"&&sysPassword" as SYSDBA
set echo on
spool C:\oracle\product\10.2.0\admin\Retain\scripts\CreateDB.log
startup nomount pfile="C:\oracle\product\10.2.0\admin\Retain\scripts\init.ora";
CREATE DATABASE "Retain"
MAXINSTANCES 8
MAXLOGHISTORY 1
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
DATAFILE 'C:\oracle\product\10.2.0\oradata\Retain\system01.dbf' SIZE 300M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE 'C:\oracle\product\10.2.0\oradata\Retain\sysaux01.dbf' SIZE 120M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
SMALLFILE DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE 'C:\oracle\product\10.2.0\oradata\Retain\temp01.dbf' SIZE 20M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
SMALLFILE UNDO TABLESPACE "UNDOTBS1" DATAFILE 'C:\oracle\product\10.2.0\oradata\Retain\undotbs01.dbf' SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
CHARACTER SET AL32UTF8
NATIONAL CHARACTER SET AL16UTF16
LOGFILE GROUP 1 ('C:\oracle\product\10.2.0\oradata\Retain\redo01.log') SIZE 51200K,
GROUP 2 ('C:\oracle\product\10.2.0\oradata\Retain\redo02.log') SIZE 51200K,
GROUP 3 ('C:\oracle\product\10.2.0\oradata\Retain\redo03.log') SIZE 51200K
USER SYS IDENTIFIED BY "&&sysPassword" USER SYSTEM IDENTIFIED BY "&&systemPassword";
spool off

The most critical items to note are the CHARACTER SET (AL32UTF8 aka UTF8), and the NATIONAL CHARACTER SET (AL16UTF16). If using a GUI based installed, make sure that you set these because they are easily missed. (They are located on the Encoding tab of the 10th step or so in the wizard).

Creating a User Account to Access the Retain Database

Finally, using the SYSTEM account, connect to the database, create a user, and grant full system privileges to the account. An example is shown below:

CREATE USER user-name IDENTIFIED BY password;

grant dba to user-name;

Restart everything, and verify you can telnet on the TCP LISTENER's PORT (1521 if as above).

If a restricted user is desired, Retain requires a user with the following role:

  • RESOURCE CONNECT

And the following database privileges to the Retain database:

  • CREATE PROCEDURE

  • CREATE SESSION

  • CREATE SYNONYM

  • CREATE TABLE

  • CREATE TYPE

  • CREATE VIEW

Tuning Oracle

Tuning Oracle is well beyond the scope of this manual and requires Oracle's online guides, as well as an experienced DBA or extensive training.

Postgres 10.9

When creating a PostgreSQL database for use by Retain, specify UTF8 encoding.

If the Retain Server is on a different machine, you must configure PostgreSQL to accept connections from that machine's IP address.

  1. Connect to PostgreSQL using its psql utility.

  2. Create a new database and user for use by Retain. The example below creates a user named "retainuser":

CREATE DATABASE retain WITH OWNER = retainuser ENCODING = 'UTF8' TABLESPACE = pg_default CONNECTION LIMIT = -1;

MariaDB 10.x

You install MariaDB using YaST.

Log into MariaDB, and set up the Retain database and user.

  1. Create the database: * This is critical. Not storing the database in UTF-8 format guarantees irreparable corruption for non-US-ASCII characters. *

    CREATE DATABASE retain DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_unicode_ci;

    1. If you make a mistake, enter this command:

      DROP DATABASE retain;

    2. The repeat this step to create the database.

  2. Retain needs a user account created that has full rights to the Retain database.

    IMPORTANT:Micro Focus strongly recommends against using the ‘root’ account.

    While logged in to MariaDB, enter the following commands, replacing UserName and Password with the desired user name and password

    Both commands are needed. The ‘%’ is a wildcard. If you want to restrict the connection to a specific IP address, specify the address instead of the ‘%’ sign.):

    GRANT ALL PRIVILEGES ON retain.* TO 'UserName'@'%' IDENTIFIED BY 'Password';

    GRANT ALL PRIVILEGES ON retain.* TO 'UserName'@'localhost' IDENTIFIED BY 'Password';

    For example, if the database user's name is "retainuser" and the password is "P@ssword1":

    GRANT ALL PRIVILEGES ON retain.* TO 'retainuser'@'%' IDENTIFIED BY 'P@ssword1';

    GRANT ALL PRIVILEGES ON retain.* TO 'retainuser'@'localhost' IDENTIFIED BY 'P@ssword1';

Further Database Tuning

You are responsible to tune the SQL Database Server. Tuning database servers for performance often requires an experienced DBA.

You are also responsible for backing up, managing storage, and ensure that the database is fault tolerant.

MySQL

You should periodically optimize the Retain tables in order to achieve optimum query and update performance. This can have a big impact on the performance of archive jobs, deletion jobs, etc. It is also a good idea to optimize the memory settings for MySQL.

Backing Up the Database

Be sure to back up (dump) the database before performing any maintenance. If on a VM, you can alternatively back up the VM or take a snapshot.

Optimizing Tables

WARNING:This should be done during a maintenance window because it locks the tables and can take hours to complete (or days for larger databases).

The time required is a function of the number of records in the tables, the power of the underlying hardware, and the RAM available. The more innodb_buffer_pool_size you can give to MySQL, the more tablespace it can load into RAM as explained here.

Like hard disks, database tables' indexes can become fragmented resulting in poor performance. Optimizing tables can dramatically improve archive job and deletion job performance.

At the command prompt, type: mysqlcheck -o -u root -p retain

  • "-o" instructs it to optimize all tables in the database.

  • "-u root" specifies the user "root".

  • "-p" causes it to prompt for the retain database password.

  • "retain" instructs it to run against the "retain" database (or whatever you named it during installation - "retain" is the default).

The command above optimizes all Retain tables at once. However, many are static and would not necessarily be subject to fragmentation issues.

Paste the following query into the MySQL prompt after logging into MySQL. This optimizes the tables one at a time.

These are the Retain 3.x tables you might consider optimizing:

OPTIMIZE TABLE retain.Audit;
OPTIMIZE TABLE retain.s_AddressBookCache;
OPTIMIZE TABLE retain.t_abook;
OPTIMIZE TABLE retain.t_document;
OPTIMIZE TABLE retain.t_dsref;
OPTIMIZE TABLE retain.t_message;
OPTIMIZE TABLE retain.t_message_attachments;
OPTIMIZE TABLE retain.t_message_properties;
OPTIMIZE TABLE retain.t_message_recipients;
OPTIMIZE TABLE retain.t_recipient;

These are the Retain 2.x tables you might consider optimizing if you are planning on migrating to Retain 3.x in the next few days or weeks:

OPTIMIZE TABLE retain.Audit;
OPTIMIZE TABLE retain.Document;
OPTIMIZE TABLE retain.Email;
OPTIMIZE TABLE retain.t_Node;
OPTIMIZE TABLE retain.t_attachment;
OPTIMIZE TABLE retain.t_msg_properties;
OPTIMIZE TABLE retain.t_recp_properties;
OPTIMIZE TABLE retain.t_recipients;

You can check on the progress of the queries by opening a separate terminal window and launching the following command:

watch -n 1 "mysqladmin -u root -p[password] processlist"

(note: no space between "-p" and your mysql root user password!)

NOTE:Retain uses the innoDB engine in MySQL, which does not support the optimize option; however, running this option automatically performs a re-create action on the tables, which eliminates the fragmentation. There might be better ways to accomplish this, so this information is supplied to point you in the right direction.

For more detailed information and other mysqlcheck options, you may want to research this on the Internet. For your convenience, here are a couple of helpful articles:

4.5.3. mysqlcheck - A Table Maintenance Program

MySQL Back to Basics: Analyze, Check, Optimize, and Repair (written in 2008)

MySQL Reference Manual on getting a dump of a corrupted database This is sometimes the only option for recovering data from a corrupted INNODB database.

Memory Optimization

Memory for your innoDB can be configured in /etc/my.cnf (Linux) or on Windows, in the [drive]:\Program Files\MySQL\MySQL Server 5.x/my.ini file (where "x" is the MySQL version).

You'll find the following section in your my.cnf and there are identical lines in the my.ini on Windows although they may not be in this same order. The settings you may want to consider modifying are in red. This is followed by indented explanations in blue and are additional comments made for this article - those additional comments are not found in the my.cnf or my.ini.

Be sure to stop tomcat Starting and Stopping the Retain Server before working with MySQL.

# Uncomment the following if you are using InnoDB tables. If it does not exist, create it. innodb_file_per_table

The "innodb_file_per_table" is not included in the default my.cnf or my.ini and has to be typed in manually. It causes MySQL to create an .idx file for every table in the database. The benefits of this is up for debate. Some swear by it because they feel it eliminates contention for a single file containing all the tables (ibdata1); however, others have written that it is fast at first and then bogs down. We have found that, for Retain databases, it seems to positively impact performance. Do your own research and determine what is best for your system.

#innodb_data_home_dir = /var/lib/mysql
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = /var/lib/mysql
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 4G

This allocates buffer memory for MySQL into which it can load table and index data. Obviously, accessing RAM is faster than disk, so the more memory you can give for the buffer pool, the more MySQL can load into the buffer. MySQL uses memory for other things, so if you have 6G to play with, you probably do not want the buffer pool to be larger than 4G. You'll have to experiment on your own system.

A helpful tool that is free is called mysqltuner.pl. It is a perl script that runs against your database to help you tune it's memory, security, etc. To get it, create a directory on your Linux server and change to that directory. Then type: wget mysqltuner.pl. Once it is down donwloading (takes 1 second), type: chmod +x *.pl so that it can be executed; then, execute it by typing: ./mysqltuner.pl.

innodb_additional_mem_pool_size = 204M

From what we've seen, this should be set to 5% of the buffer pool size. But, again, do your research.

# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 1024M

This affects the ib_logfile0 and ib_logfile1 files in your MySQL data directory. These files hold statements/transactions. The maximum size these can be set to (regardless of your buffer pool size) is 1024M (or 1G, totaling 2 GB for all log files together) for MySQL 5.5 and 255 G (total for all log files together can be up to 512 GB) for 5.6. For more information check out the MySQL website here. There is also some discussion on what values you should set the log files to (see https://www.percona.com/blog/2008/11/21/how-to-calculate-a-good-innodb-log-file-size/. The maximum size these can be set to is 1024M.

IMPORTANT:If you change this setting, it would be a good idea to flush the existing logs (ib_logfile0 and ib_logfile1). To do this, take the following steps:

a) Log into MySQL: mysql -u root -p

b) From the MySQL prompt, type: SET GLOBAL innodb_fast_shutdown=0;

Then type "quit" to log out of MySQL.

c) Shut down MySQL: rcmysql stop

d) Rename the ib_logfile0 and ib_logfile1 files.

g) Start up MySQL: rcmysql start

h) If MySQL starts up successfully, you can delete the ib_logfile* files.

If you do not delete or move the exisiting ib_logfile0 and ib_logfile1 files from the MySQL data directory when changing the logfile size, MySQL does not load.

innodb_log_buffer_size = 8M

The log buffer allows transactions to run without having to write the log to disk before the transactions commit. If the transactions are large, then making the log buffer larger saves disk I/O. There is a status called "innodb_log_waits" in MySQL. This shows the number of times a wait was required for it to be flushed before continuing. If you have any waits, then the buffer size is too small. Here is the query you can run on that status: SHOW GLOBAL STATUS LIKE 'innodb_log_waits';

#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50

To enable any one of those settings, remove the hash mark ("#") preceding it. The following is an example for a typical system that has 16G of total RAM, where 2G is allocated for the Linux OS and 8G to Tomcat. The amount allocated to Tomcat versus the database varies per customer. A system where a lot of queries/searches are made in Retain for items, or that runs multiple workers on the Retain Server itself, allocates more memory to Tomcat, like in this example; otherwise, a customer might want to favor MySQL a bit and give Tomcat a little less.

Changes to the my.cnf do not take affect until after MySQL is restarted. Again, if you changed the log file size setting, you must follow the steps listed above or MySQL fails to load.

Once MySQL loads successfully, you can start tomcat Starting and Stopping the Retain Server.