Skip to main content
Pentaho Documentation

Use MS SQL Server as Your Repository Database

Overview

Explains how to configure MS SQL Server for use as a repository database.

Before you prepare your Business Analytics (BA) Repository, complete the tasks in Prepare Environment.

The DI Repository resides on the database that you installed during the Prepare Environment step, and consists of three repositories: Jackrabbit, Quartz, and Hibernate.

  • Jackrabbit contains the solution repository, examples, security data, and content data from reports that you use Pentaho software to create.
  • Quartz holds data that is related to scheduling reports and jobs.
  • Hibernate holds data that is related to audit logging.

Initialize MS SQL Server DI Repository Database

To initialize MS SQL Server so that it serves as the DI Repository, you will need to run a few SQL scripts to create the Hibernate, Quartz, and Jackrabbit (JCR) databases.

Use the ASCII character set when you run these scripts. Do not use UTF-8 because there are text string length limitations that might cause the scripts to fail.

The next few sections take you through the steps to initialize the MS SQL Server DI repository database.

Adjust MS SQL Server Configuration Settings 

Configure the following MS SQL Server settings in Microsoft SQL Server Management Studio or other tool of your choice. 

  • Select SQL Server and Windows Authentication Mode ​to use mixed authentication.
  • Enable TCP/IP for MS SQL Server.
  • Make sure that MS SQL Server is listening on an external IP, and not localhost.

Change Default Passwords

We recommend that you change the default passwords in the SQL script files. If you are evaluating Pentaho, you might want to skip this step.

If you do decide to make the databases more secure, use any text editor to change the passwords in these files:

  • pentaho/server/data-integration-server/data/sqlserver/create_jcr_sqlServer.sql
  • pentaho/server/data-integration-server/data/sqlserver/create_quartz_sqlServer.sql
  • pentaho/server/data-integration-server/data/sqlserver/create_repository_sqlServer.sql

Run SQL Scripts

Once you change the passwords, you will need to run these SQL scripts. You will need administrator permissions on the server in order to run these scripts. The process for running SQL scripts is the same for Windows or Linux machines. The list of  SQL scripts is shown in the table below.

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

Run the scripts from the sqlcmd utility window or from Microsoft SQL Server Management Studio.

Action SQL Script
Create Quartz -i <filepath to DDL>\create_quartz_sqlServer.sql
Create Hibernate repository -i <filepath to DDL>\create_repository_sqlServer.sql
Create Jackrabbit -i <filepath to DDL>\create_jcr_sqlServer.sql

Verify MS SQL Server Initialization

After you run the scripts, this list will help you verify that databases, users, and logins have been created.

  1. Open MS SQL Server Management Studio.
  2. In the Object Explorer section of the window, make sure that the Quartz, Jackrabbit (JCR), and Hibernate databases are present.
  3. Navigate to Security > Logins and make sure that the appropriate users have been created. 
  4. Exit from MS SQL Server Management Studio tool.

Configure MS SQL Server DI Repository Database

Now that you have initialized your repository database, you will need to configure Quartz, Hibernate, and Jackrabbit for a MS SQL Server database.

By default, the examples in this section are for a MS SQL Server database that runs on port 1433. The default password is also in these examples.

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

Set Up Quartz on MS SQL Server DI 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.

  1. Open the pentaho/server/data-integration-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 here.
    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, like this.
    org.quartz.dataSource.myDS.jndiURL = Quartz
    
  4. Save the file and close the text editor.

Set Hibernate Settings for MS SQL Server

Modify the hibernate settings file to specify where Pentaho should find the DI Repository’s hibernate config file.

The hibernate config 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/data-integration-server/pentaho-solutions/system/hibernate directory.

  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.

Replace Default Version of Audit Log File with MS SQL Server Version

Since you are using MS SQL Server to host the DI Repository, you need to replace the audit_sql.xml file with one that is configured for MS SQL Server.

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

Modify Jackrabbit Repository Information for MS SQL Server

There are parts of code that you will need to alter in order to change the default JCR repository to MS SQL Server.

  1. Navigate to the pentaho/server/data-integration-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 the ones in your configuration.

Item: Code Section:
Repository
<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=di_jackrabbit"/>
…
<param name="schema" value="mssql"/>
</FileSystem>
DataStore
<DataStore class="org.apache.jackrabbit.core.data.db.DbDataStore">
    <param name="url" value="jdbc:sqlserver://localhost:1433;DatabaseName=di_jackrabbit"/>
…
<param name="schema" value="mssql"/>
</DataStore>
Workspaces
<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=di_jackrabbit"/>
…
<param name="schema" value="mssql"/>
</FileSystem>

PersistenceManager

(1st part)

<PersistenceManager class="org.apache.jackrabbit.core.persistence.bundle.MSSqlPersistenceManager">
      <param name="url" value="jdbc:sqlserver://localhost:1433;DatabaseName=di_jackrabbit"/>
…
<param name="schema" value="mssql"/>
</PersistenceManager>
Versioning
<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=di_jackrabbit"/>
…
<param name="schema" value="mssql"/>
</FileSystem>

PersistenceManager

(2nd part)

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

Journal

 

<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=di_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_"/>
</Journal>

Perform Tomcat-Specific Connection Tasks

After your repository has been configured, you must configure the web application servers to connect to the DI Repository. In this step, JDBC and JNDI connections are made to the Hibernate, Jackrabbit, and Quartz databases.

By default, the DI Server software is configured to be deployed and run on the Tomcat server. As such, connections have already been specified and only the Tomcat context.xml file must be modified.

If you plan to run the DI Server on Tomcat, you must modify JDBC Connection information.

Download and Install Repository Database JDBC Drivers

For the DI Server to connect to the DI Repository database of your choice, add the DI Repository's database JDBC driver library to the appropriate place in the web application server on which the DI Server will be deployed. The default web application server for the manual installation process is Tomcat.

  1. Download a JDBC driver JAR from your database vendor or a third-party driver developer.

Due to licensing restrictions, Pentaho does not distribute the necessary JDBC driver JARs. This is why you have to download the file yourself and install it.

  1. Copy the JDBC driver JAR that you just downloaded to the <your tomcat installation directory>/lib directory for the server.
  2. If you are also installing Spoon, copy the JDBC driver JAR into the directory shown in the table.
List of Products and Corresponding Locations for JDBC Drivers
Server or Design Tool Directory
Data Integration (DI) Server pentaho/server/data-integration-server/tomcat/lib (Tomcat installations only.)
Pentaho Data Integration (Spoon) pentaho/design-tools/data-integration/lib

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 BA 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. You also modify the values for the validationQuery parameters in this file if you have chosen to use an BA Repository database other than PostgreSQL.

If you have a different port, password, user, driver class information, or IP address, make sure that you change the password and port number in these examples to match the ones in your configuration environment.

  1. Consult your database documentation to determine the JDBC class name and connection string for your BA Repository database.
  2. Go to the biserver-ee/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 MySQL, Oracle, and PostgreSQL. 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" maxWait="10000" maxIdle="5" maxActive="20" factory="org.apache.commons.dbcp.BasicDataSourceFactory" 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" maxWait="10000" maxIdle="5" maxActive="20" factory="org.apache.commons.dbcp.BasicDataSourceFactory" 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" maxWait="10000" maxIdle="5" maxActive="20" factory="org.apache.commons.dbcp.BasicDataSourceFactory" type="javax.sql.DataSource" auth="Container" name="jdbc/Quartz"/>
    
  4. Make sure that the validationQuery variable for your database is set to this: validationQuery="select 1"
  5. Save the context.xml file, then close it.
  6. To make sure that the changes that you made in the context.xml file take effect when Tomcat is started, navigate to the tomcat/conf/Catalina directory. If the pentaho.xml file is in the present, delete it. It will be generated again when you start the BA Server, but will contain the changes that you just made in the context.xml file.

Next Steps