` entry. Replace `postgresql.hibernate.cfg.xml` with `mysql5.hibernate.cfg.xml`:
From:
<config-file>system/hibernate/postgresql.hibernate.cfg.xml</config-file>
To:
<config-file>system/hibernate/mysql5.hibernate.cfg.xml</config-file>
3. Save and close the file.
4. (MariaDB only) Open `mysql5.hibernate.cfg.xml`.
5. Find the driver class and change it from MySQL to MariaDB:
From:
<property name="connection.driver_class">com.mysql.jdbc.Driver</property>
To:
<property name="connection.driver_class">org.mariadb.jdbc.Driver</property>
#### Replace the audit log file with the MySQL version
Replace `audit_sql.xml` with the MySQL version.
Use the same file for MariaDB.
1. Locate `pentaho-solutions/system/dialects/mysql5/audit_sql.xml`.
2. Copy it into `pentaho-solutions/system`.
#### Modify Jackrabbit repository information for MySQL or MariaDB
Update Jackrabbit to use the MySQL (or MariaDB) repository.
1. Open `pentaho/server/pentaho-server/pentaho-solutions/system/jackrabbit/repository.xml`.
2. Uncomment the MySQL lines.
3. Comment out the MS SQL Server, Oracle, and PostgreSQL lines.
4. Verify these sections use the MySQL values.
| Item | Code Section |
|---|
| Repository | <FileSystem class="org.apache.jackrabbit.core.fs.db.DbFileSystem">
<param name="driver" value="javax.naming.InitialContext"/>
<param name="url" value="java:comp/env/jdbc/jackrabbit"/>
<param name="schema" value="mysql"/>
<param name="schemaObjectPrefix" value="fs_repos_"/>
</FileSystem>
|
| DataStore | <DataStore class="org.apache.jackrabbit.core.data.db.DbDataStore">
<param name="driver" value="javax.naming.InitialContext"/>
<param name="url" value="java:comp/env/jdbc/jackrabbit"/>
<param name="databaseType" value="mysql"/>
<param name="minRecordLength" value="1024"/>
<param name="maxConnections" value="3"/>
<param name="copyWhenReading" value="true"/>
<param name="tablePrefix" value=""/>
<param name="schemaObjectPrefix" value="ds_repos_"/>
</DataStore>
|
| Workspaces | <FileSystem class="org.apache.jackrabbit.core.fs.db.DbFileSystem">
<param name="driver" value="javax.naming.InitialContext"/>
<param name="url" value="java:comp/env/jdbc/jackrabbit"/>
<param name="schema" value="mysql"/>
<param name="schemaObjectPrefix" value="fs_ws_"/>
</FileSystem>
|
| PersistenceManager (1st part) | <PersistenceManager class="org.apache.jackrabbit.core.persistence.bundle.MySqlPersistenceManager">
<param name="driver" value="javax.naming.InitialContext"/>
<param name="url" value="java:comp/env/jdbc/jackrabbit"/>
<param name="schema" value="mysql"/>
<param name="schemaObjectPrefix" value="${wsp.name}_pm_ws_"/>
</PersistenceManager>
|
| Versioning | <FileSystem class="org.apache.jackrabbit.core.fs.db.DbFileSystem">
<param name="driver" value="javax.naming.InitialContext"/>
<param name="url" value="java:comp/env/jdbc/jackrabbit"/>
<param name="schema" value="mysql"/>
<param name="schemaObjectPrefix" value="fs_ver_"/>
</FileSystem>
|
| PersistenceManager (2nd part) | <PersistenceManager class="org.apache.jackrabbit.core.persistence.bundle.MySqlPersistenceManager">
<param name="driver" value="javax.naming.InitialContext"/>
<param name="url" value="java:comp/env/jdbc/jackrabbit"/>
<param name="schema" value="mysql"/>
<param name="schemaObjectPrefix" value="pm_ver_"/>
</PersistenceManager>
|
| DatabaseJournal | <Journal class="org.apache.jackrabbit.core.journal.DatabaseJournal">
<param name="revision" value="${rep.home}/revision.log"/>
<param name="driver" value="javax.naming.InitialContext"/>
<param name="url" value="java:comp/env/jdbc/jackrabbit"/>
<param name="schema" value="mysql"/>
<param name="schemaObjectPrefix" value="J_C_"/>
<param name="janitorEnabled" value="true"/>
<param name="janitorSleep" value="86400"/>
<param name="janitorFirstRunHourOfDay" value="3"/>
</Journal>
|
{% endstep %}
{% step %}
### Perform Tomcat-specific connection tasks
After your repository is configured, configure the web application server to connect to the Pentaho Repository.
In this step, you will make JDBC and JNDI connections to the Hibernate, Jackrabbit, and Quartz components.
{% hint style="info" %}
**Note**: By default, the Pentaho Server software is configured to deploy and run on Tomcat. Connections are already specified. Only update the Tomcat `context.xml` file if you changed default ports or passwords.1
{% endhint %}
#### Download driver and apply to the Pentaho Server
To connect to a database, including the Pentaho Repository database, download and copy a JDBC driver to the right locations for the Pentaho Server and the web application server.
{% hint style="info" %}
**Note**: Due to licensing restrictions, Pentaho cannot redistribute some third-party database drivers. You must download and install these files yourself.
{% endhint %}
1. Download a JDBC driver JAR from your database vendor or a third-party driver developer. The [JDBC drivers reference](https://docs.pentaho.com/install/jdbc-drivers-reference) lists supported drivers.
2. Copy the JDBC driver JAR to the `pentaho/server/pentaho-server/tomcat/lib` folder.
3. Copy the [`hsqldb-2.3.2.jar`](https://sourceforge.net/projects/hsqldb/files/hsqldb/hsqldb_2_3/) file to `pentaho-server/tomcat/lib` if you want to keep the Pentaho sample.
#### Modify JDBC connection information in the Tomcat XML file
Database connection and network settings (username, password, driver class, IP address or domain name, and port numbers) for your Pentaho Repository database are stored in the `context.xml` file.
Update this file to match your environment.
{% hint style="warning" %}
**Important**: If you have a different port, password, user, driver class, or IP address, update the examples to match your environment.
{% endhint %}
1. Consult your database documentation to determine the JDBC class name and the connection string for your Pentaho Repository database.
2. Go to `server/pentaho-server/tomcat/webapps/pentaho/META-INF` and open `context.xml` in a text editor.
3. Add the following code if it does not already exist.
For MySQL:
<Resource validationQuery="select 1" url="jdbc:mysql://localhost:3306/hibernate"
driverClassName="com.mysql.jdbc.Driver" password="password" username="hibuser"
initialSize="0" maxActive="20" maxIdle="10" maxWait="10000"
factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory"
type="javax.sql.DataSource" auth="Container" name="jdbc/Hibernate"/>
<Resource validationQuery="select 1" url="jdbc:mysql://localhost:3306/hibernate"
driverClassName="com.mysql.jdbc.Driver" password="password" username="hibuser"
initialSize="0" maxActive="20" maxIdle="10" maxWait="10000"
factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory"
type="javax.sql.DataSource" auth="Container" name="jdbc/Audit"/>
<Resource validationQuery="select 1" url="jdbc:mysql://localhost:3306/quartz"
driverClassName="com.mysql.jdbc.Driver" password="password" username="pentaho_user"
testOnBorrow="true" initialSize="0" maxActive="20" maxIdle="10" maxWait="10000"
factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory"
type="javax.sql.DataSource" auth="Container" name="jdbc/Quartz"/>
<Resource validationQuery="select 1" url="jdbc:mysql://localhost:3306/pentaho_operations_mart"
driverClassName="com.mysql.jdbc.Driver" password="password" username="hibuser"
initialSize="0" maxActive="20" maxIdle="10" maxWait="10000"
factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory"
type="javax.sql.DataSource" auth="Container" name="jdbc/pentaho_operations_mart"/>
<Resource validationQuery="select 1" url="jdbc:mysql://localhost:3306/pentaho_operations_mart"
driverClassName="com.mysql.jdbc.Driver" password="password" username="hibuser"
initialSize="0" maxActive="20" maxIdle="10" maxWait="10000"
factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory"
type="javax.sql.DataSource" auth="Container" name="jdbc/PDI_Operations_Mart"/>
<Resource name="jdbc/live_logging_info" auth="Container" type="javax.sql.DataSource"
factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory"
initialSize="0" maxActive="20" maxIdle="10" maxWait="10000"
username="hibuser" password="password" driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost:3306/pentaho_dilogs" validationQuery="select 1"/>
<Resource name="jdbc/jackrabbit" auth="Container" type="javax.sql.DataSource"
factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory"
maxActive="20" minIdle="0" maxIdle="5" initialSize="0" maxWait="10000"
username="jcr_user" password="password" driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost:3306/jackrabbit" validationQuery="select 1"/>
For MariaDB:
<Resource validationQuery="select 1" url="jdbc:mariadb://localhost:3306/hibernate"
driverClassName="org.mariadb.jdbc.Driver" password="password" username="hibuser"
initialSize="0" maxActive="20" maxIdle="10" maxWait="10000"
factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory"
type="javax.sql.DataSource" auth="Container" name="jdbc/Hibernate"/>
<Resource validationQuery="select 1" url="jdbc:mariadb://localhost:3306/hibernate"
driverClassName="org.mariadb.jdbc.Driver" password="password" username="hibuser"
initialSize="0" maxActive="20" maxIdle="10" maxWait="10000"
factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory"
type="javax.sql.DataSource" auth="Container" name="jdbc/Audit"/>
<Resource validationQuery="select 1" url="jdbc:mariadb://localhost:3306/quartz"
driverClassName="org.mariadb.jdbc.Driver" password="password" username="pentaho_user"
testOnBorrow="true" initialSize="0" maxActive="20" maxIdle="10" maxWait="10000"
factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory"
type="javax.sql.DataSource" auth="Container" name="jdbc/Quartz"/>
<Resource validationQuery="select 1" url="jdbc:mariadb://localhost:3306/pentaho_operations_mart"
driverClassName="org.mariadb.jdbc.Driver" password="password" username="hibuser"
initialSize="0" maxActive="20" maxIdle="10" maxWait="10000"
factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory"
type="javax.sql.DataSource" auth="Container" name="jdbc/pentaho_operations_mart"/>
<Resource validationQuery="select 1" url="jdbc:mariadb://localhost:3306/pentaho_operations_mart"
driverClassName="org.mariadb.jdbc.Driver" password="password" username="hibuser"
initialSize="0" maxActive="20" maxIdle="10" maxWait="10000"
factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory"
type="javax.sql.DataSource" auth="Container" name="jdbc/PDI_Operations_Mart"/>
<Resource name="jdbc/live_logging_info" auth="Container" type="javax.sql.DataSource"
factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory"
initialSize="0" maxActive="20" maxIdle="10" maxWait="10000"
username="hibuser" password="password" driverClassName="org.mariadb.jdbc.Driver"
url="jdbc:mariadb://localhost:3306/pentaho_dilogs" validationQuery="select 1"/>
<Resource name="jdbc/jackrabbit" auth="Container" type="javax.sql.DataSource"
factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory"
maxActive="20" minIdle="0" maxIdle="5" initialSize="0" maxWait="10000"
username="jcr_user" password="password" driverClassName="org.mariadb.jdbc.Driver"
url="jdbc:mariadb://localhost:3306/jackrabbit" validationQuery="select 1"/>
4. Update usernames, passwords, driver classes, IP addresses (or domain names), and port numbers to match your environment.
5. Comment out resource references for databases you are not using.
6. Verify `validationQuery` is set to `select 1` (for example: `validationQuery="select 1"`).
7. Save `context.xml`, then close it.
{% endstep %}
{% step %}
### Start the Pentaho Server
After you finish setting up the Pentaho Repository, go to [Step 3: Start Pentaho Server and install licenses and client tools](https://docs.pentaho.com/install/pentaho-installation-overview-cp/archive-installation/step-3-start-pentaho-server-and-install-licenses-and-client-tools).
{% endstep %}
{% endstepper %}
## Oracle
Install Oracle as the host database for the Pentaho Server in an archive installation by completing the following tasks, in order:
{% hint style="warning" %}
**Important**: Before you prepare your Pentaho Repository, you must complete [Step 1: Prepare your environment](https://docs.pentaho.com/install/pentaho-installation-overview-cp/archive-installation/step-1-prepare-your-environment).
{% endhint %}
{% stepper %}
{% step %}
### Initialize Oracle Pentaho Repository database
To initialize Oracle so that it serves as the Pentaho Repository, you need to run several SQL scripts to create the Hibernate, Quartz, Jackrabbit (JCR), and Pentaho Operations Mart components.
{% hint style="warning" %}
**Important:**
* Use the ASCII character set when you run these scripts. Do not use UTF-8. Text string length limitations might cause the scripts to fail.
* If you use a different password or user, update the examples to match.
* Review Quartz database information:
* Pentaho 10.2.0.0 and earlier use Quartz 1.x. Quartz 1.x uses a `QRTZ5_` prefix in the database.
* Pentaho 10.2.0.1 and later use Quartz 2.x. Quartz 2.x uses a `QRTZ6_` prefix in the database.
* When upgrading from an earlier version to Pentaho 10.2.0.1 and later, create the `QRTZ6_` tables by repeating the Quartz database creation procedure. You can optionally migrate existing Quartz schedules using the migration script. During this upgrade procedure, executing the SQL create script resets the corresponding Quartz database without loss of the original data. However, the other associated repository databases in 10.2.0.1 will be reset by SQL scripts if run, and data might be deleted.
* Always back up your data before proceeding.
* If you do not complete the Quartz upgrade, Pentaho Server fails at startup. You will also see this exception message in `catalina.log`:
Missing Quartz library database error
{% endhint %}
#### Change default passwords
For production systems, follow best practices and change the default passwords in the SQL scripts.
{% hint style="info" %}
**Note:** If you are evaluating Pentaho, you can skip this step.
{% endhint %}
1. Browse to the `/pentaho-server/data/oracle` folder.
2. Use a text editor to update the scripts as needed for your user, password, database, and other settings:
* `create_jcr_ora.sql`
* `create_quartz_ora.sql`
* `create_repository_ora.sql`
* `pentaho_mart_oracle.sql`
3. Save and close the files.
#### Run SQL scripts
When upgrading from previous Pentaho versions, including 10.2.0.0 GA to 10.2.0.1 and later, you must manually initialize a new Quartz database. A new Quartz library is created in the repository database as a result.
Always follow best practices and back up your data before you proceed. If you want to keep existing Quartz schedules, you can migrate the existing tables to the new tables.
{% hint style="info" %}
**Note:** You may need administrator permissions to run these scripts on the host OS server.
{% endhint %}
1. Back up your data. See step 3, **Back up your existing Pentaho products and install Pentaho 11.0**, in the [Upgrade an archive installation](https://docs.pentaho.com/install/pentaho-upgrade-cp#upgrade-an-archive-installation) section.
2. If the Pentaho Server is running, stop it. For instructions, see [Stop and start the Pentaho Server and repository](https://docs.pentaho.com/install/legacy-redirects/tasks-to-be-performed-by-an-it-administrator-legacy-redirects/configure-the-pentaho-server#stop-and-start-the-pentaho-server-and-repository).
3. Make sure that your Oracle instance is running.
4. Open a Command Prompt or terminal window that runs SQL\*Plus, then run the following scripts in this order:
1. `/pentaho-server/data/oracle/create_jcr_ora.sql`
2. `/pentaho-server/data/oracle/create_quartz_ora.sql`
3. `/pentaho-server/data/oracle/create_repository_ora.sql`
4. `/pentaho-server/data/pentaho_mart_oracle.sql`
This creates the Quartz database, Jackrabbit repository, Hibernate repository, and Pentaho Operations Mart.
The `QRTZ6_` tables are created. Any existing `QRTZ5_` tables remain. If you start the Pentaho Server now, the schedule is empty.
5. If you want to retain your existing schedule data, open `migrate_old_quartz_data_oracle.sql` in a text editor.
6. Update the script for your user, password, database, and other settings, then save it.
7. Run the migration script:
`/pentaho-server/data/oracle/migrate_old_quartz_data_oracle.sql`
{% hint style="info" %}
**Notes:**
* The original `QRTZ5_` data remains available. Delete it only after you verify a successful migration.
* You unpacked the Pentaho Operations Mart SQL file while preparing your environment for the archive installation.
{% endhint %}
#### Verify Oracle initialization
{% hint style="info" %}
**Note:** Unless you changed it in Step 1, the default password for each username below is `password`.
{% endhint %}
After you run the scripts, verify that databases and user roles were created correctly:
1. Open a Terminal or Command Prompt window running SQL\*Plus (or a similar client) and connect to the Oracle database.
2. Log in as *hibuser*.
3. Verify that you can see the tables under **hibernate**.
4. If you installed Pentaho Operations Mart, verify that you can see the tables under **pentaho\_operations\_mart**.
5. Log in as *jcr\_user*.
6. Verify that you can see the tables under **jackrabbit**.
The Jackrabbit tables may not appear until you start Pentaho for the first time.
7. Log in as *pentaho\_user*.
8. Verify that you can see the tables under **quartz**.
9. Exit the tool.
You have initialized the Oracle Pentaho Repository database.
{% endstep %}
{% step %}
### Configure Oracle Pentaho Repository database
Now that you have initialized your repository database, you will need to configure Quartz, Hibernate, Jackrabbit, and Pentaho Operations Mart for an Oracle database.
By default, the examples in this section are for an Oracle database that runs on port 1521. The default password is also in these examples.
{% hint style="warning" %}
**Important**: If you use a different port or password, you must update the examples to match your environment.
{% endhint %}
#### Set up Quartz on Oracle
Event information, such as scheduled reports, is stored in the Quartz JobStore. During the installation process, you must indicate where the JobStore is located by modifying the `quartz.properties` file.
1. Open the `pentaho/server/pentaho-server/pentaho-solutions/system/scheduler-plugin/quartz/quartz.properties` file in any text editor.
2. Locate the **#\_replace\_jobstore\_properties** section and set the **org.quartz.jobStore.driverDelegateClass** as shown:
org.quartz.jobStore.driverDelegateClass = org.quartz.impl.jdbcjobstore.oracle.OracleDelegate
3. Save the file and close the text editor.
#### Set Hibernate settings for Oracle
Modify the Hibernate settings file to specify where Pentaho should find the Pentaho Repository’s Hibernate configuration file. The Hibernate configuration file specifies driver and connection information, as well as dialects and how to handle connection closes and timeouts.
{% hint style="info" %}
**Note:** The Hibernate database is also where the Pentaho Server stores the audit logs that act as source data for the Pentaho Operations Mart.
{% endhint %}
The files in this section are located in the `pentaho/server/pentaho-server/pentaho-solutions/system/hibernate` directory.
Perform the following steps to specify where Pentaho can find the Hibernate configuration file.
1. Open the `hibernate-settings.xml` file in a text editor. Find the tags and change `postgresql.hibernate.cfg.xml` to `oracle10g.hibernate.cfg.xml` as shown.
From:
```xml
system/hibernate/postgresql.hibernate.cfg.xml
```
To:
```xml
system/hibernate/oracle10g.hibernate.cfg.xml
```
2. Save and close the file.
#### Replace default version of audit log file with Oracle version
Since you are using Oracle to host the Pentaho Repository, you need to replace the `audit_sql.xml` file with one that is configured for Oracle.
1. Locate the `pentaho-solutions/system/dialects/oracle10g/audit_sql.xml` file.
2. Copy it into the `pentaho-solutions/system` directory.
#### Modify Jackrabbit repository information for Oracle
Edit the following code to change the default Jackrabbit repository to Oracle.
1. Navigate to the `pentaho/server/pentaho-server/pentaho-solutions/system/jackrabbit` and open the `repository.xml` file with any text editor.
2. As shown in the table below, locate and verify or change the code so that the Oracle lines are not commented out, but the MySQL, PostgreSQL, and MS SQL Server lines are commented out.
| Item | Code Section |
|---|
| Repository | <FileSystem class="org.apache.jackrabbit.core.fs.db.OracleFileSystem">
<param name="driver" value="javax.naming.InitialContext"/>
<param name="url" value="java:comp/env/jdbc/jackrabbit"/>
<param name="schemaObjectPrefix" value="fs_repos_"/>
<param name="tablespace" value="pentaho_tablespace"/>
</FileSystem>
|
{% endstep %}
{% step %}
### Perform Tomcat-specific connection tasks
After your repository has been configured, you must configure the web application servers to connect to the Pentaho Repository. In this step, you will make JDBC and JNDI connections to the Hibernate, Jackrabbit, and Quartz components.
{% hint style="info" %}
**Note:** By default, the Pentaho Server software is configured to be deployed and run on the Tomcat server. As such, connections have already been specified and the Tomcat `context.xml` file must be modified ONLY if you have changed the default ports or passwords.
{% endhint %}
Use these steps to set up the JDBC driver and update the Tomcat connection settings.
#### Download driver and apply to the Pentaho Server
To connect to a database, including the Pentaho Repository database, you must download and copy a JDBC driver to the appropriate places for the Pentaho Server and the web application server.
{% hint style="info" %}
**Note:** Due to licensing restrictions, Pentaho cannot redistribute some third-party database drivers. You must download and install the file yourself.
{% endhint %}
1. Download a JDBC Driver JAR from your database vendor or a third-party driver developer.\
The [JDBC drivers reference](https://docs.pentaho.com/install/jdbc-drivers-reference) has a list of supported drivers.
2. Copy the JDBC driver JAR you downloaded to the `pentaho/server/pentaho-server/tomcat/lib` folder.
3. Copy the [`hsqldb-2.3.2.jar`](https://sourceforge.net/projects/hsqldb/files/hsqldb/hsqldb_2_3/) file to `pentaho-server/tomcat/lib` if you want to retain the sample provided by Pentaho.
#### Modify JDBC connection information in the Tomcat XML file
Database connection and network information, such as the username, password, driver class information, IP address or domain name, and port numbers for your Pentaho Repository database are stored in the `context.xml` file. Modify this file to reflect the database connection and network information for your operating environment.
{% hint style="warning" %}
**Important:** If you have a different port, password, user, driver class information, or IP address, you must change the values in these examples to match your environment.
{% endhint %}
1. Consult your database documentation to determine the JDBC class name and the connection string for your Pentaho Repository database.
2. Navigate to the `server/pentaho-server/tomcat/webapps/pentaho/META-INF` directory and open the `context.xml` file with any text editor.
3. Add the following code to the file if it does not already exist. Replace `XE` in the URL setting with your schema name.
<Resource name="jdbc/Hibernate" auth="Container" type="javax.sql.DataSource"
factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory"
maxActive="20" minIdle="0" maxIdle="5" initialSize="0" maxWait="10000"
username="hibuser" password="password" driverClassName="oracle.jdbc.OracleDriver"
url="jdbc:oracle:thin:@localhost:1521/XE" validationQuery="select 1 from dual"
jdbcInterceptors="ConnectionState" defaultAutoCommit="true"/>
<Resource name="jdbc/Audit" auth="Container" type="javax.sql.DataSource"
factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory"
maxActive="20" minIdle="0" maxIdle="5" initialSize="0" maxWait="10000"
username="hibuser" password="password" driverClassName="oracle.jdbc.OracleDriver"
url="jdbc:oracle:thin:@localhost:1521/XE" validationQuery="select 1 from dual"/>
<Resource name="jdbc/Quartz" auth="Container" type="javax.sql.DataSource"
factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory"
maxActive="20" minIdle="0" maxIdle="5" initialSize="0" maxWait="10000"
username="quartz" password="password" testOnBorrow="true"
driverClassName="oracle.jdbc.OracleDriver"
url="jdbc:oracle:thin:@localhost:1521/XE" validationQuery="select 1 from dual"/>
<Resource name="jdbc/PDI_Operations_Mart" auth="Container" type="javax.sql.DataSource"
factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory"
maxActive="20" minIdle="0" maxIdle="5" initialSize="0" maxWait="10000"
username="pentaho_operations_mart" password="password"
driverClassName="oracle.jdbc.OracleDriver"
url="jdbc:oracle:thin:@localhost:1521/XE" validationQuery="select 1 from dual"/>
<Resource name="jdbc/pentaho_operations_mart" auth="Container" type="javax.sql.DataSource"
factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory"
maxActive="20" minIdle="0" maxIdle="5" initialSize="0" maxWait="10000"
username="pentaho_operations_mart" password="password"
driverClassName="oracle.jdbc.OracleDriver"
url="jdbc:oracle:thin:@localhost:1521/XE" validationQuery="select 1 from dual"/>
<Resource name="jdbc/live_logging_info" auth="Container" type="javax.sql.DataSource"
factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory"
maxActive="20" minIdle="0" maxIdle="5" initialSize="0" maxWait="10000"
username="pentaho_dilogs" password="password"
driverClassName="oracle.jdbc.OracleDriver"
url="jdbc:oracle:thin:@localhost:1521/XE" validationQuery="select 1 from dual"/>
<Resource name="jdbc/SampleData" auth="Container" type="javax.sql.DataSource"
factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory"
maxActive="20" minIdle="0" maxIdle="5" initialSize="0" maxWait="10000"
username="pentaho_user" password="password"
driverClassName="org.hsqldb.jdbcDriver"
url="jdbc:hsqldb:hsql://localhost/sampledata"
validationQuery="select 1 from dual"/>
<Resource name="jdbc/SampleDataAdmin" auth="Container" type="javax.sql.DataSource"
factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory"
maxActive="20" minIdle="0" maxIdle="5" initialSize="0" maxWait="10000"
username="pentaho_admin" password="password"
driverClassName="org.hsqldb.jdbcDriver"
url="jdbc:hsqldb:hsql://localhost/sampledata"
validationQuery="select 1 from dual"/>
<Resource name="jdbc/jackrabbit" auth="Container" type="javax.sql.DataSource"
factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory"
maxActive="20" minIdle="0" maxIdle="5" initialSize="0" maxWait="10000"
username="jcr_user" password="password"
driverClassName="oracle.jdbc.OracleDriver"
url="jdbc:oracle:thin:@localhost:1521/XE"
validationQuery="select 1 from dual"/>
4. Modify the username, password, driver class information, IP address (or domain name), and port numbers to match your environment.
5. Comment out any resource references that refer to other databases.
6. Verify that the `validationQuery` variable for your database is set to `validationQuery=\"select 1 from dual\"`.
7. Save the `context.xml` file, then close it.
{% endstep %}
{% step %}
### Start the Pentaho Server
After you finish setting up the Pentaho Repository, go to [Step 3: Start Pentaho Server and install licenses and client tools](https://docs.pentaho.com/install/pentaho-installation-overview-cp/archive-installation/step-3-start-pentaho-server-and-install-licenses-and-client-tools).
{% endstep %}
{% endstepper %}
## MS SQL Server
Install MS SQL Server as the host database for the Pentaho Server in an archive installation by completing the following tasks, in order:
{% hint style="warning" %}
**Important**: Before you prepare your Pentaho Repository, you must complete [Step 1: Prepare your environment](https://docs.pentaho.com/install/pentaho-installation-overview-cp/archive-installation/step-1-prepare-your-environment).
{% endhint %}
{% stepper %}
{% step %}
### Initialize MS SQL Server Pentaho Repository database
To initialize MS SQL Server so it serves as the Pentaho Repository, run SQL scripts to create the Hibernate, Quartz, Jackrabbit (JCR), and Pentaho Operations Mart components.
{% hint style="warning" %}
**Important**:
* Use the ASCII character set when you run these scripts. Do not use UTF-8. Text string length limitations can cause the scripts to fail.
* If you have a different password or user, update the examples to match your configuration.
* Pentaho 10.2.0.0 and earlier use the Quartz 1.x library, which uses a `QRTZ5_` prefix in the database. Pentaho 10.2.0.1 and later use Quartz 2.x, which uses a `QRTZ6_` prefix. When you upgrade from an earlier version, create the `QRTZ6_` tables by repeating the Quartz database creation step. You can optionally migrate schedules with the migration script. Always back up your data before you proceed.
* If you do not complete the Quartz upgrade, Pentaho Server fails at startup. You will see a “Missing Quartz library database error” exception in `catalina.log`.
{% endhint %}
#### Adjust MS SQL Server configuration settings
Configure these MS SQL Server settings in Microsoft SQL Server Management Studio (or a similar tool).
1. Select **SQL Server and Windows Authentication Mode** to use mixed authentication.
2. Enable **TCP/IP** for MS SQL Server.
3. Make sure MS SQL Server listens on an external IP, not localhost.
#### Change default passwords
For production, follow best practices to change the default passwords in the SQL scripts.
{% hint style="info" %}
**Note:** If you are evaluating Pentaho, you may want to skip this step.
{% endhint %}
1. Browse to the `/pentaho-server/data/sqlserver` folder.
2. Modify these create scripts as needed for your existing setup (user, password, database, and other required information):
* `create_jcr_sqlServer.sql`
* `create_quartz_sqlServer.sql`
* `create_repository_sqlServer.sql`
* `pentaho_mart_sqlserver.sql`
3. Save and close the files.
#### Run SQL scripts
When upgrading from previous Pentaho versions (including 10.2.0.0 GA to 10.2.0.1 and later), manually initialize a new Quartz database. This creates a new Quartz library in the repository database. Back up your data before you proceed.
Run the SQL creation scripts.
{% hint style="info" %}
**Note:** You may need administrator permissions to run these scripts on the host OS server.
{% endhint %}
1. Back up your data. See step 3, **Back up your existing Pentaho products and install Pentaho 11.0**, in the [Upgrade an archive installation](https://docs.pentaho.com/install/pentaho-upgrade-cp#upgrade-an-archive-installation) section.
2. If the Pentaho Server is running, stop it. For instructions, see [Stop and start the Pentaho Server and repository](https://docs.pentaho.com/install/legacy-redirects/tasks-to-be-performed-by-an-it-administrator-legacy-redirects/configure-the-pentaho-server#stop-and-start-the-pentaho-server-and-repository).
3. Make sure your MS SQL Server instance is running.
4. Open a `sqlcmd` utility window, or run these scripts from Microsoft SQL Server Management Studio, in this order:
1. `/pentaho-server/data/sqlserver/create_jcr_sqlServer.sql`
2. `/pentaho-server/data/sqlserver/create_quartz_sqlServer.sql`
3. `/pentaho-server/data/sqlserver/create_repository_sqlServer.sql`
4. `/pentaho-server/data/sqlserver/pentaho_mart_sqlserver.sql`
The Quartz database, Jackrabbit and MS SQL Server repositories, and Pentaho Operations Mart are created. The new Quartz scheduler table prefixed with `QRTZ6_` is created. Any existing `QRTZ5_` scheduler database is retained. Starting the Pentaho Server at this point results in an empty schedule.
5. If you want to retain your existing scheduler database, open the `migrate_old_quartz_data_sqlserver.sql` file.
6. Modify the migration script as needed for your existing setup (user, password, database, and other required information), then save it.
7. Run the migration script:\
`/pentaho-server/data/sqlserver/migrate_old_quartz_data_sqlserver.sql`
The original Quartz scheduler database is migrated to the new Quartz database.
{% hint style="info" %}
**Notes:**
* The original `QRTZ5_` data is retained. You can delete it after you confirm migration is successful.
* You unpacked the Pentaho Operations Mart SQL file while preparing your environment.
{% endhint %}
#### Verify MS SQL Server initialization
{% hint style="info" %}
**Note:** Unless you changed it in Step 1, the default password for each username below is `password`.
{% endhint %}
After you run the scripts, verify that the databases and user roles were created properly:
1. Open MS SQL Server Management Studio.
2. Log in as `hibuser`.
3. In **Object Explorer**, navigate to `hibernate` and verify that you can see the tables.
4. If you installed Pentaho Operations Mart, log in as `pentaho_operations_mart`.
5. In **Object Explorer**, navigate to `pentaho_operations_mart` and verify that you can see the tables.
6. Log in as `jcr_user`.
7. In **Object Explorer**, navigate to `jackrabbit` and verify that you can see the tables.\
The Jackrabbit tables may not appear until you start Pentaho for the first time.
8. Log in as `pentaho_user`.
9. In **Object Explorer**, navigate to `quartz` and verify that you can see the tables.
10. Exit MS SQL Server Management Studio.
{% endstep %}
{% step %}
### Configure MS SQL Server Pentaho Repository database
After you initialize your repository database, configure Quartz, Hibernate, Jackrabbit, and Pentaho Operations Mart for an MS SQL Server database.
By default, the examples in this section assume MS SQL Server running on port `1433`. They also use the default password.
{% hint style="warning" %}
**Important:** If you use a different port or password, update the examples to match your configuration.
{% endhint %}
#### Set up Quartz on MS SQL Server
Event information, such as scheduled reports, is stored in the Quartz JobStore. During installation, indicate where the JobStore is located by modifying `quartz.properties`.
1. Open `pentaho/server/pentaho-server/pentaho-solutions/system/scheduler-plugin/quartz/quartz.properties`.
2. In the `#_replace_jobstore_properties` section, set `org.quartz.jobStore.driverDelegateClass`:
org.quartz.jobStore.driverDelegateClass = org.quartz.impl.jdbcjobstore.MSSQLDelegate
3. Save the file.
#### Set Hibernate settings for MS SQL Server
Modify the Hibernate settings file to specify where Pentaho should find the Pentaho Repository Hibernate configuration file.
{% hint style="info" %}
**Note:** The Hibernate database is also where Pentaho Server stores the audit logs used as source data for Pentaho Operations Mart.
{% endhint %}
The files in this section are located in `pentaho/server/pentaho-server/pentaho-solutions/system/hibernate`.
1. Open `hibernate-settings.xml`.
2. Find the `` tags. Change `postgresql.hibernate.cfg.xml` to `sqlserver.hibernate.cfg.xml`.
From:
```xml
system/hibernate/postgresql.hibernate.cfg.xml
```
To:
```xml
system/hibernate/sqlserver.hibernate.cfg.xml
```
3. Save and close the file.
#### Replace default version of audit log file with MS SQL Server version
Since you are using MS SQL Server to host the Pentaho Repository, replace `audit_sql.xml` with the MS SQL Server version.
1. Locate `pentaho-solutions/system/dialects/sqlserver/audit_sql.xml`.
2. Copy it into the `pentaho-solutions/system` directory.
#### Modify Jackrabbit repository information for MS SQL Server
Edit `repository.xml` to change the default Jackrabbit repository to MS SQL Server.
1. Navigate to `pentaho/server/pentaho-server/pentaho-solutions/system/jackrabbit`.
2. Open `repository.xml`.
3. Locate and verify or change the configuration so that the MS SQL Server lines are uncommented, and the MySQL, Oracle, and PostgreSQL lines are commented out.
| Item | Code Section |
|---|
| Repository | <FileSystem class="org.apache.jackrabbit.core.fs.db.MSSqlFileSystem">
<param name="driver" value="javax.naming.InitialContext"/>
<param name="url" value="java:comp/env/jdbc/jackrabbit"/>
<param name="schema" value="mssql"/>
<param name="schemaObjectPrefix" value="fs_repos_"/>
</FileSystem>
|
{% endstep %}
{% step %}
### Perform Tomcat-specific connection tasks
After your repository is configured, configure your web application server to connect to the Pentaho Repository.
In this step, you make JDBC and JNDI connections to the Hibernate, Jackrabbit, and Quartz components.
{% hint style="info" %}
**Note:** By default, Pentaho Server is configured to be deployed and run on Tomcat. Connections are already specified. Modify Tomcat `context.xml` only if you changed the default ports or passwords.
{% endhint %}
#### Download driver and apply to the Pentaho Server
To connect to a database (including the Pentaho Repository database), download and copy a JDBC driver to the Pentaho Server and the web application server.
{% hint style="info" %}
**Note:** Due to licensing restrictions, Pentaho cannot redistribute some third-party database drivers. You must download and install the file yourself.
{% endhint %}
1. Download a JDBC driver `.jar` from your database vendor or a third-party driver developer. See [JDBC drivers reference](https://docs.pentaho.com/install/jdbc-drivers-reference).
2. Copy the JDBC driver `.jar` to `pentaho/server/pentaho-server/tomcat/lib`.
3. Copy [`hsqldb-2.3.2.jar`](https://sourceforge.net/projects/hsqldb/files/hsqldb/hsqldb_2_3/) to `pentaho-server/tomcat/lib` if you want to retain the Pentaho sample data.
#### Modify JDBC connection information in the Tomcat XML file
Database connection and network information (such as user name, password, driver class, host, and port) is stored in `context.xml`. Modify it to match your environment.
{% hint style="info" %}
**Important:** If you use a different user name, password, or port, update the examples to match your environment.
{% endhint %}
1. Consult your database documentation to determine the JDBC class name and connection string for your Pentaho Repository database.
2. Navigate to `server/pentaho-server/tomcat/webapps/pentaho/META-INF`.
3. Open `context.xml`.
4. Add the following resources if they do not already exist:
<Resource name="jdbc/Hibernate" auth="Container" type="javax.sql.DataSource"
factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory"
maxActive="20" maxIdle="10" maxWait="10000"
initialSize="0" username="hibuser" password="password"
driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
url="jdbc:sqlserver://localhost:1433;DatabaseName=hibernate;encrypt=true;trustServerCertificate=true"
validationQuery="select 1"/>
<Resource name="jdbc/Audit" auth="Container" type="javax.sql.DataSource"
factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory"
maxActive="20" maxIdle="10" maxWait="10000"
initialSize="0" username="hibuser" password="password"
driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
url="jdbc:sqlserver://localhost:1433;DatabaseName=hibernate;encrypt=true;trustServerCertificate=true"
validationQuery="select 1"/>
<Resource name="jdbc/Quartz" auth="Container" type="javax.sql.DataSource"
factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory"
initialSize="0" maxActive="20" maxIdle="10" maxWait="10000"
testOnBorrow="true" username="pentaho_user" password="password"
driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
url="jdbc:sqlserver://localhost:1433;DatabaseName=quartz;encrypt=true;trustServerCertificate=true"
validationQuery="select 1"/>
<Resource name="jdbc/pentaho_operations_mart" auth="Container" type="javax.sql.DataSource"
factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory"
maxActive="20" maxIdle="10" maxWait="10000"
initialSize="0" username="pentaho_operations_mart" password="password"
driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
url="jdbc:sqlserver://localhost:1433;DatabaseName=pentaho_operations_mart;encrypt=true;trustServerCertificate=true"
validationQuery="select 1"/>
<Resource name="jdbc/PDI_Operations_Mart" auth="Container" type="javax.sql.DataSource"
factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory"
maxActive="20" maxIdle="10" maxWait="10000"
initialSize="0" username="pentaho_operations_mart" password="password"
driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
url="jdbc:sqlserver://localhost:1433;DatabaseName=pentaho_operations_mart;encrypt=true;trustServerCertificate=true"
validationQuery="select 1"/>
<Resource name="jdbc/live_logging_info" auth="Container" type="javax.sql.DataSource"
factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory"
initialSize="0" maxActive="20" maxIdle="10"
maxWait="10000" username="dilogs_user" password="password"
driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
url="jdbc:sqlserver://localhost:1433;DatabaseName=pentaho_dilogs;encrypt=true;trustServerCertificate=true"
validationQuery="select 1"/>
<Resource name="jdbc/SampleData" auth="Container" type="javax.sql.DataSource"
factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory"
maxActive="20" minIdle="0" maxIdle="5" initialSize="0" maxWait="10000"
username="pentaho_user" password="password" driverClassName="org.hsqldb.jdbcDriver"
url="jdbc:hsqldb:hsql://localhost/sampledata" validationQuery="select 1"/>
<Resource name="jdbc/SampleDataAdmin" auth="Container" type="javax.sql.DataSource"
factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory"
maxActive="20" minIdle="0" maxIdle="5" initialSize="0" maxWait="10000"
username="pentaho_admin" password="password" driverClassName="org.hsqldb.jdbcDriver"
url="jdbc:hsqldb:hsql://localhost/sampledata" validationQuery="select 1"/>
<Resource name="jdbc/jackrabbit" auth="Container" type="javax.sql.DataSource"
factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory"
maxActive="20" minIdle="0" maxIdle="5" initialSize="0"
maxWait="10000" username="jcr_user" password="password"
driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
url="jdbc:sqlserver://localhost:1433;DatabaseName=jackrabbit;encrypt=true;trustServerCertificate=true"
validationQuery="select 1"/>
Note: Depending on your MS SQL Server configuration, you may be able to set trustServerCertificate=true. See Connecting with encryption.