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

Legacy page. Content moved to the main topic.

This page is kept for existing links. Go to Manual installation process.

Use these steps to set up MS SQL Server as the Pentaho Repository host database.

Before you begin

Prerequisite

Prepare your OS environment first:

• Prepare your Windows environment for a manual installation

• Prepare your Linux environment for a manual installation

Repository components

The Pentaho Repository database includes:

• Jackrabbit Stores the solution repository, examples, security data, and report content.

• Quartz Stores schedules for reports and jobs.

• Hibernate Stores audit logging data.

• (Optional) Pentaho Operations Mart Stores usage and performance data.

Initialize the MS SQL Server Pentaho Repository database

You will run SQL scripts to create the Hibernate, Quartz, Jackrabbit (JCR), and Pentaho Operations Mart schemas.

Use the ASCII character set when you run these scripts. Do not use UTF-8. Some scripts can fail due to string length limits.

If your usernames, passwords, or ports differ, update the examples first.

Pentaho 10.2.0.0 and earlier use Quartz 1.x tables with a QRTZ5_ prefix. Pentaho 10.2.0.1 and later use Quartz 2.x tables with a QRTZ6_ prefix.

When upgrading to 10.2.0.1+ you must create the QRTZ6_ tables. You can optionally migrate existing schedules with migrate_old_quartz_data_sqlserver.sql.

Failure to complete this Quartz upgrade can prevent the server from starting. You may see Missing Quartz library database error in catalina.log.

1

Step 1: 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.

2

Step 2: Change default passwords

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

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.

3

Step 3: Run SQL scripts

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

Back up your data before proceeding.

1. Back up your data. See Back up your existing Pentaho products and install Pentaho 11.0.

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.

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

4

Step 4: Verify MS SQL Server initialization

Unless you changed it in Step 1, 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.

Configure the MS SQL Server Pentaho Repository database

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

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

1

Step 1: Set up Quartz on MS SQL Server

Quartz schedule data is stored in the Quartz JobStore. You configure the JobStore in quartz.properties.

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

2. In #_replace_jobstore_properties, set this value:

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

3. Save the file.

2

Step 2: Set Hibernate settings for MS SQL Server

Hibernate configuration includes the driver, connection, dialect, and connection handling. The Hibernate database also stores audit logs for Operations Mart.

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:

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

   To:

   <config-file>system/hibernate/sqlserver.hibernate.cfg.xml</config-file>

3. Save the file.

3

Step 3: Replace the default audit log file with the MS SQL Server version

Replace the audit SQL file to match MS SQL Server.

1. Locate pentaho-solutions/system/dialects/sqlserver/audit_sql.xml.

2. Copy it to pentaho-solutions/system.

4

Step 4: Modify Jackrabbit repository information for MS SQL Server

Update repository.xml to use MS SQL Server for the Jackrabbit repository.

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

<FileSystem class="org.apache.jackrabbit.core.fs.db.MSSqlFileSystem">
  <param name="driver" value="javax.naming.InitialContext"/>
  <param name="url" value="java:comp/env/jdbc/jackrabbit"/>
  <param name="schema" value="mssql"/>
  <param name="schemaObjectPrefix" value="fs_repos_"/>
</FileSystem>

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="mssql"/>
  <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.MSSqlFileSystem">
  <param name="driver" value="javax.naming.InitialContext"/>
  <param name="url" value="java:comp/env/jdbc/jackrabbit"/>
  <param name="schema" value="mssql"/>
  <param name="schemaObjectPrefix" value="fs_ws_"/>
</FileSystem>

PersistenceManager (workspace)

<PersistenceManager class="org.apache.jackrabbit.core.persistence.bundle.MSSqlPersistenceManager">
  <param name="driver" value="javax.naming.InitialContext"/>
  <param name="url" value="java:comp/env/jdbc/jackrabbit"/>
  <param name="schema" value="mssql"/>
  <param name="schemaObjectPrefix" value="${wsp.name}_pm_ws_"/>
</PersistenceManager>

Versioning

<FileSystem class="org.apache.jackrabbit.core.fs.db.MSSqlFileSystem">
  <param name="driver" value="javax.naming.InitialContext"/>
  <param name="url" value="java:comp/env/jdbc/jackrabbit"/>
  <param name="schema" value="mssql"/>
  <param name="schemaObjectPrefix" value="fs_ver_"/>
</FileSystem>

PersistenceManager (versioning)

<PersistenceManager class="org.apache.jackrabbit.core.persistence.bundle.MSSqlPersistenceManager">
  <param name="driver" value="javax.naming.InitialContext"/>
  <param name="url" value="java:comp/env/jdbc/jackrabbit"/>
  <param name="schema" value="mssql"/>
  <param name="schemaObjectPrefix" value="pm_ver_"/>
</PersistenceManager>

DatabaseJournal

<Journal class="org.apache.jackrabbit.core.journal.MSSqlDatabaseJournal">
  <param name="revision" value="${rep.home}/revision.log" />
  <param name="url" value="java:comp/env/jdbc/jackrabbit"/>
  <param name="driver" value="javax.naming.InitialContext"/>
  <param name="schema" value="mssql"/>
  <param name="schemaObjectPrefix" value="cl_j_"/>
  <param name="janitorEnabled" value="true"/>
  <param name="janitorSleep" value="86400"/>
  <param name="janitorFirstRunHourOfDay" value="3"/>
</Journal>

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.

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

1

Step 1: 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.

2

Step 2: Modify JDBC connection information in the Tomcat context.xml file

Connection details for your repository database are stored in context.xml.

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.

   <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"/>
   <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" />
   <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"/>
   <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"/>
   <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"/>
   <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"/>
   <Resource name="jdbc/SampleData" auth="Container" type="javax.sql.DataSource"
           factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory"
           maxActive="20" minIdle="0" maxIdle="5" initialSize="0"
           maxWait="10000" username="pentaho_user" password="password"
           driverClassName="org.hsqldb.jdbcDriver" url="jdbc:hsqldb:hsql://localhost/sampledata"
           validationQuery="select 1"/>
   <Resource name="jdbc/SampleDataAdmin" auth="Container" type="javax.sql.DataSource"
           factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory"
           maxActive="20" minIdle="0" maxIdle="5" initialSize="0"
           maxWait="10000" username="pentaho_admin" password="password"
           driverClassName="org.hsqldb.jdbcDriver" url="jdbc:hsqldb:hsql://localhost/sampledata"
           validationQuery="select 1"/>
   <Resource name="jdbc/jackrabbit" auth="Container" type="javax.sql.DataSource"
           factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory"
           maxActive="20" minIdle="0" maxIdle="5" initialSize="0"
           maxWait="10000" username="jcr_user" password="password"
           driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
           url="jdbc:sqlserver://localhost:1433;databaseName=jackrabbit"
           validationQuery="select 1" jdbcInterceptors="ConnectionState" defaultAutoCommit="true"/>

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

5. Save the file.

Next steps

Continue with Configure and start the Pentaho Server after manual installation.