Use MS SQL Server as Your Repository Database (Archive Installation)

Step 1: Set Up Quartz on MS SQL Server Pentaho Repository Database

Event information, such as scheduled reports, is stored in the Quartz JobStore. During the installation process, you must indicate where the JobStore is located, by modifying the quartz.properties file using the following steps.

1. Open the pentaho/server/pentaho-server/pentaho-solutions/system/quartz/quartz.properties file in any text editor.
2. Locate the #_replace_jobstore_properties section and set the org.quartz.jobStore.driverDelegateClass as shown below.

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

3. Locate the # Configure Datasources section and set the org.quartz.dataSource.myDS.jndiURL equal to Quartz, as follows.

   org.quartz.dataSource.myDS.jndiURL = Quartz
   

4. Save the file and close the text editor.

Step 2: Set Hibernate Settings for MS SQL Server

Modify the Hibernate settings file to specify where Pentaho should find the repository’s Hibernate configuration file.

The Hibernate configuration file specifies driver and connection information, as well as dialects and how to handle connection closes and timeouts.

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

Perform the following steps to specify where Pentaho can find the Hibernate configuration file.

1. Open the hibernate-settings.xml file in a text editor. Find the <config-file> tags and change postgresql.hibernate.cfg.xml to sqlserver.hibernate.cfg.xml as shown.
   From:

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

   To:

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

2. Save and close the file.
3. Open the sqlserver.hibernate.cfg.xml file in a text editor.
4. Make sure that the password and port number match the ones you specified in your configuration. Make changes if necessary, then 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, you need to replace the audit_sql.xml file with one that is configured for the MS SQL Server.

1. Locate the pentaho-solutions/system/dialects/sqlserver/audit_sql.xml file.
2. Copy it into the pentaho-solutions/system directory.

Step 4: Modify Jackrabbit Repository Information for MS SQL Server

Change the default jackrabbit repository to MS SQL Server using the following steps.

1. Navigate to the pentaho/server/pentaho-server/pentaho-solutions/system/jackrabbit and open the repository.xml file with any text editor.
2. Following the table below, locate and change the code so that the MS SQL Server lines are not commented out, but the MySQL, PostgreSQL and Oracle lines are commented out.

If you have a different port or different password, make sure that you change the password and port number in these examples to match those in your configuration.

<FileSystem class="org.apache.jackrabbit.core.fs.db.MSSqlFileSystem">
     <param name="driver" value="com.microsoft.sqlserver.jdbc.SQLServerDriver"/>
     <param name="url" value="jdbc:sqlserver://localhost:1433;DatabaseName=jackrabbit"/>
…
<param name="schema" value="mssql"/>
</FileSystem>

<DataStore class="org.apache.jackrabbit.core.data.db.DbDataStore">
    <param name="url" value="jdbc:sqlserver://localhost:1433;DatabaseName=jackrabbit"/>
…
<param name="schema" value="mssql"/>
</DataStore>

<FileSystem class="org.apache.jackrabbit.core.fs.db.MSSqlFileSystem">
      <param name="driver" value="com.microsoft.sqlserver.jdbc.SQLServerDriver"/>
      <param name="url" value="jdbc:sqlserver://localhost:1433;DatabaseName=jackrabbit"/>
…
<param name="schema" value="mssql"/>
</FileSystem>

<PersistenceManager class="org.apache.jackrabbit.core.persistence.bundle.MSSqlPersistenceManager">
      <param name="url" value="jdbc:sqlserver://localhost:1433;DatabaseName=jackrabbit"/>
…
<param name="schema" value="mssql"/>
</PersistenceManager>

<FileSystem class="org.apache.jackrabbit.core.fs.db.MSSqlFileSystem">
      <param name="driver" value="com.microsoft.sqlserver.jdbc.SQLServerDriver"/>
      <param name="url" value="jdbc:sqlserver://localhost:1433;DatabaseName=jackrabbit"/>
…
<param name="schema" value="mssql"/>
</FileSystem>

<PersistenceManager class="org.apache.jackrabbit.core.persistence.bundle.MSSqlPersistenceManager">
      <param name="url" value="jdbc:sqlserver://localhost:1433;DatabaseName=jackrabbit"/>
      …
<param name="schema" value="mssql"/>
</PersistenceManager>

<Journal class="org.apache.jackrabbit.core.journal.MSSqlDatabaseJournal">
    <param name="revision" value="${rep.home}/revision.log"/>
    <param name="url" value="jdbc:sqlserver://localhost:1433;DatabaseName=jackrabbit"/>
    <param name="driver" value="com.microsoft.sqlserver.jdbc.SQLServerDriver"/>
    <param name="user" value="jcr_user"/>
    <param name="password" value="password"/>
    <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>

Step 1: Download Driver and Apply to the Pentaho Server

To connect to a database, including the Pentaho Repository database, you will need to download and copy a JDBC driver to the appropriate places for Pentaho Server as well as on the web application server. 

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

1. Download a JDBC driver JAR from your database vendor or a third-party driver developer.
2. Copy the JDBC driver JAR you just downloaded to the pentaho/server/pentaho-server/tomcat/lib folder.
3. Copy the hsqldb-2.3.2.jar file to pentaho-server/tomcat/lib if you want to retain the sample provided by Pentaho.

Step 2: Modify JDBC Connection Information in the Tomcat context.xml File

Database connection and network information, such as the username, password, driver class information, IP address or domain name, and port numbers for your Pentaho Repository database are stored in the context.xml file. Modify this file to reflect the database connection and network information to reflect your operating environment. If you have chosen to use a Pentaho Repository database other than MS SQL, modify the values for the validationQuery parameters in this file.

If you have a different user or password, make sure that you change the user and password in these examples to match those in your configuration environment.

1. Consult your database documentation to determine the JDBC class name and connection string for your Pentaho Repository database.
2. Go to the pentaho-server/tomcat/webapps/pentaho/META-INF directory and open the context.xml file with any file editor.
3. Comment out the resource references that refer to databases other than MS SQL Server, such as PostgreSQL, MySQL, and Oracle. Then, add the following code to the file if it does not already exist. Be sure to adjust the port numbers and passwords to reflect your environment, if necessary.

   <Resource validationQuery="select 1" url="jdbc:sqlserver://localhost:1433;DatabaseName=hibernate" driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver" password="password" username="hibuser" initialSize="0" maxActive="20" maxIdle="10" maxWait="10000" factory="org.apache.tomcat.jdbc.pool.DataSourceFactory" type="javax.sql.DataSource" auth="Container" name="jdbc/Hibernate"/>
   
   <Resource validationQuery="select 1" url="jdbc:sqlserver://localhost:1433;DatabaseName=hibernate" driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver" password="password" username="hibuser" initialSize="0" maxActive="20" maxIdle="10" maxWait="10000" factory="org.apache.tomcat.jdbc.pool.DataSourceFactory" type="javax.sql.DataSource" auth="Container" name="jdbc/Audit"/>
   
   <Resource validationQuery="select 1" url="jdbc:sqlserver://localhost:1433;DatabaseName=quartz" driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver" password="password" username="pentaho_user" testOnBorrow="true" initialSize="0" maxActive="20" maxIdle="10" maxWait="10000" factory="org.apache.tomcat.jdbc.pool.DataSourceFactory" type="javax.sql.DataSource" auth="Container" name="jdbc/Quartz"/>
   
   <Resource validationQuery="select 1" url="jdbc:sqlserver://localhost:1433;DatabaseName=pentaho_operations_mart" driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver" password="password" username="pentaho_operations_mart" initialSize="0" maxActive="20" maxIdle="10" maxWait="10000" factory="org.apache.tomcat.jdbc.pool.DataSourceFactory" type="javax.sql.DataSource" auth="Container" name="jdbc/pentaho_operations_mart"/>
   
   <Resource validationQuery="select 1" url="jdbc:sqlserver://localhost:1433;DatabaseName=pentaho_operations_mart" driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver" password="password" username="pentaho_operations_mart" initialSize="0" maxActive="20" maxIdle="10" maxWait="10000" factory="org.apache.tomcat.jdbc.pool.DataSourceFactory" type="javax.sql.DataSource" auth="Container" name="jdbc/PDI_Operations_Mart"/>
   

4. Modify the username, password, driver class information, IP address (or domain name), and port numbers so they reflect the correct values for your environment.
5. Make sure that the validationQuery variable for your database is set as follows: validationQuery="select 1"
6. Save the context.xml file, then close it.