Use MS SQL Server as your repository database (Archive installation)
This article includes the steps for installing MS SQL as the host database for the Pentaho Server in an archive installation of Pentaho.
Before you begin
Before you prepare your Pentaho Repository, you must prepare either a Windows or Linux environment.
The Pentaho Repository resides on the database that you installed during the Windows or Linux environment preparation step, and consists of the following components:
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.
(Optional) Pentaho Operations Mart
To report on system usage and performance.
Initialize MS SQL Server Pentaho Repository database
To initialize MS SQL Server so that it serves as the Pentaho Repository, you will need to run several SQL scripts to create the Hibernate, Quartz, Jackrabbit (JCR), and Pentaho Operations Mart components.
The sections in this article take you through the steps to initialize the MS SQL Server Pentaho Repository database.
Step 1: Adjust MS SQL Server configuration settings
Procedure
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.
Step 2: Change default passwords
Procedure
Browse to the pentaho/server/pentaho-server/data/sqlserver directory.
Use any text editor to change the passwords in these SQL scripts:
- create_jcr_sqlServer.sql
- create_quartz_sqlServer.sql
- create_repository_sqlServer.sql
- pentaho_mart_sqlServer.sql
Step 3: Run SQL scripts
Run the SQL scripts in the table below.
Run these 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 |
Create Pentaho Operations mart | -i <filepath to DDL>/pentaho_mart_sqlserver.sql |
Step 4: Verify MS SQL Server initialization
After you run the scripts, perform the following steps to verify that databases and user roles have been created:
Procedure
Open MS SQL Server Management Studio.
In the Object Explorer section of the window, make sure that the Quartz, Jackrabbit (JCR), Hibernate, and Pentaho Operations Mart databases are present.
Navigate to
and make sure that the appropriate users have been created.Exit from the MS SQL Server Management Studio tool.
Configure MS SQL Server Pentaho RepositoryDatabase
Now that you have initialized your repository database, you will need to configure Quartz, Hibernate, Jackrabbit, and Pentaho Operations Mart for a MS SQL Server database.
By default, the examples in this section are for a PostgreSQL database that runs on port 1433. The default password is also in these examples.
Step 1: Set up Quartz on MS SQL Server
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.
Open the pentaho/server/pentaho-server/pentaho-solutions/system/quartz/quartz.properties file in any text editor.
Locate the #_replace_jobstore_properties section and set the org.quartz.jobStore.driverDelegateClass as shown:
org.quartz.jobStore.driverDelegateClass = org.quartz.impl.jdbcjobstore.MSSQLDelegate
Locate the # Configure Datasources section and set the org.quartz.dataSource.myDS.jndiURL equal to Quartz, as shown:
org.quartz.dataSource.myDS.jndiURL = Quartz
Save the file and close the text editor.
Step 2: Set Hibernate settings for MS SQL Server
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.
Procedure
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>
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 to host the Pentaho Repository, you need to replace the audit_sql.xml file with one that is configured for MS SQL Server.
Procedure
Locate the pentaho-solutions/system/dialects/sqlserver/audit_sql.xml file.
Copy it into the pentaho-solutions/system directory.
Step 4: Modify Jackrabbit repository information for MS SQL Server
Procedure
Navigate to the pentaho/server/pentaho-server/pentaho-solutions/system/jackrabbit and open the repository.xml file with any text editor.
As shown in the table below, locate and verify or change the code so that the MS SQL Server lines are not commented out, but 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 has been configured, you must configure the web application servers to connect to the Pentaho Repository. In this step, you will make JDBC and JNDI connections to the Hibernate, Jackrabbit, and Quartz components.
The next couple of sections guide you through the process of working with the JDBC drivers and connection information for Tomcat.
Step 1: Download driver and apply to the Pentaho Server
Procedure
Download a JDBC Driver JAR from your database vendor or a third-party driver developer.
Copy the JDBC driver JAR you just downloaded to the pentaho/server/pentaho-server/tomcat/lib folder.
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 XML file
Procedure
Consult your database documentation to determine the JDBC class name and the connection string for your Pentaho Repository database.
Navigate to the server/pentaho-server/tomcat/webapps/pentaho/META-INF directory and open the context.xml file with any text editor.
Add the following code to the file if it does not already exist.
<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.pentaho.di.core.database.util.DecryptingDataSourceFactory" 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.pentaho.di.core.database.util.DecryptingDataSourceFactory" 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.pentaho.di.core.database.util.DecryptingDataSourceFactory" 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.pentaho.di.core.database.util.DecryptingDataSourceFactory" 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.pentaho.di.core.database.util.DecryptingDataSourceFactory" type="javax.sql.DataSource" auth="Container" name="jdbc/PDI_Operations_Mart"/> <Resource validationQuery="select 1" url="jdbc:sqlserver://localhost:1433;DatabaseName=pentaho_dilogs" 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" name="jdbc/live_logging_info"/> <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"/>
Modify the username, password, driver class information, IP address (or domain name), and port numbers to match the correct values for your environment.
Comment out any resource references that refer to other databases.
Verify that the validationQuery parameter for your database is set to: validationQuery="select 1".
Save the context.xml file, then close it.
Start Your Server
Now that you have completed the initial Pentaho Archive installation steps, you are ready to start the Pentaho Server.