Ctrlk
Try Pentaho! - Start your 30 day evaluation today
For the complete documentation index, see llms.txt. This page is also available as Markdown.

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.

Important: If you use examples or scripts, update usernames, passwords, and ports to match your environment.

Prepare the Pentaho Repository for your host database:

Use PostgreSQL as your repository database (manual installation)

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

1

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.

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:

Change default passwords

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

Note: If you are evaluating Pentaho, you can skip this step.

  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.

You may need administrator permissions to run these scripts on the host OS.

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 section.

  2. If Pentaho Server is running, stop it. See 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.

Note: If you do not have pentaho_mart_postgresql.sql, make sure you unpacked the Pentaho Operations Mart SQL file during installation.

Verify PostgreSQL initialization

Note: If you did not change the default password, the default password for the users below is password.

  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.

    Note: The Jackrabbit tables may not appear until after you start Pentaho for the first time.

  5. Log in as pentaho_user, then verify you can see the tables under quartz.

You have initialized the PostgreSQL Pentaho Repository database.

2

Configure the PostgreSQL Pentaho Repository database

Configure Quartz, Hibernate, Jackrabbit, and Operations Mart for PostgreSQL.

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.

  • If you use a different port or password, update the examples to match your environment.

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:

    org.quartz.jobStore.driverDelegateClass = org.quartz.impl.jdbcjobstore.PostgreSQLDelegate

  3. Save the file.

Set Hibernate settings for PostgreSQL

Hibernate settings point Pentaho to the correct Hibernate configuration file.

Note: Hibernate also stores the audit logs used by Operations Mart.

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:

    <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

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

    • PersistenceManager (workspaces)

      <PersistenceManager class="org.apache.jackrabbit.core.persistence.bundle.PostgreSQLPersistenceManager">
  <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="${wsp.name}_pm_ws_"/>
</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="postgresql"/>
  <param name="schemaObjectPrefix" value="cl_j_"/>
  <param name="janitorEnabled" value="true"/>
  <param name="janitorSleep" value="86400"/>
  <param name="janitorFirstRunHourOfDay" value="3"/>
</Journal>
3

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.

Note: Pentaho Server is configured for Tomcat by default. Only edit Tomcat context.xml if you changed default ports or passwords.

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.

Note: Pentaho cannot redistribute some third-party database drivers. Download the driver from your database vendor.

  1. Download a JDBC driver JAR from your database vendor. See 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.

Important: If your port, password, username, driver class, or host differs from the defaults, update the examples.

  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:

  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.

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

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

1

Initialize the MySQL or MariaDB Pentaho Repository database

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

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:

Change default passwords

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

Note: If you are evaluating Pentaho, you might skip this step.

  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.

Note: You might need administrator permissions on the host OS.

  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 section.

  2. If the Pentaho Server is running, stop it. See 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

Note: The original QRTZ5_ tables are retained. Delete them only after you confirm migration success.

Verify MySQL or MariaDB initialization

Note: If you did not change the default passworkd, it is password.

  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.

    Note: Jackrabbit tables might not appear until the first server start.

  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.

2

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.

Important: If your ports or passwords differ, update the examples. Match your environment values.

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:

  3. Save the file.

Set Hibernate settings for MySQL or MariaDB

Hibernate settings point Pentaho to the correct Hibernate configuration file.

Note: Hibernate also stores the audit logs used by Operations Mart.

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:

    To:

  3. Save and close the file.

  4. (MariaDB only) Open mysql5.hibernate.cfg.xml.

  5. Change the driver class:

    From:

    To:

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

    • DataStore

    • Workspaces

    • PersistenceManager (workspaces)

    • DatabaseJournal

3

Perform Tomcat-specific connection tasks

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

Note: Pentaho Server is configured for Tomcat by default. Modify context.xml only if you changed ports or passwords.

Download the driver and apply it to the Pentaho Server

You need a JDBC driver for your repository database.

Note: Due to licensing restrictions, you must download some drivers yourself.

  1. Download a JDBC driver JAR from your database vendor or a third-party provider. See 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.

Important: If your port, password, user, driver class, or host differs, update the examples. Match your environment values.

  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

    • MariaDB

  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.

Use Oracle as your repository database (manual installation)

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

1

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.

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:

Change default passwords

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

Note: If you are evaluating Pentaho, you can skip this step.

  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.

Important: Back up your data before proceeding.

  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 section.

  2. If the Pentaho Server is running, stop it. See 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.

Note: The original QRTZ5_ tables remain. Delete them only after you confirm the migration.

Verify Oracle initialization

Note: Unless you changed it in Step 1, the default password for each username is password.

  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.

2

Configure the Oracle Pentaho Repository database

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

Important: If your ports or passwords differ, update the examples.

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:

  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:

    To:

  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

    • DataStore

    • Workspaces

    • PersistenceManager (workspaces)

    • Versioning

    • PersistenceManager (versioning)

    • DatabaseJournal

3

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.

Note: Pentaho Server is configured for Tomcat by default. Update context.xml only if you changed default ports, hosts, or passwords.

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.

  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

Important: Update host, port, username, password, and database identifiers to match your environment. Also comment out resources for databases you do not use.

  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:

  4. Save the file.

After you have prepared your Pentaho Repository, go to Step 3: Configure and start the Pentaho Server after manual installation.

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

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

1

Initialize the MySQL or MariaDB Pentaho Repository database

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

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:

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.

Note: If you are evaluating Pentaho, you can skip this step.

  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.

Important: Back up your data before proceeding.

  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 section.

  2. If the Pentaho Server is running, stop it. See 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.

    Note: The original QRTZ5_ tables remain. Delete them only after you confirm the migration.

Verify MS SQL Server initialization

Note: If you did not change it, the default password for each username is password.

  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.

2

Configure the MS SQL Server Pentaho Repository database

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

Important: These examples assume MS SQL Server on port 1433 and the default passwords. If your ports or passwords differ, update the examples.

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:

  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:

    To:

  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

    • DataStore

    • Workspaces

    • PersistenceManager (workspace)

    • Versioning

    • PersistenceManager (versioning)

    • DatabaseJournal

3

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.

Note: Pentaho Server is configured for Tomcat by default. Update context.xml only if you changed default ports, hosts, or passwords.

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.

  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

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.

  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.

  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.

PreviousStep 1: Prepare your environment
NextStep 3: Configure and start the Pentaho Server after manual installation

Last updated

Was this helpful?