BPM Admin Guide

Using MS SQL Server as Pentaho Server Database

This section describes how to change the backend database of Pentaho Server from the default PostgreSQL installation to MS SQL Server.

This is not required when using MS SQL Server as data source for the ETL.

Prepare the environment

Note: More information is available here.
  1. Extract server files (file name example, pentaho-server-ee-9.3.0.0-428-dist.zip).
  2. Run installer.bat as Administrator.
  3. Accept the license term agreement, then click Next.
  4. Change the installation path to <Installation_directory>/Pentaho/server.
  5. A warning message that the directory already exists appears. Click Yes. Any existing files in the directory will be retained.

Configuring MS SQL Pentaho Repository Database

Note: More information is available here.
  1. Adjust MS SQL Server configuration settings:
    1. Use mixed authentication - on MS SQL Server Management Studio object explorer, right-click the server then go to Properties » Security, and select SQL Server and Windows Authentication mode.
    2. Enable TCP/IP for MS SQL Server - on SQL Server Configuration Manager » SQL Server Network Configuration » Protocols for MSSQLSERVER, right-click TCP/IP and click Enable.
    3. Configure MS SQL Server to listen on an external IP and not localhost - on SQL Server Configuration Manager » SQL Server Network Configuration » Protocols for MSSQLSERVER, right-click TCP/IP » Properties » IP Addresses and update the IP Addresses.
  2. Navigate to <INSTALL_DIR>/Pentaho/server/pentaho-server/data/sqlserver to find the following SQL scripts:
    • create_jcr_sqlServer.sql
    • create_repository_ sqlServer.sql
    • create_quartz_sqlServer.sql
  3. To change the passwords, go to the <INSTALL_DIR>/Pentaho/server/pentaho-server/data/sqlserver directory and use any text editor to change the passwords in the SQL scripts from step 2.
  4. Run SQL Scripts using Microsoft SQL Server Management Studio in the following order:
    1. File » Open » File….
    2. Select all the scripts.
    3. Execute each script.

      Alternatively, you can run the scripts using sqlcmd (Requires admin privileges):

      • -i <filepath to DDL>/create_quartz_sqlServer.sql
      • -i <filepath to DDL>/create_repository_sqlServer.sql
      • -i <filepat h to DDL>/create_jcr_sqlServer.sql
  5. Verify that databases and user roles 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), Hibernate, and Pentaho Operations Mart databases are present.
    3. Navigate to Security » Logins and make sure that the appropriate users have been created.
    4. Exit the MS SQL Server Management Studio tool.

Set Up Quartz

  1. Open the <INSTALL_DIR>/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 here.

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

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

    org.quartz.dataSource.myDS.jndiURL = Quartz

  4. Save the file and close the text editor.

Set Hibernate Settings

  1. Open <INSTALL_DIR>/Pentaho/server/pentaho-server/pentaho-solutions/system/hibernate/hibernate-settings.xml file in any text editor.
  2. Change the <config-file>:

    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 and close the text editor.

Set Audit Log file with SQL Server version

  1. Copy the file <INSTALL_DIR>/pentaho/server/pentaho-server/pentaho-solutions/system/dialects/sqlserver/audit_sql.xml.
  2. Paste and replace the existing audit_sql.xml in <INSTALL_DIR>/pentaho/server/pentaho-server/pentaho-solutions/system.

Set Jackrabbit Repository Information

  1. 1. Open <INSTALL_DIR>/pentaho/server/pentaho-server/pentaho-solutions/system/jackrabbit/repository.xml file in any text editor
  2. Comment out (<!-code -->) the code section for PostgreSQL and uncomment the MS SQL Server counterpart of the following and then update the parameters:
    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://<Server>:<Port>;DatabaseName=jackrabbit"/>
    <param name = "user" value="jcr_user"/>
    <param name = "password" value="password"/>
    <param name = "schemaObjectPrefix" value="fs_repos_"/>
    </FileSystem>
    DataStore

    <DataStore class = "org.apache.jackrabbit.core.data.db.DbDataStore">
    <param name = "url" value="jdbc:sqlserver://<Server>:<Port>;DatabaseName=jackrabbit"/>
    <param name = "driver" value="com.microsoft.sqlserver.jdbc.SQLServerDriver"/>
    <param name = "user" value="jcr_user"/>
    <param name = "password" value="password"/>
    <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="com.microsoft.sqlserver.jdbc.SQLServerDriver"/>
    <param name = "url" value="jdbc:sqlserver://<Server>:<Port>;DatabaseName=jackrabbit"/>
    <param name = "user" value="jcr_user"/>
    <param name = "password" value="password"/>
    <param name = "schema" value="mssql"/>
    <param name = "schemaObjectPrefix" value="fs_ws_"/> </FileSystem>

    Persistence Manager (Part 1)

    <PersistenceManager class = "org.apache.jackrabbit.core.persistence.bundle.MSSqlPersistenceManager">
    <param name = "url" value="jdbc:sqlserver://<Server>:<Port>;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="${wsp.name}_pm_ws_"/>
    </PersistenceManager>

    Versioning

    <FileSystem class = "org.apache.jackrabbit.core.fs.db.MSSqlFileSystem">
    <param name = "url" value="jdbc:sqlserver://<Server>:<Port>;DatabaseName=jackrabbit"/>
    <param name = "user" value="jcr_user"/>
    <param name = "password" value="password"/>
    <param name = "schema" value="mssql"/>
    <param name = "schemaObjectPrefix" value="fs_ver_"/>
    </FileSystem>

    Persistence Manager (Part 2)

    <PersistenceManager class = "org.apache.jackrabbit.core.persistence.bundle.MSSqlPersistenceManager">
    <param name = "url" value="jdbc:sqlserver://<Server>:<Port>;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="pm_ver_"/> </PersistenceManager>

    Note: The password for jcr_user is the one defined in create_jcr_ora.sql.
  3. Comment out the DatabaseJournal code section:

    <!--
    <Cluster id="node1">
    <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></Cluster>
    -->
  4. Apply JDBC Drivers to the Pentaho Server.
    1. Download the Microsoft JDBC Driver for SQL Server.
    2. Copy the jar file to <INSTALL_DIR>/pentaho/server/pentaho-server/tomcat/lib.

Update Tomcat Context XML File with JDBC Connection

  1. Open <INSTALL_DIR>/pentaho/server/pentaho-server/tomcat/webapps/pentaho/META-INF/context.xml in any text editor.
  2. Comment out the existing <Resources> in the file and add the following lines:

    <Resource validationQuery="select 1"
    url = " jdbc:sqlserver:// <Server>:<Port>;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:// <Server>:<Port>;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:// <Server>:<Port>;DatabaseName=quartz"
    driverClassName = "com.microsoft.sqlserver.jdbc.SQLServerDriver"
    password = "password"
    username = "pentaho_user"
    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:// <Server>:<Port>;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:// <Server>:<Port>;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 " />
    Note: jdbc/pentaho_operations_mart and jdbc/PDI_Operations_Mart resources needs to be included even if it the pentaho_mart_oracle.sql was not run.

Clean Pentaho Server Repository Cache

The evaluation installation created samples that are still referencing the previous PostgreSQL database. This need to be removed for Pentaho Repository to recreate the cache with the right database references. Follow these simple instructions:

  1. Stop Pentaho Server.
    1. Open a command prompt.
    2. Run stop-pentaho.bat in <INSTALL_DIR>/server/pentaho-server/.
  2. Navigate to <INSTALL_DIR>/ server/pentaho-server/pentaho-solutions/system/jackrabbit/.
  3. Rename or delete the repository folder.

Start Pentaho Server

  1. Open a command prompt.
  2. Run start-pentaho.bat as Administrator in <INSTALL_DIR>/ server/pentaho-server/.
  3. Verify that the server is running by opening Pentaho User Console. In any browser open http: <hostname>:<port>/pentaho.