# Step 2: Prepare your Pentaho Repository

The Pentaho Repository resides on the database you installed during environment setup.

It includes these components:

* **Jackrabbit**\
  Contains the solution repository, examples, security data, and report content data.
* **Quartz**\
  Stores scheduler data for reports and jobs.
* **Hibernate**\
  Stores audit logging data.
* **(Optional) Pentaho Operations Mart**\
  Stores system usage and performance data.

{% hint style="warning" %}
**Important:** If you use examples or scripts, update usernames, passwords, and ports to match your environment.
{% endhint %}

Prepare the Pentaho Repository for your host database:

* [Use PostgreSQL as your repository database (manual installation)](#use-postgresql-as-your-repository-database-manual-installation)
* [Use MySQL or MariaDB as your repository database (manual installation)](#use-mysql-or-mariadb-as-your-repository-database-manual-installation)
* [Use Oracle as your repository database (manual installation)](#use-oracle-as-your-repository-database-manual-installation)
* [Use MS SQL Server as your repository database (manual installation)](#use-ms-sql-server-as-your-repository-database-manual-installation)

## Use PostgreSQL as your repository database (manual installation)

Install PostgreSQL as the host database for the Pentaho Server repository.

{% stepper %}
{% step %}

### Initialize the PostgreSQL Pentaho Repository database

Run PostgreSQL initialization scripts to create the Pentaho Repository schemas.

You create schemas for Hibernate, Quartz, Jackrabbit (JCR), and Pentaho Operations Mart.

{% 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.
* 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`:

    <pre data-overflow="wrap" data-expandable="true"><code>Missing Quartz library database error
    </code></pre>

{% endhint %}

#### Change default passwords

For production, follow best practices and change the default passwords in the SQL script files.

{% hint style="info" %}
**Note**: If you are evaluating Pentaho, you can skip this step.
{% endhint %}

1. Browse to `<your-pentaho-dir>/pentaho-server/data/postgresql`.
2. Edit these scripts for your user, password, database, and any other required values:
   * `create_jcr_postgresql.sql`
   * `create_quartz_postgresql.sql`
   * `create_repository_postgresql.sql`
   * `pentaho_mart_postgresql.sql`
3. Save the files.

#### Run SQL scripts

Run the PostgreSQL initialization scripts from the Pentaho software distribution.

{% hint style="info" %}
You may need administrator permissions to run these scripts on the host OS.
{% endhint %}

If you are upgrading from earlier Pentaho versions (including 10.2.0.0 GA → 10.2.0.1+), you must create the new Quartz 2.x tables.

If you want to keep existing scheduler data, migrate `QRTZ5_` data to the new `QRTZ6_` tables.

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 Pentaho Server is running, stop it. 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 PostgreSQL is running.
4. Open a `psql` console and run these scripts in order:

   1. `<your-pentaho-dir>/pentaho-server/data/postgresql/create_quartz_postgresql.sql`
   2. `<your-pentaho-dir>/pentaho-server/data/postgresql/create_jcr_postgresql.sql`
   3. `<your-pentaho-dir>/pentaho-server/data/postgresql/create_repository_postgresql.sql` (Hibernate)
   4. `<your-pentaho-dir>/pentaho-server/data/postgresql/pentaho_mart_postgresql.sql`

   The Quartz database, Jackrabbit repository, PostgreSQL repository, and Operations Mart schema are created. The new Quartz scheduler tables are also created with the prefix, `QRTZ6_`. Any existing `QRTZ5_` scheduler tables are retained.
5. If you want to retain existing scheduler data, open `migrate_old_quartz_data_postgresql.sql`.
6. Update the migration script for your current user, password, database, and any other required values.
7. Run the migration script in the following location: `<your-pentaho-dir>/pentaho-server/data/postgresql/migrate_old_quartz_data_postgresql.sql`. The original `QRTZ5_` data is retained. Delete it after you confirm the migration is successful.

{% hint style="info" %}
**Note**: If you do not have `pentaho_mart_postgresql.sql`, make sure you unpacked the Pentaho Operations Mart SQL file during installation.
{% endhint %}

#### Verify PostgreSQL initialization

{% hint style="info" %}
**Note**: If you did not change the default password, the default password for the users below is `password`.
{% endhint %}

1. Open pgAdmin (or a similar client).
2. Log in as `hibuser`, then verify you can see the tables under `hibernate`.
3. If you installed Operations Mart, verify you can see the tables under `pentaho_operations_mart`.
4. Log in as `jcr_user`, then verify you can see the tables under `jackrabbit`.&#x20;

   <div data-gb-custom-block data-tag="hint" data-style="info" class="hint hint-info"><p><strong>Note</strong>: The Jackrabbit tables may not appear until after you start Pentaho for the first time.</p></div>
5. Log in as `pentaho_user`, then verify you can see the tables under `quartz`.

You have initialized the PostgreSQL Pentaho Repository database.&#x20;
{% endstep %}

{% step %}

### Configure the PostgreSQL Pentaho Repository database

Configure Quartz, Hibernate, Jackrabbit, and Operations Mart for PostgreSQL.&#x20;

{% hint style="warning" %}
**Important**:

* PostgreSQL is configured by default. If you kept the default password and port, skip this section and continue to [Perform Tomcat-specific connection tasks](#perform-tomcat-specific-connection-tasks).
* If you use a different port or password, update the examples to match your environment.
  {% endhint %}

#### Set up Quartz on PostgreSQL

Quartz stores scheduler data, such as scheduled reports.

You point Pentaho at the correct JobStore by updating `quartz.properties`.

1. Open `pentaho/server/pentaho-server/pentaho-solutions/system/scheduler-plugin/quartz/quartz.properties`.
2. In `#_replace_jobstore_properties`, set `org.quartz.jobStore.driverDelegateClass`:

   <pre class="language-properties" data-overflow="wrap"><code class="lang-properties">org.quartz.jobStore.driverDelegateClass = org.quartz.impl.jdbcjobstore.PostgreSQLDelegate
   </code></pre>
3. Save the file.

#### Set Hibernate settings for PostgreSQL

Hibernate settings point Pentaho to the correct Hibernate configuration file.

{% hint style="info" %}
**Note**: Hibernate also stores the audit logs used by Operations Mart.
{% endhint %}

Files are in `pentaho/server/pentaho-server/pentaho-solutions/system/hibernate`.

1. Open `hibernate-settings.xml`.

2. Find the `<config-file>` entry, then confirm it uses the PostgreSQL config:

   ```xml
   <config-file>system/hibernate/postgresql.hibernate.cfg.xml</config-file>
   ```

3. Save the file if you made changes.

#### **Modify Jackrabbit repository information for PostgreSQL**

Update Jackrabbit to use the PostgreSQL repository.

1. Open `pentaho/server/pentaho-server/pentaho-solutions/system/jackrabbit/repository.xml`.
2. Comment out resource references for databases you are not using.
3. Verify these sections use the PostgreSQL values.
   * Repository

     <pre class="language-xml" data-overflow="wrap"><code class="lang-xml">&#x3C;FileSystem class="org.apache.jackrabbit.core.fs.db.DbFileSystem">
       &#x3C;param name="driver" value="javax.naming.InitialContext"/>
       &#x3C;param name="url" value="java:comp/env/jdbc/jackrabbit"/>
       &#x3C;param name="schema" value="postgresql"/>
       &#x3C;param name="schemaObjectPrefix" value="fs_repos_"/>
     &#x3C;/FileSystem>
     </code></pre>

   * DataStore

     <pre class="language-xml" data-overflow="wrap"><code class="lang-xml">&#x3C;DataStore class="org.apache.jackrabbit.core.data.db.DbDataStore">
       &#x3C;param name="driver" value="javax.naming.InitialContext"/>
       &#x3C;param name="url" value="java:comp/env/jdbc/jackrabbit"/>
       &#x3C;param name="databaseType" value="postgresql"/>
       &#x3C;param name="minRecordLength" value="1024"/>
       &#x3C;param name="maxConnections" value="3"/>
       &#x3C;param name="copyWhenReading" value="true"/>
       &#x3C;param name="tablePrefix" value=""/>
       &#x3C;param name="schemaObjectPrefix" value="ds_repos_"/>
     &#x3C;/DataStore>
     </code></pre>

   * Workspaces

     ```xml
     <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="postgresql"/>
       <param name="schemaObjectPrefix" value="fs_ws_"/>
     </FileSystem>
     ```

   * PersistenceManager (workspaces)

     <pre class="language-xml" data-overflow="wrap"><code class="lang-xml">&#x3C;PersistenceManager class="org.apache.jackrabbit.core.persistence.bundle.PostgreSQLPersistenceManager">
       &#x3C;param name="driver" value="javax.naming.InitialContext"/>
       &#x3C;param name="url" value="java:comp/env/jdbc/jackrabbit"/>
       &#x3C;param name="schema" value="postgresql"/>
       &#x3C;param name="schemaObjectPrefix" value="${wsp.name}_pm_ws_"/>
     &#x3C;/PersistenceManager>
     </code></pre>

   * DatabaseJournal

     <pre class="language-xml" data-overflow="wrap"><code class="lang-xml">&#x3C;Journal class="org.apache.jackrabbit.core.journal.DatabaseJournal">
       &#x3C;param name="revision" value="${rep.home}/revision.log" />
       &#x3C;param name="driver" value="javax.naming.InitialContext"/>
       &#x3C;param name="url" value="java:comp/env/jdbc/jackrabbit"/>
       &#x3C;param name="schema" value="postgresql"/>
       &#x3C;param name="schemaObjectPrefix" value="cl_j_"/>
       &#x3C;param name="janitorEnabled" value="true"/>
       &#x3C;param name="janitorSleep" value="86400"/>
       &#x3C;param name="janitorFirstRunHourOfDay" value="3"/>
     &#x3C;/Journal>
     </code></pre>

{% endstep %}

{% step %}

### Perform Tomcat-specific connection tasks

After you configure the Pentaho Repository, configure Tomcat to connect to it.

This step sets up JDBC and JNDI connections for Hibernate, Jackrabbit, and Quartz.

{% hint style="info" %}
**Note**: Pentaho Server is configured for Tomcat by default. Only edit Tomcat `context.xml` if you changed default ports or passwords.
{% endhint %}

#### Download the JDBC driver and apply it to Pentaho Server

To connect to a database, you must install a JDBC driver JAR.

Install it for Pentaho Server and Tomcat.

{% hint style="info" %}
**Note**: Pentaho cannot redistribute some third-party database drivers. Download the driver from your database vendor.
{% endhint %}

1. Download a JDBC driver JAR from your database vendor. 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. Optional: Copy `hsqldb-2.3.2.jar` to `pentaho/server/pentaho-server/tomcat/lib`.

Do this if you want to keep the Pentaho sample data.

#### Modify JDBC connection information in the Tomcat `context.xml` file

Database connection settings are stored in Tomcat `context.xml`.

Update the file to match your environment.

{% hint style="warning" %}
**Important**: If your port, password, username, driver class, or host differs from the defaults, update the examples.
{% endhint %}

1. Consult your database documentation for:
   * JDBC driver class name
   * JDBC connection string

2. Open `server/pentaho-server/tomcat/webapps/pentaho/META-INF/context.xml` in a text editor.

3. Add the following XML if it does not already exist:

   <pre class="language-xml" data-overflow="wrap" data-expandable="true"><code class="lang-xml">&#x3C;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="org.postgresql.Driver" url="jdbc:postgresql://localhost:5432/hibernate"
           validationQuery="select 1" jdbcInterceptors="ConnectionState" defaultAutoCommit="true"/>
   &#x3C;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="org.postgresql.Driver" url="jdbc:postgresql://localhost:5432/hibernate"
           validationQuery="select 1" />
   &#x3C;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="pentaho_user" password="password" testOnBorrow="true"
           driverClassName="org.postgresql.Driver" url="jdbc:postgresql://localhost:5432/quartz"
           validationQuery="select 1"/>
   &#x3C;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="hibuser" password="password"
           driverClassName="org.postgresql.Driver" url="jdbc:postgresql://localhost:5432/hibernate"
           validationQuery="select 1"/>
   &#x3C;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="hibuser" password="password"
           driverClassName="org.postgresql.Driver" url="jdbc:postgresql://localhost:5432/hibernate"
           validationQuery="select 1"/>
   &#x3C;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="hibuser" password="password" driverClassName="org.postgresql.Driver"
           url="jdbc:postgresql://localhost:5432/hibernate?searchpath=pentaho_dilogs"
           validationQuery="select 1"/>
   &#x3C;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"/>
   &#x3C;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"/>
   &#x3C;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.postgresql.Driver" url="jdbc:postgresql://localhost:5432/jackrabbit"
           validationQuery="select 1" jdbcInterceptors="ConnectionState" defaultAutoCommit="true"/>
   </code></pre>

4. Update the username, password, driver class, host, and port to match your environment.

5. Comment out any resources for databases you are not using.

6. Verify `validationQuery="select 1"`.

7. Save the file.

After you have prepared your Pentaho Repository, go to [Step 3: Configure and start the Pentaho Server after manual installation](https://docs.pentaho.com/install/pentaho-installation-overview-cp/manual-installation/step-3-configure-and-start-the-pentaho-server-after-manual-installation).
{% endstep %}
{% endstepper %}

## Use MySQL or MariaDB as your repository database (manual installation)

Install MySQL or MariaDB as the host database for the Pentaho Server repository.

{% stepper %}
{% step %}

### Initialize the MySQL or MariaDB Pentaho Repository database

Run SQL scripts to create the Hibernate, Quartz, Jackrabbit (JCR), and 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.
* 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`:

    <pre data-overflow="wrap" data-expandable="true"><code>Missing Quartz library database error
    </code></pre>

{% endhint %}

#### Change default passwords

For production, change the default passwords in the SQL scripts.

{% hint style="info" %}
**Note**: If you are evaluating Pentaho, you might skip this step.
{% endhint %}

1. Browse to `<your-pentaho-directory>/pentaho-server/data/mysql`.
2. Update these scripts for your user, password, database, and other required values:
   * `create_jcr_mysql.sql`
   * `create_quartz_mysql.sql`
   * `create_repository_mysql.sql`
   * `pentaho_mart_mysql.sql`
3. Save and close the files.

#### Run SQL scripts

When upgrading from older versions (including 10.2.0.0 GA), initialize a new Quartz database. This creates the Quartz 2.x tables.

{% hint style="info" %}
**Note**: You might need administrator permissions on the host OS.
{% 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. 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 MySQL or MariaDB instance is running.
4. Open a MySQL/MariaDB prompt or MySQL Workbench. Then run the scripts in this order:

   1. `<your-pentaho-directory>/pentaho-server/data/mysql/create_jcr_mysql.sql`
   2. `<your-pentaho-directory>/pentaho-server/data/mysql/create_quartz_mysql.sql`
   3. `<your-pentaho-directory>/pentaho-server/data/mysql/create_repository_mysql.sql`
   4. `<your-pentaho-directory>/pentaho-server/data/mysql/pentaho_mart_mysql.sql`

   This creates:

   * Quartz scheduler tables prefixed with `QRTZ6_`
   * Jackrabbit tables
   * Repository tables
   * (Optional) Operations Mart tables

   Any existing `QRTZ5_` tables are retained. If you start the server now, the schedule is empty.
5. If you want to retain existing schedules, open `migrate_old_quartz_data_mysql.sql`.
6. Update the migration script as needed. Then save it.
7. Run: `<your-pentaho-directory>/pentaho-server/data/mysql/migrate_old_quartz_data_mysql.sql`

{% hint style="info" %}
**Note**: The original `QRTZ5_` tables are retained. Delete them only after you confirm migration success.
{% endhint %}

#### Verify MySQL or MariaDB initialization

{% hint style="info" %}
**Note**: If you did not change the default passworkd, it is `password`.
{% endhint %}

1. Open MySQL Workbench.
2. Log in as `hibuser`.
3. Under **Schemas**, expand **hibernate** > **Tables**. Verify you see tables.
4. If you installed Operations Mart, expand **pentaho\_operations\_mart** > **Tables**. Verify tables.
5. Log in as `jcr_user`.
6. Under **Schemas**, expand **jackrabbit** > **Tables**. Verify tables.

   <div data-gb-custom-block data-tag="hint" data-style="info" class="hint hint-info"><p><strong>Note</strong>: Jackrabbit tables might not appear until the first server start.</p></div>
7. Log in as `pentaho_user`.
8. Under **Schemas**, expand **quartz** > **Tables**. Verify tables.
9. Exit MySQL Workbench.

You have initialized the MySQL or MariaDB Pentaho Repository database.&#x20;
{% endstep %}

{% step %}

### Configure the MySQL or MariaDB Pentaho Repository database

Configure Quartz, Hibernate, Jackrabbit, and Operations Mart.

The examples assume MySQL on port `3306` with default passwords.

{% hint style="warning" %}
**Important**: If your ports or passwords differ, update the examples. Match your environment values.
{% endhint %}

#### Set up Quartz on MySQL or MariaDB

Quartz stores scheduler event data, such as scheduled reports.

You point Pentaho at the correct JobStore by updating `quartz.properties`.

1. Open `pentaho/server/pentaho-server/pentaho-solutions/system/scheduler-plugin/quartz/quartz.properties`.
2. In `#_replace_jobstore_properties`, set `org.quartz.jobStore.driverDelegateClass`:

   <pre class="language-properties" data-overflow="wrap" data-expandable="true"><code class="lang-properties"><strong>org.quartz.jobStore.driverDelegateClass = org.quartz.impl.jdbcjobstore.StdJDBCDelegate
   </strong></code></pre>
3. Save the file.

#### Set Hibernate settings for MySQL or MariaDB

Hibernate settings point Pentaho to the correct Hibernate configuration file.

{% hint style="info" %}
**Note**: Hibernate also stores the audit logs used by Operations Mart.
{% endhint %}

Files are in `pentaho/server/pentaho-server/pentaho-solutions/system/hibernate`.

1. Open `hibernate-settings.xml`.
2. Find the `<config-file>` entry. Replace `postgresql.hibernate.cfg.xml` with `mysql5.hibernate.cfg.xml`:

   From:

   ```xml
   <config-file>system/hibernate/postgresql.hibernate.cfg.xml</config-file>
   ```

   To:&#x20;

   ```xml
   <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. Change the driver class:

   From:

   ```xml
   <property name="connection.driver_class">com.mysql.jdbc.Driver</property>:
   ```

   To:

   ```xml
   <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.
   * Repository

     <pre class="language-xml" data-overflow="wrap"><code class="lang-xml">&#x3C;FileSystem class="org.apache.jackrabbit.core.fs.db.DbFileSystem">
       &#x3C;param name="driver" value="javax.naming.InitialContext"/>
       &#x3C;param name="url" value="java:comp/env/jdbc/jackrabbit"/>
       &#x3C;param name="schema" value="mysql"/>
       &#x3C;param name="schemaObjectPrefix" value="fs_repos_"/>
     &#x3C;/FileSystem>
     </code></pre>
   * DataStore

     <pre class="language-xml" data-overflow="wrap"><code class="lang-xml">&#x3C;DataStore class="org.apache.jackrabbit.core.data.db.DbDataStore">
       &#x3C;param name="driver" value="javax.naming.InitialContext"/>
       &#x3C;param name="url" value="java:comp/env/jdbc/jackrabbit"/>
       &#x3C;param name="databaseType" value="mysql"/>
       &#x3C;param name="minRecordLength" value="1024"/>
       &#x3C;param name="maxConnections" value="3"/>
       &#x3C;param name="copyWhenReading" value="true"/>
       &#x3C;param name="tablePrefix" value=""/>
       &#x3C;param name="schemaObjectPrefix" value="ds_repos_"/>
     &#x3C;/DataStore>
     </code></pre>
   * Workspaces

     <pre class="language-xml" data-overflow="wrap"><code class="lang-xml">&#x3C;FileSystem class="org.apache.jackrabbit.core.fs.db.DbFileSystem">
       &#x3C;param name="driver" value="javax.naming.InitialContext"/>
       &#x3C;param name="url" value="java:comp/env/jdbc/jackrabbit"/>
       &#x3C;param name="schema" value="mysql"/>
       &#x3C;param name="schemaObjectPrefix" value="fs_ws_"/>
     &#x3C;/FileSystem>
     </code></pre>
   * PersistenceManager (workspaces)

     <pre class="language-xml" data-overflow="wrap"><code class="lang-xml">&#x3C;PersistenceManager class="org.apache.jackrabbit.core.persistence.bundle.MySqlPersistenceManager">
       &#x3C;param name="driver" value="javax.naming.InitialContext"/>
       &#x3C;param name="url" value="java:comp/env/jdbc/jackrabbit"/>
       &#x3C;param name="schema" value="mysql"/>
       &#x3C;param name="schemaObjectPrefix" value="${wsp.name}_pm_ws_"/>
     &#x3C;/PersistenceManager>
     </code></pre>
   * DatabaseJournal

     <pre class="language-xml" data-overflow="wrap"><code class="lang-xml">&#x3C;Journal class="org.apache.jackrabbit.core.journal.DatabaseJournal">
       &#x3C;param name="revision" value="${rep.home}/revision.log"/>
       &#x3C;param name="driver" value="javax.naming.InitialContext"/>
       &#x3C;param name="url" value="java:comp/env/jdbc/jackrabbit"/>
       &#x3C;param name="schema" value="mysql"/>
       &#x3C;param name="schemaObjectPrefix" value="J_C_"/>
       &#x3C;param name="janitorEnabled" value="true"/>
       &#x3C;param name="janitorSleep" value="86400"/>
       &#x3C;param name="janitorFirstRunHourOfDay" value="3"/>
     &#x3C;/Journal>
     </code></pre>

{% endstep %}

{% step %}

### Perform Tomcat-specific connection tasks

Configure Tomcat to connect to Hibernate, Jackrabbit, and Quartz.

{% hint style="info" %}
**Note**: Pentaho Server is configured for Tomcat by default. Modify `context.xml` only if you changed ports or passwords.
{% endhint %}

#### Download the driver and apply it to the Pentaho Server

You need a JDBC driver for your repository database.

{% hint style="info" %}
**Note**: Due to licensing restrictions, you must download some drivers yourself.
{% endhint %}

1. Download a JDBC driver JAR from your database vendor or a third-party provider. See [JDBC drivers reference](https://docs.pentaho.com/install/jdbc-drivers-reference).
2. Copy the driver JAR to `pentaho/server/pentaho-server/tomcat/lib`.
3. If you want to keep the sample data, copy `hsqldb-2.3.2.jar` to `pentaho-server/tomcat/lib`.

#### Modify JDBC connection information in the Tomcat context XML file

Database connection information is stored in `context.xml`.

{% hint style="warning" %}
**Important**: If your port, password, user, driver class, or host differs, update the examples. Match your environment values.
{% endhint %}

1. Use your database documentation to confirm:
   * JDBC driver class name
   * JDBC connection string format

2. Open `server/pentaho-server/tomcat/webapps/pentaho/META-INF/context.xml`.

3. Add resource entries if they do not exist already.
   * MySQL

     <pre class="language-xml" data-overflow="wrap" data-expandable="true"><code class="lang-xml">&#x3C;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="com.mysql.cj.jdbc.Driver" url="jdbc:mysql://localhost:3306/hibernate"
             validationQuery="select 1" jdbcInterceptors="ConnectionState" defaultAutoCommit="true"/>
     &#x3C;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="com.mysql.cj.jdbc.Driver" url="jdbc:mysql://localhost:3306/hibernate"
             validationQuery="select 1" />
     &#x3C;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="pentaho_user" password="password" testOnBorrow="true"
             driverClassName="com.mysql.cj.jdbc.Driver" url="jdbc:mysql://localhost:3306/quartz"
             validationQuery="select 1"/>
     &#x3C;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="hibuser" password="password"
             driverClassName="com.mysql.cj.jdbc.Driver" url="jdbc:mysql://localhost:3306/hibernate"
             validationQuery="select 1"/>
     &#x3C;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="hibuser" password="password"
             driverClassName="com.mysql.cj.jdbc.Driver" url="jdbc:mysql://localhost:3306/hibernate"
             validationQuery="select 1"/>
     &#x3C;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="hibuser" password="password" driverClassName="com.mysql.cj.jdbc.Driver"
             url="jdbc:mysql://localhost:3306/hibernate"
             validationQuery="select 1"/>
     &#x3C;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"/>
     &#x3C;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"/>
     &#x3C;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.cj.jdbc.Driver" url="jdbc:mysql://localhost:3306/jackrabbit"
             validationQuery="select 1" jdbcInterceptors="ConnectionState" defaultAutoCommit="true"/>
     </code></pre>
   * MariaDB

     <pre class="language-xml" data-overflow="wrap" data-expandable="true"><code class="lang-xml">&#x3C;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="org.mariadb.jdbc.Driver" url="jdbc:mariadb://localhost:3306/hibernate"
             validationQuery="select 1" jdbcInterceptors="ConnectionState" defaultAutoCommit="true"/>
     &#x3C;!-- Repeat the same pattern for Audit, Quartz, and jackrabbit. -->
     </code></pre>

4. Update usernames, passwords, hostnames, and ports to match your environment.

5. Comment out resource entries for databases you are not using.

6. Verify `validationQuery="select 1"` is correct for your database.

7. Save `context.xml`.

After you have prepared your Pentaho Repository, go to [Step 3: Configure and start the Pentaho Server after manual installation](https://docs.pentaho.com/install/pentaho-installation-overview-cp/manual-installation/step-3-configure-and-start-the-pentaho-server-after-manual-installation).
{% endstep %}
{% endstepper %}

## Use Oracle as your repository database (manual installation)

Install Oracle as the host database for the Pentaho Server repository.

{% stepper %}
{% step %}

### Initialize the Oracle Pentaho Repository database

To initialize Oracle as a Pentaho Repository, run several SQL scripts to create Hibernate, Quartz, Jackrabbit (JCR), and 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.
* 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`:

    <pre data-overflow="wrap" data-expandable="true"><code>Missing Quartz library database error
    </code></pre>

{% endhint %}

#### Change default passwords

For production systems, 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 `<your-pentaho-directory>/pentaho-server/data/oracle`.
2. Edit these scripts for your user, password, database, and required 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

If you are upgrading from earlier Pentaho versions, initialize a new Quartz database for Quartz 2.x.

{% hint style="warning" %}
**Important**: Back up your data before proceeding.
{% 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. 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 Oracle instance is running.
4. Open SQL\*Plus (or a similar client) and run the scripts in this order:
   1. `<your-pentaho-directory>/pentaho-server/data/oracle/create_jcr_ora.sql`
   2. `<your-pentaho-directory>/pentaho-server/data/oracle/create_quartz_ora.sql`
   3. `<your-pentaho-directory>/pentaho-server/data/oracle/create_repository_ora.sql`
   4. `<your-pentaho-directory>/pentaho-server/data/oracle/pentaho_mart_oracle.sql`
5. (Optional) Migrate schedules from Quartz 1.x:
   1. Open `<your-pentaho-directory>/pentaho-server/data/oracle/migrate_old_quartz_data_oracle.sql`.
   2. Update the script for your environment, then save it.
   3. Run the migration script.

{% hint style="info" %}
**Note**: The original `QRTZ5_` tables remain. Delete them only after you confirm the migration.
{% endhint %}

#### Verify Oracle initialization

{% hint style="info" %}
**Note**: Unless you changed it in Step 1, the default password for each username is `password`.
{% endhint %}

1. Open SQL\*Plus (or a similar client) and connect to the Oracle database.
2. Log in as `hibuser`. Verify you can see the tables under `hibernate`.
3. If you installed Operations Mart, verify you can see the tables under `pentaho_operations_mart`.
4. Log in as `jcr_user`. Verify you can see the tables under `jackrabbit`. Jackrabbit tables might not appear until you start Pentaho for the first time.
5. Log in as `pentaho_user`. Verify you can see the tables under `quartz`.
   {% endstep %}

{% step %}

### Configure the Oracle Pentaho Repository database

By default, these examples assume Oracle runs on port `1521`.

{% hint style="warning" %}
**Important**: If your ports or passwords differ, update the examples.
{% endhint %}

#### Set up Quartz on Oracle

1. Open `pentaho/server/pentaho-server/pentaho-solutions/system/scheduler-plugin/quartz/quartz.properties`.
2. In `#_replace_jobstore_properties`, set:

   <pre class="language-properties" data-overflow="wrap"><code class="lang-properties">org.quartz.jobStore.driverDelegateClass = org.quartz.impl.jdbcjobstore.oracle.OracleDelegate
   </code></pre>
3. Save the file.

#### Set Hibernate settings for Oracle

Files are in `pentaho/server/pentaho-server/pentaho-solutions/system/hibernate`.

1. Open `hibernate-settings.xml`.
2. Update the `<config-file>` value:

   From:

   ```xml
   <config-file>system/hibernate/postgresql.hibernate.cfg.xml</config-file>
   ```

   To:

   ```xml
   <config-file>system/hibernate/oracle10g.hibernate.cfg.xml</config-file>
   ```
3. Save and close the file.

#### Replace the audit log file with the Oracle version

1. Locate `pentaho-solutions/system/dialects/oracle10g/audit_sql.xml`.
2. Copy it into `pentaho-solutions/system`.

#### Modify Jackrabbit repository information for Oracle

1. Open `pentaho/server/pentaho-server/pentaho-solutions/system/jackrabbit/repository.xml`.
2. Uncomment the Oracle sections.
3. Comment out the MySQL, PostgreSQL, and MS SQL Server sections. Use these Oracle sections as your reference:
   * Repository

     <pre class="language-xml" data-overflow="wrap"><code class="lang-xml">&#x3C;FileSystem class="org.apache.jackrabbit.core.fs.db.OracleFileSystem">
       &#x3C;param name="driver" value="javax.naming.InitialContext"/>
       &#x3C;param name="url" value="java:comp/env/jdbc/jackrabbit"/>
       &#x3C;param name="schemaObjectPrefix" value="fs_repos_"/>
       &#x3C;param name="tablespace" value="pentaho_tablespace"/>
     &#x3C;/FileSystem>
     </code></pre>
   * DataStore

     <pre class="language-xml" data-overflow="wrap"><code class="lang-xml">&#x3C;DataStore class="org.apache.jackrabbit.core.data.db.DbDataStore">
       &#x3C;param name="driver" value="javax.naming.InitialContext"/>
       &#x3C;param name="url" value="java:comp/env/jdbc/jackrabbit"/>
       &#x3C;param name="databaseType" value="oracle"/>
       &#x3C;param name="minRecordLength" value="1024"/>
       &#x3C;param name="maxConnections" value="3"/>
       &#x3C;param name="copyWhenReading" value="true"/>
       &#x3C;param name="tablePrefix" value=""/>
       &#x3C;param name="schemaObjectPrefix" value="ds_repos_"/>
     &#x3C;/DataStore>
     </code></pre>
   * Workspaces

     <pre class="language-xml" data-overflow="wrap"><code class="lang-xml">&#x3C;FileSystem class="org.apache.jackrabbit.core.fs.db.OracleFileSystem">
       &#x3C;param name="driver" value="javax.naming.InitialContext"/>
       &#x3C;param name="url" value="java:comp/env/jdbc/jackrabbit"/>
       &#x3C;param name="schemaObjectPrefix" value="fs_ws_"/>
       &#x3C;param name="tablespace" value="pentaho_tablespace"/>
     &#x3C;/FileSystem>
     </code></pre>
   * PersistenceManager (workspaces)

     <pre class="language-xml" data-overflow="wrap"><code class="lang-xml">&#x3C;PersistenceManager class="org.apache.jackrabbit.core.persistence.bundle.OraclePersistenceManager">
       &#x3C;param name="driver" value="javax.naming.InitialContext"/>
       &#x3C;param name="url" value="java:comp/env/jdbc/jackrabbit"/>
       &#x3C;param name="schema" value="oracle"/>
       &#x3C;param name="schemaObjectPrefix" value="${wsp.name}_pm_ws_"/>
       &#x3C;param name="tablespace" value="pentaho_tablespace"/>
     &#x3C;/PersistenceManager>
     </code></pre>
   * Versioning

     <pre class="language-xml" data-overflow="wrap"><code class="lang-xml">&#x3C;FileSystem class="org.apache.jackrabbit.core.fs.db.OracleFileSystem">
       &#x3C;param name="driver" value="javax.naming.InitialContext"/>
       &#x3C;param name="url" value="java:comp/env/jdbc/jackrabbit"/>
       &#x3C;param name="schemaObjectPrefix" value="fs_ver_"/>
       &#x3C;param name="tablespace" value="pentaho_tablespace"/>
     &#x3C;/FileSystem>
     </code></pre>
   * PersistenceManager (versioning)

     <pre class="language-xml" data-overflow="wrap"><code class="lang-xml">&#x3C;PersistenceManager class="org.apache.jackrabbit.core.persistence.bundle.OraclePersistenceManager">
       &#x3C;param name="driver" value="javax.naming.InitialContext"/>
       &#x3C;param name="url" value="java:comp/env/jdbc/jackrabbit"/>
       &#x3C;param name="schema" value="oracle"/>
       &#x3C;param name="schemaObjectPrefix" value="pm_ver_"/>
       &#x3C;param name="tablespace" value="pentaho_tablespace"/>
     &#x3C;/PersistenceManager>
     </code></pre>
   * DatabaseJournal

     <pre class="language-xml" data-overflow="wrap"><code class="lang-xml">&#x3C;Journal class="org.apache.jackrabbit.core.journal.OracleDatabaseJournal">
       &#x3C;param name="revision" value="${rep.home}/revision.log" />
       &#x3C;param name="driver" value="javax.naming.InitialContext"/>
       &#x3C;param name="url" value="java:comp/env/jdbc/jackrabbit"/>
       &#x3C;param name="schema" value="oracle"/>
       &#x3C;param name="schemaObjectPrefix" value="J_C_"/>
       &#x3C;param name="janitorEnabled" value="true"/>
       &#x3C;param name="janitorSleep" value="86400"/>
       &#x3C;param name="janitorFirstRunHourOfDay" value="3"/>
     &#x3C;/Journal>
     </code></pre>

{% endstep %}

{% step %}

### Perform Tomcat-specific connection tasks

After repository configuration, configure your web application server to connect to the repository. This includes JDBC and JNDI connections for Hibernate, Jackrabbit, and Quartz.

{% hint style="info" %}
**Note**: Pentaho Server is configured for Tomcat by default. Update `context.xml` only if you changed default ports, hosts, or passwords.
{% endhint %}

#### Download the JDBC driver and apply it to Pentaho Server

1. Download a JDBC driver `.jar` from Oracle or a third-party provider.
2. If needed, confirm the driver is supported. See [JDBC drivers reference](https://docs.pentaho.com/install/jdbc-drivers-reference).
3. Copy the JDBC driver `.jar` to `pentaho/server/pentaho-server/tomcat/lib`.
4. (Optional) Copy `hsqldb-2.3.2.jar` to `pentaho/server/pentaho-server/tomcat/lib` to keep the sample data.

#### Modify JDBC connection information in the Tomcat `context.xml` file

{% hint style="warning" %}
**Important**: Update host, port, username, password, and database identifiers to match your environment. Also comment out resources for databases you do not use.
{% endhint %}

1. Find the JDBC driver class name and connection string in your database documentation.

2. Open `server/pentaho-server/tomcat/webapps/pentaho/META-INF/context.xml`.

3. Add or update resources similar to the following examples:

   <pre class="language-xml" data-overflow="wrap" data-expandable="true"><code class="lang-xml">&#x3C;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:@&#x3C;host>:1521:&#x3C;SID_or_service>"
           validationQuery="select 1" jdbcInterceptors="ConnectionState" defaultAutoCommit="true"/>
   &#x3C;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="pentaho_user" password="password"
           driverClassName="oracle.jdbc.OracleDriver"
           url="jdbc:oracle:thin:@&#x3C;host>:1521:&#x3C;SID_or_service>"
           validationQuery="select 1"/>
   &#x3C;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:@&#x3C;host>:1521:&#x3C;SID_or_service>"
           validationQuery="select 1" jdbcInterceptors="ConnectionState" defaultAutoCommit="true"/>
   </code></pre>

4. Save the file.

After you have prepared your Pentaho Repository, go to [Step 3: Configure and start the Pentaho Server after manual installation](https://docs.pentaho.com/install/pentaho-installation-overview-cp/manual-installation/step-3-configure-and-start-the-pentaho-server-after-manual-installation).
{% endstep %}
{% endstepper %}

## Use MS SQL Server as your repository database (manual installation)

Install MS SQL Server as the host database for the Pentaho Server repository.

{% stepper %}
{% step %}

### Initialize the MySQL or MariaDB Pentaho Repository database

Run SQL scripts to create the Hibernate, Quartz, Jackrabbit (JCR), and Operations Mart schemas.

{% 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.
* 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`:

    <pre data-overflow="wrap" data-expandable="true"><code>Missing Quartz library database error
    </code></pre>

{% endhint %}

#### Adjust MS SQL Server configuration settings

Configure these settings in Microsoft SQL Server Management Studio (or similar):

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 systems, 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 `<your-pentaho-directory>/pentaho-server/data/sqlserver`.
2. Edit these scripts for your user, password, database, and required settings:
   * `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

If you are upgrading from earlier Pentaho versions, initialize a new Quartz database for Quartz 2.x.

{% hint style="warning" %}
**Important**: Back up your data before proceeding.
{% 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. 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. Run these scripts in order:

   1. `<your-pentaho-directory>/pentaho-server/data/sqlserver/create_jcr_sqlServer.sql`
   2. `<your-pentaho-directory>/pentaho-server/data/sqlserver/create_quartz_sqlServer.sql`
   3. `<your-pentaho-directory>/pentaho-server/data/sqlserver/create_repository_sqlServer.sql`
   4. `<your-pentaho-directory>/pentaho-server/data/sqlserver/pentaho_mart_sqlserver.sql`

   This creates:

   * Jackrabbit schema and repository tables
   * Quartz tables (`QRTZ6_` for Quartz 2.x)
   * Hibernate repository schema
   * Pentaho Operations Mart schema
5. (Optional) Migrate schedules from Quartz 1.x:

   1. Open `<your-pentaho-directory>/pentaho-server/data/sqlserver/migrate_old_quartz_data_sqlserver.sql`.
   2. Update the script for your environment, then save it.
   3. Run the migration script.

   <div data-gb-custom-block data-tag="hint" data-style="info" class="hint hint-info"><p><strong>Note</strong>: The original <code>QRTZ5_</code> tables remain. Delete them only after you confirm the migration.</p></div>

#### Verify MS SQL Server initialization

{% hint style="info" %}
**Note**: If you did not change it, the default password for each username is `password`.
{% endhint %}

1. Open MS SQL Server Management Studio.
2. Log in as `hibuser`.
3. In **Object Explorer**, select `hibernate`. Verify tables exist.
4. If you installed Pentaho Operations Mart, log in as `pentaho_operations_mart`.
5. In **Object Explorer**, select `pentaho_operations_mart`. Verify tables exist.
6. Log in as `jcr_user`.
7. In **Object Explorer**, select `jackrabbit`. Verify tables exist. Jackrabbit tables may not appear until you start Pentaho the first time.
8. Log in as `pentaho_user`.
9. In **Object Explorer**, select `quartz`. Verify tables exist.
10. Exit MS SQL Server Management Studio.
    {% endstep %}

{% step %}

### Configure the MS SQL Server Pentaho Repository database

After initialization, configure Quartz, Hibernate, Jackrabbit, and Pentaho Operations Mart to use MS SQL Server.

{% hint style="warning" %}
**Important**: These examples assume MS SQL Server on port `1433` and the default passwords. If your ports or passwords differ, update the examples.
{% endhint %}

#### Set up Quartz on MS SQL Server

1. Open `pentaho/server/pentaho-server/pentaho-solutions/system/scheduler-plugin/quartz/quartz.properties`.

2. In `#_replace_jobstore_properties`, set this value:

   <pre class="language-properties" data-overflow="wrap"><code class="lang-properties">org.quartz.jobStore.driverDelegateClass = org.quartz.impl.jdbcjobstore.MSSQLDelegate
   </code></pre>

3. Save the file.

#### Set Hibernate settings for MS SQL Server

All files below are in `pentaho/server/pentaho-server/pentaho-solutions/system/hibernate`.

1. Open `hibernate-settings.xml`.
2. Update the `<config-file>` value:

   From:

   <pre class="language-xml" data-overflow="wrap" data-expandable="true"><code class="lang-xml">&#x3C;config-file>system/hibernate/postgresql.hibernate.cfg.xml&#x3C;/config-file>
   </code></pre>

   To:

   <pre class="language-xml" data-overflow="wrap" data-expandable="true"><code class="lang-xml">&#x3C;config-file>system/hibernate/sqlserver.hibernate.cfg.xml&#x3C;/config-file>
   </code></pre>
3. Save the file.

#### Replace the default audit log file with the MS SQL Server version

1. Locate `pentaho-solutions/system/dialects/sqlserver/audit_sql.xml`.
2. Copy it to `pentaho-solutions/system`.

#### Modify Jackrabbit repository information for MS SQL Server

1. Open `pentaho/server/pentaho-server/pentaho-solutions/system/jackrabbit/repository.xml`.
2. Uncomment the MS SQL Server sections.
3. Comment out the MySQL, Oracle, and PostgreSQL sections. Use these MS SQL Server sections as your reference:
   * Repository

     <pre class="language-xml" data-overflow="wrap"><code class="lang-xml">&#x3C;FileSystem class="org.apache.jackrabbit.core.fs.db.MSSqlFileSystem">
       &#x3C;param name="driver" value="javax.naming.InitialContext"/>
       &#x3C;param name="url" value="java:comp/env/jdbc/jackrabbit"/>
       &#x3C;param name="schema" value="mssql"/>
       &#x3C;param name="schemaObjectPrefix" value="fs_repos_"/>
     &#x3C;/FileSystem>
     </code></pre>
   * DataStore

     <pre class="language-xml" data-expandable="true"><code class="lang-xml">&#x3C;DataStore class="org.apache.jackrabbit.core.data.db.DbDataStore">
       &#x3C;param name="driver" value="javax.naming.InitialContext"/>
       &#x3C;param name="url" value="java:comp/env/jdbc/jackrabbit"/>
       &#x3C;param name="databaseType" value="mssql"/>
       &#x3C;param name="minRecordLength" value="1024"/>
       &#x3C;param name="maxConnections" value="3"/>
       &#x3C;param name="copyWhenReading" value="true"/>
       &#x3C;param name="tablePrefix" value=""/>
       &#x3C;param name="schemaObjectPrefix" value="ds_repos_"/>
     &#x3C;/DataStore>
     </code></pre>
   * Workspaces

     <pre class="language-xml" data-expandable="true"><code class="lang-xml">&#x3C;FileSystem class="org.apache.jackrabbit.core.fs.db.MSSqlFileSystem">
       &#x3C;param name="driver" value="javax.naming.InitialContext"/>
       &#x3C;param name="url" value="java:comp/env/jdbc/jackrabbit"/>
       &#x3C;param name="schema" value="mssql"/>
       &#x3C;param name="schemaObjectPrefix" value="fs_ws_"/>
     &#x3C;/FileSystem>
     </code></pre>
   * PersistenceManager (workspace)

     <pre class="language-xml" data-expandable="true"><code class="lang-xml">&#x3C;PersistenceManager class="org.apache.jackrabbit.core.persistence.bundle.MSSqlPersistenceManager">
       &#x3C;param name="driver" value="javax.naming.InitialContext"/>
       &#x3C;param name="url" value="java:comp/env/jdbc/jackrabbit"/>
       &#x3C;param name="schema" value="mssql"/>
       &#x3C;param name="schemaObjectPrefix" value="${wsp.name}_pm_ws_"/>
     &#x3C;/PersistenceManager>
     </code></pre>
   * Versioning

     <pre class="language-xml" data-expandable="true"><code class="lang-xml">&#x3C;FileSystem class="org.apache.jackrabbit.core.fs.db.MSSqlFileSystem">
       &#x3C;param name="driver" value="javax.naming.InitialContext"/>
       &#x3C;param name="url" value="java:comp/env/jdbc/jackrabbit"/>
       &#x3C;param name="schema" value="mssql"/>
       &#x3C;param name="schemaObjectPrefix" value="fs_ver_"/>
     &#x3C;/FileSystem>
     </code></pre>
   * PersistenceManager (versioning)

     <pre class="language-xml" data-expandable="true"><code class="lang-xml">&#x3C;PersistenceManager class="org.apache.jackrabbit.core.persistence.bundle.MSSqlPersistenceManager">
       &#x3C;param name="driver" value="javax.naming.InitialContext"/>
       &#x3C;param name="url" value="java:comp/env/jdbc/jackrabbit"/>
       &#x3C;param name="schema" value="mssql"/>
       &#x3C;param name="schemaObjectPrefix" value="pm_ver_"/>
     &#x3C;/PersistenceManager>
     </code></pre>
   * DatabaseJournal

     <pre class="language-xml" data-expandable="true"><code class="lang-xml">&#x3C;Journal class="org.apache.jackrabbit.core.journal.MSSqlDatabaseJournal">
       &#x3C;param name="revision" value="${rep.home}/revision.log" />
       &#x3C;param name="url" value="java:comp/env/jdbc/jackrabbit"/>
       &#x3C;param name="driver" value="javax.naming.InitialContext"/>
       &#x3C;param name="schema" value="mssql"/>
       &#x3C;param name="schemaObjectPrefix" value="cl_j_"/>
       &#x3C;param name="janitorEnabled" value="true"/>
       &#x3C;param name="janitorSleep" value="86400"/>
       &#x3C;param name="janitorFirstRunHourOfDay" value="3"/>
     &#x3C;/Journal>
     </code></pre>

{% endstep %}

{% step %}

### Perform Tomcat-specific connection tasks

After repository configuration, configure your web application server to connect to the repository. This includes JDBC and JNDI connections for Hibernate, Jackrabbit, and Quartz.

{% hint style="info" %}
**Note**: Pentaho Server is configured for Tomcat by default. Update `context.xml` only if you changed default ports, hosts, or passwords.
{% endhint %}

#### Download the JDBC driver and apply it to Pentaho Server

You must provide a JDBC driver for MS SQL Server. Pentaho cannot redistribute all third-party drivers.

1. Download a JDBC driver `.jar` from Microsoft or a third-party provider.
2. If needed, confirm the driver is supported. See [JDBC drivers reference](https://docs.pentaho.com/install/jdbc-drivers-reference).
3. Copy the JDBC driver `.jar` to `pentaho/server/pentaho-server/tomcat/lib`.
4. (Optional) Copy `hsqldb-2.3.2.jar` to `pentaho/server/pentaho-server/tomcat/lib` to keep the sample data.

#### Modify JDBC connection information in the Tomcat `context.xml` file

{% hint style="warning" %}
**Important**: If your host, port, username, password, or driver class differs, update the values below. Also comment out resources for databases you do not use.
{% endhint %}

1. Find the JDBC driver class name and connection string in your database documentation.

2. Open `server/pentaho-server/tomcat/webapps/pentaho/META-INF/context.xml`.

3. Add or update the resources if they do not already exist.

   <pre class="language-xml" data-overflow="wrap" data-expandable="true"><code class="lang-xml">&#x3C;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="com.microsoft.sqlserver.jdbc.SQLServerDriver"
           url="jdbc:sqlserver://localhost:1433;databaseName=hibernate"
           validationQuery="select 1" jdbcInterceptors="ConnectionState" defaultAutoCommit="true"/>
   &#x3C;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="com.microsoft.sqlserver.jdbc.SQLServerDriver"
           url="jdbc:sqlserver://localhost:1433;databaseName=hibernate"
           validationQuery="select 1" />
   &#x3C;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="pentaho_user" password="password" testOnBorrow="true"
           driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
           url="jdbc:sqlserver://localhost:1433;databaseName=quartz"
           validationQuery="select 1"/>
   &#x3C;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="hibuser" password="password"
           driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
           url="jdbc:sqlserver://localhost:1433;databaseName=hibernate"
           validationQuery="select 1"/>
   &#x3C;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="hibuser" password="password"
           driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
           url="jdbc:sqlserver://localhost:1433;databaseName=pentaho_operations_mart"
           validationQuery="select 1"/>
   &#x3C;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="hibuser" password="password"
           driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
           url="jdbc:sqlserver://localhost:1433;databaseName=hibernate"
           validationQuery="select 1"/>
   &#x3C;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"/>
   &#x3C;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"/>
   &#x3C;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"
           validationQuery="select 1" jdbcInterceptors="ConnectionState" defaultAutoCommit="true"/>
   </code></pre>

4. Verify `validationQuery="select 1"` is correct for your database.

5. Save the file.

After you have prepared your Pentaho Repository, go to [Step 3: Configure and start the Pentaho Server after manual installation](https://docs.pentaho.com/install/pentaho-installation-overview-cp/manual-installation/step-3-configure-and-start-the-pentaho-server-after-manual-installation).
{% endstep %}
{% endstepper %}