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

This article includes the steps for using MS SQL Server as the host database for the Pentaho Server in an archive installation.

Before you begin

Prerequisites

Before you prepare your Pentaho Repository, prepare your environment:

• Prepare your Windows environment for an archive install

• Prepare your Linux environment for an archive install

Components

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

It consists of these components:

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

• Quartz Holds data related to scheduling reports and jobs.

• Hibernate Holds data related to audit logging.

• (Optional) Pentaho Operations Mart Reports on system usage and performance.

Initialize MS SQL Server Pentaho Repository database

The sections in this topic take you through the steps to initialize the MS SQL Server Pentaho Repository database.

To initialize MS SQL Server so it serves as the Pentaho Repository, run SQL scripts to create the Hibernate, Quartz, Jackrabbit (JCR), and Pentaho Operations Mart components.

CAUTION: Use the ASCII character set when you run these scripts. Do not use UTF-8. Text string length limitations can cause the scripts to fail.

CAUTION: If you have a different password or user, update the examples to match your configuration.

CAUTION: Pentaho 10.2.0.0 and earlier use the Quartz 1.x library, which uses a QRTZ5_ prefix in the database. Pentaho 10.2.0.1 and later use Quartz 2.x, which uses a QRTZ6_ prefix. When you upgrade from an earlier version, create the QRTZ6_ tables by repeating the Quartz database creation step. You can optionally migrate schedules with the migration script. Always back up your data before you proceed.

Important: If you do not complete the Quartz upgrade, Pentaho Server fails at startup. You will see a “Missing Quartz library database error” exception in catalina.log.

Step 1: Adjust MS SQL Server configuration settings

Configure these MS SQL Server settings in Microsoft SQL Server Management Studio (or a similar tool).

1. Select SQL Server and Windows Authentication Mode to use mixed authentication.

2. Enable TCP/IP for MS SQL Server.

3. Make sure MS SQL Server listens on an external IP, not localhost.

Step 2: Change default passwords

For production, follow best practices to change the default passwords in the SQL scripts.

Note: If you are evaluating Pentaho, you may want to skip this step.

1. Browse to the <*your pentaho directory*>/pentaho-server/data/sqlserver folder.

2. Modify these create scripts as needed for your existing setup (user, password, database, and other required information):

   • create_jcr_sqlServer.sql

   • create_quartz_sqlServer.sql

   • create_repository_sqlServer.sql

   • pentaho_mart_sqlserver.sql

3. Save and close the files.

Step 3: Run SQL scripts

When upgrading from previous Pentaho versions (including 10.2.0.0 GA to 10.2.0.1 and later), manually initialize a new Quartz database. This creates a new Quartz library in the repository database. Back up your data before you proceed.

Run the SQL creation scripts.

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

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. For instructions, see Stop and start the Pentaho Server and repository.

3. Make sure your MS SQL Server instance is running.

4. Open a sqlcmd utility window, or run these scripts from Microsoft SQL Server Management Studio, in this order:

   1. <*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

   The Quartz database, Jackrabbit and MS SQL Server repositories, and Pentaho Operations Mart are created. The new Quartz scheduler table prefixed with QRTZ6_ is created. Any existing QRTZ5_ scheduler database is retained. Starting the Pentaho Server at this point results in an empty schedule.

5. If you want to retain your existing scheduler database, open the migrate_old_quartz_data_sqlserver.sql file.

6. Modify the migration script as needed for your existing setup (user, password, database, and other required information), then save it.

7. Run the migration script: <*your pentaho directory*>/pentaho-server/data/sqlserver/migrate_old_quartz_data_sqlserver.sql

The original Quartz scheduler database is migrated to the new Quartz database.

Note: The original QRTZ5_ data is retained. You can delete it after you confirm migration is successful.

Note: You unpacked the Pentaho Operations Mart SQL file while preparing your environment. See Prepare your Windows environment for an archive install or Prepare your Linux environment for an archive install.

Step 4: Verify MS SQL Server initialization

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

After you run the scripts, verify that the databases and user roles were created properly:

1. Open MS SQL Server Management Studio.

2. Log in as hibuser.

3. In Object Explorer, navigate to hibernate and verify that you can see the tables.

4. If you installed Pentaho Operations Mart, log in as pentaho_operations_mart.

5. In Object Explorer, navigate to pentaho_operations_mart and verify that you can see the tables.

6. Log in as jcr_user.

7. In Object Explorer, navigate to jackrabbit and verify that you can see the tables. The Jackrabbit tables may not appear until you start Pentaho for the first time.

8. Log in as pentaho_user.

9. In Object Explorer, navigate to quartz and verify that you can see the tables.

10. Exit MS SQL Server Management Studio.

Configure MS SQL Server Pentaho Repository database

After you initialize your repository database, configure Quartz, Hibernate, Jackrabbit, and Pentaho Operations Mart for an MS SQL Server database.

By default, the examples in this section assume MS SQL Server running on port 1433. They also use the default password.

CAUTION: If you use a different port or password, update the examples to match your configuration.

Step 1: Set up Quartz on MS SQL Server

Event information, such as scheduled reports, is stored in the Quartz JobStore. During installation, indicate where the JobStore is located by modifying quartz.properties.

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

2. In the #_replace_jobstore_properties section, set org.quartz.jobStore.driverDelegateClass:

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

3. Save the file.

Step 2: Set Hibernate settings for MS SQL Server

Modify the Hibernate settings file to specify where Pentaho should find the Pentaho Repository Hibernate configuration file.

Note: The Hibernate database is also where Pentaho Server stores the audit logs used as source data for Pentaho Operations Mart.

The files in this section are located in pentaho/server/pentaho-server/pentaho-solutions/system/hibernate.

1. Open hibernate-settings.xml.

2. Find the <config-file> tags. Change postgresql.hibernate.cfg.xml to sqlserver.hibernate.cfg.xml.

   From:

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

   To:

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

3. Save and close the file.

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

Since you are using MS SQL Server to host the Pentaho Repository, replace audit_sql.xml with the MS SQL Server version.

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

2. Copy it into the pentaho-solutions/system directory.

Step 4: Modify Jackrabbit repository information for MS SQL Server

Edit repository.xml to change the default Jackrabbit repository to MS SQL Server.

1. Navigate to pentaho/server/pentaho-server/pentaho-solutions/system/jackrabbit.

2. Open repository.xml.

3. Locate and verify or change the configuration so that the MS SQL Server lines are uncommented, and the MySQL, Oracle, and PostgreSQL lines are commented out.

Item	Code Section
Repository	<FileSystem class="org.apache.jackrabbit.core.fs.db.MSSqlFileSystem"> <param name="driver" value="javax.naming.InitialContext"/> <param name="url" value="java:comp/env/jdbc/jackrabbit"/> <param name="schema" value="mssql"/> <param name="schemaObjectPrefix" value="fs_repos_"/> </FileSystem>
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 (1st part)	<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 (2nd part)	<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 your repository is configured, configure your web application server to connect to the Pentaho Repository.

In this step, you make JDBC and JNDI connections to the Hibernate, Jackrabbit, and Quartz components.

Note: By default, Pentaho Server is configured to be deployed and run on Tomcat. Connections are already specified. Modify Tomcat context.xml only if you changed the default ports or passwords.

Step 1: Download driver and apply to the Pentaho Server

To connect to a database (including the Pentaho Repository database), download and copy a JDBC driver to the Pentaho Server and the web application server.

Note: Due to licensing restrictions, Pentaho cannot redistribute some third-party database drivers. You must download and install the file yourself.

1. Download a JDBC driver .jar from your database vendor or a third-party driver developer. See JDBC drivers reference.

2. Copy the JDBC driver .jar to pentaho/server/pentaho-server/tomcat/lib.

3. Copy hsqldb-2.3.2.jar to pentaho-server/tomcat/lib if you want to retain the Pentaho sample data.

Step 2: Modify JDBC connection information in the Tomcat XML file

Database connection and network information (such as user name, password, driver class, host, and port) is stored in context.xml. Modify it to match your environment.

CAUTION: If you use a different user name, password, or port, update the examples to match your environment.

1. Consult your database documentation to determine the JDBC class name and connection string for your Pentaho Repository database.

2. Navigate to server/pentaho-server/tomcat/webapps/pentaho/META-INF.

3. Open context.xml.

4. Add the following resources if they do not already exist:

   <Resource name="jdbc/Hibernate" auth="Container" type="javax.sql.DataSource"
           factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory"
           maxActive="20" maxIdle="10" maxWait="10000"
           initialSize="0" username="hibuser" password="password"
           driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
           url="jdbc:sqlserver://localhost:1433;DatabaseName=hibernate;encrypt=true;trustServerCertificate=false"
           validationQuery="select 1"/>
   <Resource name="jdbc/Audit" auth="Container" type="javax.sql.DataSource"
           factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory"
           maxActive="20" maxIdle="10" maxWait="10000"
           initialSize="0" username="hibuser" password="password"
           driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"        
           url="jdbc:sqlserver://localhost:1433;DatabaseName=hibernate;encrypt=true;trustServerCertificate=false"
           validationQuery="select 1"/>
   <Resource  name="jdbc/Quartz" auth="Container" type="javax.sql.DataSource"
           factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory"
           initialSize="0" maxActive="20" maxIdle="10" maxWait="10000"
           testOnBorrow="true" username="pentaho_user" password="password"
           driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
           url="jdbc:sqlserver://localhost:1433;DatabaseName=quartz;encrypt=true;trustServerCertificate=false"
           validationQuery="select 1"/>
   <Resource name="jdbc/pentaho_operations_mart" auth="Container" type="javax.sql.DataSource"
           factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory"
           maxActive="20" maxIdle="10" maxWait="10000"
           initialSize="0" username="pentaho_operations_mart" password="password"
           driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
           url="jdbc:sqlserver://localhost:1433;DatabaseName=pentaho_operations_mart;encrypt=true;trustServerCertificate=false"
           validationQuery="select 1"/>
   <Resource name="jdbc/PDI_Operations_Mart" auth="Container" type="javax.sql.DataSource"
           factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory"
           maxActive="20" maxIdle="10" maxWait="10000"
           initialSize="0" username="pentaho_operations_mart" password="password"
           driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
           url="jdbc:sqlserver://localhost:1433;DatabaseName=pentaho_operations_mart;encrypt=true;trustServerCertificate=false"
           validationQuery="select 1"/>
   <Resource name="jdbc/live_logging_info" auth="Container" type="javax.sql.DataSource"
           factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory"
           initialSize="0" maxActive="20" maxIdle="10"
           maxWait="10000" username="dilogs_user" password="password"
           driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
           url="jdbc:sqlserver://localhost:1433;DatabaseName=pentaho_dilogs;encrypt=true;trustServerCertificate=false"
           validationQuery="select 1"/>
   <Resource name="jdbc/SampleData" auth="Container" type="javax.sql.DataSource"
           factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory"
           maxActive="20" minIdle="0" maxIdle="5" initialSize="0" maxWait="10000"
           username="pentaho_user" password="password" driverClassName="org.hsqldb.jdbcDriver"
           url="jdbc:hsqldb:hsql://localhost/sampledata" validationQuery="select 1"/>
   <Resource name="jdbc/SampleDataAdmin" auth="Container" type="javax.sql.DataSource"
           factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory"
           maxActive="20" minIdle="0" maxIdle="5" initialSize="0" maxWait="10000"
           username="pentaho_admin" password="password" driverClassName="org.hsqldb.jdbcDriver"
           url="jdbc:hsqldb:hsql://localhost/sampledata" validationQuery="select 1"/>
   <Resource name="jdbc/jackrabbit" auth="Container" type="javax.sql.DataSource"
           factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory"
           maxActive="20" minIdle="0" maxIdle="5" initialSize="0"
           maxWait="10000" username="jcr_user" password="password"
           driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
           url="jdbc:sqlserver://localhost:1433;DatabaseName=jackrabbit;encrypt=true;trustServerCertificate=false"
           validationQuery="select 1"/>

   Note: Depending on your MS SQL Server configuration, you may be able to set trustServerCertificate=true. See Connecting with encryption.

5. Modify user names, passwords, driver class information, host, and port to match your environment.

6. Comment out any resource references that refer to other databases.

7. Verify your database validation query is set to validationQuery="select 1".

8. Save and close context.xml.

Start your server

After you complete the archive installation steps, start Pentaho Server.

See Starting the Pentaho Server after an archive installation.