# Use MS SQL Server as your repository database (Manual installation)

{% hint style="info" %}
This page is kept for existing links. Go to [Manual installation process](/install/legacy-redirects/manual-installation-process.md#use-ms-sql-server-as-your-repository-database-manual-installation).
{% endhint %}

Use these steps to set up MS SQL Server as the Pentaho Repository host database.

### Before you begin

#### Prerequisite

Prepare your OS environment first:

* [Prepare your Windows environment for a manual installation](/install/legacy-redirects/manual-installation-process-archived-content/prepare-your-windows-environment-for-a-manual-installation.md)
* [Prepare your Linux environment for a manual installation](/install/legacy-redirects/manual-installation-process-archived-content/prepare-your-linux-environment-for-a-manual-installation.md)

#### Repository components

The Pentaho Repository database includes:

* **Jackrabbit**\
  Stores the solution repository, examples, security data, and report content.
* **Quartz**\
  Stores schedules for reports and jobs.
* **Hibernate**\
  Stores audit logging data.
* **(Optional) Pentaho Operations Mart**\
  Stores usage and performance data.

### Initialize the MS SQL Server Pentaho Repository database

You will run SQL scripts to create the Hibernate, Quartz, Jackrabbit (JCR), and Pentaho Operations Mart schemas.

{% hint style="warning" %}
Use the ASCII character set when you run these scripts.\
Do not use UTF-8. Some scripts can fail due to string length limits.
{% endhint %}

{% hint style="warning" %}
If your usernames, passwords, or ports differ, update the examples first.
{% endhint %}

{% hint style="warning" %}
Pentaho 10.2.0.0 and earlier use Quartz 1.x tables with a `QRTZ5_` prefix.\
Pentaho 10.2.0.1 and later use Quartz 2.x tables with a `QRTZ6_` prefix.

When upgrading to 10.2.0.1+ you must create the `QRTZ6_` tables.\
You can optionally migrate existing schedules with `migrate_old_quartz_data_sqlserver.sql`.

Failure to complete this Quartz upgrade can prevent the server from starting.\
You may see `Missing Quartz library database error` in `catalina.log`.
{% endhint %}

{% stepper %}
{% step %}

### Step 1: Adjust MS SQL Server configuration settings

Configure these settings in Microsoft SQL Server Management Studio (or similar):

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`.
   {% endstep %}

{% step %}

### Step 2: Change default passwords

For production systems, change the default passwords in the SQL scripts.

{% hint style="info" %}
If you are evaluating Pentaho, you can skip this step.
{% endhint %}

1. Browse to `<your-pentaho-directory>/pentaho-server/data/sqlserver`.
2. Edit these scripts for your user, password, database, and required settings:
   * `create_jcr_sqlServer.sql`
   * `create_quartz_sqlServer.sql`
   * `create_repository_sqlServer.sql`
   * `pentaho_mart_sqlserver.sql`
3. Save and close the files.
   {% endstep %}

{% step %}

### Step 3: Run SQL scripts

If you are upgrading from earlier Pentaho versions, initialize a new Quartz database for Quartz 2.x.

{% hint style="warning" %}
Back up your data before proceeding.
{% endhint %}

1. Back up your data. See [Back up your existing Pentaho products and install Pentaho 11.0](/install/legacy-redirects/back-up-your-pentaho-products-and-install-pentaho-9.1-pentaho-upgrade.md).
2. If the Pentaho Server is running, stop it. See [Stop and start the Pentaho Server and repository](/install/legacy-redirects/tasks-to-be-performed-by-an-it-administrator-legacy-redirects/configure-the-pentaho-server-legacy-pages/stop-and-start-the-pentaho-server-and-repository.md).
3. Make sure your MS SQL Server instance is running.
4. Run these scripts in 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`

   This creates:

   * Jackrabbit schema and repository tables
   * Quartz tables (`QRTZ6_` for Quartz 2.x)
   * Hibernate repository schema
   * Pentaho Operations Mart schema
5. (Optional) Migrate schedules from Quartz 1.x:
   1. Open `<your-pentaho-directory>/pentaho-server/data/sqlserver/migrate_old_quartz_data_sqlserver.sql`.
   2. Update the script for your environment, then save it.
   3. Run the migration script.

{% hint style="info" %}
The original `QRTZ5_` tables remain.\
Delete them only after you confirm the migration.
{% endhint %}
{% endstep %}

{% step %}

### Step 4: Verify MS SQL Server initialization

{% hint style="info" %}
Unless you changed it in Step 1, the default password for each username is `password`.
{% endhint %}

1. Open MS SQL Server Management Studio.
2. Log in as `hibuser`.
3. In **Object Explorer**, select `hibernate`. Verify tables exist.
4. If you installed Pentaho Operations Mart, log in as `pentaho_operations_mart`.
5. In **Object Explorer**, select `pentaho_operations_mart`. Verify tables exist.
6. Log in as `jcr_user`.
7. In **Object Explorer**, select `jackrabbit`. Verify tables exist.

   Jackrabbit tables may not appear until you start Pentaho the first time.
8. Log in as `pentaho_user`.
9. In **Object Explorer**, select `quartz`. Verify tables exist.
10. Exit MS SQL Server Management Studio.
    {% endstep %}
    {% endstepper %}

### Configure the MS SQL Server Pentaho Repository database

After initialization, configure Quartz, Hibernate, Jackrabbit, and Pentaho Operations Mart to use MS SQL Server.

{% hint style="warning" %}
These examples assume MS SQL Server on port `1433` and the default passwords.\
If your ports or passwords differ, update the examples.
{% endhint %}

{% stepper %}
{% step %}

### Step 1: Set up Quartz on MS SQL Server

Quartz schedule data is stored in the Quartz JobStore. You configure the JobStore in `quartz.properties`.

1. Open `pentaho/server/pentaho-server/pentaho-solutions/system/scheduler-plugin/quartz/quartz.properties`.
2. In `#_replace_jobstore_properties`, set this value:

   ```properties
   org.quartz.jobStore.driverDelegateClass = org.quartz.impl.jdbcjobstore.MSSQLDelegate
   ```
3. Save the file.
   {% endstep %}

{% step %}

### Step 2: Set Hibernate settings for MS SQL Server

Hibernate configuration includes the driver, connection, dialect, and connection handling. The Hibernate database also stores audit logs for Operations Mart.

All files below are in `pentaho/server/pentaho-server/pentaho-solutions/system/hibernate`.

1. Open `hibernate-settings.xml`.
2. Update the `<config-file>` value:

   From:

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

   To:

   ```xml
   <config-file>system/hibernate/sqlserver.hibernate.cfg.xml</config-file>
   ```
3. Save the file.
   {% endstep %}

{% step %}

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

Replace the audit SQL file to match MS SQL Server.

1. Locate `pentaho-solutions/system/dialects/sqlserver/audit_sql.xml`.
2. Copy it to `pentaho-solutions/system`.
   {% endstep %}

{% step %}

### Step 4: Modify Jackrabbit repository information for MS SQL Server

Update `repository.xml` to use MS SQL Server for the Jackrabbit repository.

1. Open `pentaho/server/pentaho-server/pentaho-solutions/system/jackrabbit/repository.xml`.
2. Uncomment the MS SQL Server sections.
3. Comment out the MySQL, Oracle, and PostgreSQL sections.

Use these MS SQL Server sections as your reference:

#### Repository

```xml
<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

```xml
<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

```xml
<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 (workspace)

```xml
<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

```xml
<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 (versioning)

```xml
<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

```xml
<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>
```

{% endstep %}
{% endstepper %}

### Perform Tomcat-specific connection tasks

After repository configuration, configure your web application server to connect to the repository. This includes JDBC and JNDI connections for Hibernate, Jackrabbit, and Quartz.

{% hint style="info" %}
Pentaho Server is configured for Tomcat by default.\
Update `context.xml` only if you changed default ports, hosts, or passwords.
{% endhint %}

{% stepper %}
{% step %}

### Step 1: Download the JDBC driver and apply it to Pentaho Server

You must provide a JDBC driver for MS SQL Server. Pentaho cannot redistribute all third-party drivers.

1. Download a JDBC driver `.jar` from Microsoft or a third-party provider.
2. If needed, confirm the driver is supported. See [JDBC drivers reference](/install/jdbc-drivers-reference.md).
3. Copy the JDBC driver `.jar` to `pentaho/server/pentaho-server/tomcat/lib`.
4. (Optional) Copy `hsqldb-2.3.2.jar` to `pentaho/server/pentaho-server/tomcat/lib` to keep the sample data.
   {% endstep %}

{% step %}

### Step 2: Modify JDBC connection information in the Tomcat `context.xml` file

Connection details for your repository database are stored in `context.xml`.

{% hint style="warning" %}
If your host, port, username, password, or driver class differs, update the values below.\
Also comment out resources for databases you do not use.
{% endhint %}

1. Find the JDBC driver class name and connection string in your database documentation.
2. Open `server/pentaho-server/tomcat/webapps/pentaho/META-INF/context.xml`.
3. Add or update the resources if they do not already exist.

   ```xml
   <Resource name="jdbc/Hibernate" 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="hibuser" password="password"
           driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
           url="jdbc:sqlserver://localhost:1433;databaseName=hibernate"
           validationQuery="select 1" jdbcInterceptors="ConnectionState" defaultAutoCommit="true"/>
   <Resource name="jdbc/Audit" 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="hibuser" password="password"
           driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
           url="jdbc:sqlserver://localhost:1433;databaseName=hibernate"
           validationQuery="select 1" />
   <Resource name="jdbc/Quartz" 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" testOnBorrow="true"
           driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
           url="jdbc:sqlserver://localhost:1433;databaseName=quartz"
           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" minIdle="0" maxIdle="5" initialSize="0"
           maxWait="10000" username="hibuser" password="password"
           driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
           url="jdbc:sqlserver://localhost:1433;databaseName=hibernate"
           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" minIdle="0" maxIdle="5" initialSize="0"
           maxWait="10000" username="hibuser" password="password"
           driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
           url="jdbc:sqlserver://localhost:1433;databaseName=pentaho_operations_mart"
           validationQuery="select 1"/>
   <Resource name="jdbc/live_logging_info" 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="hibuser" password="password"
           driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
           url="jdbc:sqlserver://localhost:1433;databaseName=hibernate"
           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"
           validationQuery="select 1" jdbcInterceptors="ConnectionState" defaultAutoCommit="true"/>
   ```
4. Verify `validationQuery="select 1"` is correct for your database.
5. Save the file.
   {% endstep %}
   {% endstepper %}

### Next steps

Continue with [Configure and start the Pentaho Server after manual installation](/install/legacy-redirects/manual-installation-process-archived-content/configure-and-start-the-pentaho-server-after-manual-installation.md).


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.pentaho.com/install/legacy-redirects/manual-installation-process-archived-content/use-ms-sql-server-as-your-repository-database-manual-installation.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.