Configure Bloomreach Experience Manager for Microsoft SQL Server

Overview

This guide describes how to configure Bloomreach Experience Manager (XM) to use Microsoft SQL Server as the content repository database and how to set up the Tomcat application container for this configuration.

When to Use

Use this configuration when you want to run Bloomreach Experience Manager with Microsoft SQL Server as the backend database.

Prerequisites

  • Review the general database configuration guide for Bloomreach Experience Manager.
  • Follow the Linux installation manual for initial setup.
  • Ensure you have a running Microsoft SQL Server instance and valid credentials.
  • Confirm that the Microsoft SQL Server JDBC driver is available to your Tomcat installation.

Configuration Details

JDBC Datasource

Configure a JDBC datasource for Microsoft SQL Server in your Tomcat container.

Connection URL

Use one of the following formats for the JDBC connection URL:

  • jdbc:sqlserver://DBHOST:DBPORT;databaseName=DBNAME
  • jdbc:sqlserver://DBHOST\DBINSTANCE;databaseName=DBNAME

JDBC Driver

Specify the following driver class:

  • com.microsoft.sqlserver.jdbc.SQLServerDriver

Validation Query

Set the validation query to:

  • SELECT 1

Repository Configuration

To connect the repository to Microsoft SQL Server, update the repository configuration with the following database-specific settings.

Required Parameters and Classes

Set the following values in your repository configuration:

  • databaseType: mssql
  • FileSystem class: org.apache.jackrabbit.core.fs.db.MSSqlFileSystem
  • PersistenceManager class: org.apache.jackrabbit.core.persistence.pool.MSSqlPersistenceManager
  • Journal class: org.apache.jackrabbit.core.journal.MSSqlDatabaseJournal
  • DataStore class: org.apache.jackrabbit.core.data.db.DbDataStore

You may also specify the tableSpace parameter in these sections if you need to use a tablespace different from the user's default.

Database Roles and Privileges

Assign the following SQL Server roles to the database user to allow schema creation and application operation:

  • db_datareader
  • db_datawriter
  • db_ddladmin

These roles provide the necessary permissions for Bloomreach Experience Manager to create and modify tables as needed.

Example Configurations

Example JNDI Resource Configuration

Define the datasource in Tomcat's conf/context.xml:

<Resource
            name="jdbc/repositoryDS" auth="Container" type="javax.sql.DataSource"
            maxTotal="20" maxIdle="10" initialSize="2" maxWaitMillis="10000"
            testWhileIdle="true" testOnBorrow="false" validationQuery="SELECT 1"
            timeBetweenEvictionRunsMillis="10000"
            minEvictableIdleTimeMillis="60000"
            username="DBUSER" password="DBPASS"
            driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
            url="jdbc:sqlserver://DBHOST:DBPORT;databaseName=DBNAME" />

Example repository.xml

Below is a sample repository.xml configuration for Microsoft SQL Server:

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE Repository
          PUBLIC "-//The Apache Software Foundation//DTD Jackrabbit 2.6//EN"
          "http://jackrabbit.apache.org/dtd/repository-2.6.dtd">

<Repository>

  <DataSources>
    <DataSource name="repositoryDS">
      <param name="driver" value="javax.naming.InitialContext"/>
      <param name="url" value="java:comp/env/jdbc/repositoryDS"/>
      <param name="databaseType" value="mssql"/>
    </DataSource>
  </DataSources>

  <FileSystem class="org.apache.jackrabbit.core.fs.db.MSSqlFileSystem">
    <param name="dataSourceName" value="repositoryDS"/>
    <param name="schemaObjectPrefix" value="repository_"/>
  </FileSystem>

  <Security appName="Jackrabbit">
    <SecurityManager class="org.hippoecm.repository.security.SecurityManager"/>
    <AccessManager class="org.hippoecm.repository.security.HippoAccessManager"/>
    <LoginModule class="org.hippoecm.repository.security.HippoLoginModule"/>
  </Security>

  <Workspaces rootPath="${rep.home}/workspaces" defaultWorkspace="default"/>

  <Workspace name="${wsp.name}">
    <FileSystem class="org.apache.jackrabbit.core.fs.db.MSSqlFileSystem">
      <param name="dataSourceName" value="repositoryDS"/>
      <param name="schemaObjectPrefix" value="${wsp.name}_"/>
    </FileSystem>

    <PersistenceManager class="org.apache.jackrabbit.core.persistence.pool.MSSqlPersistenceManager">
      <param name="dataSourceName" value="repositoryDS"/>
      <param name="schemaObjectPrefix" value="${wsp.name}_"/>
      <param name="externalBLOBs" value="true"/>
      <param name="consistencyCheck" value="false"/>
      <param name="consistencyFix" value="false"/>
      <param name="bundleCacheSize" value="256"/>
    </PersistenceManager>

    <SearchIndex class="org.hippoecm.repository.FacetedNavigationEngineImpl">
      <param name="indexingConfiguration" value="indexing_configuration.xml"/>
      <param name="indexingConfigurationClass" value="org.hippoecm.repository.query.lucene.ServicingIndexingConfigurationImpl"/>
      <param name="path" value="${wsp.home}/index"/>
      <param name="useSimpleFSDirectory" value="true"/>
      <param name="useCompoundFile" value="true"/>
      <param name="minMergeDocs" value="100"/>
      <param name="volatileIdleTime" value="10"/>
      <param name="maxMergeDocs" value="100000"/>
      <param name="mergeFactor" value="5"/>
      <param name="maxFieldLength" value="10000"/>
      <param name="bufferSize" value="1000"/>
      <param name="cacheSize" value="1000"/>
      <param name="onWorkspaceInconsistency" value="log"/>
      <param name="forceConsistencyCheck" value="false"/>
      <param name="enableConsistencyCheck" value="false"/>
      <param name="autoRepair" value="true"/>
      <param name="analyzer" value="org.hippoecm.repository.query.lucene.StandardHippoAnalyzer"/>
      <param name="queryClass" value="org.apache.jackrabbit.core.query.QueryImpl"/>
      <param name="respectDocumentOrder" value="false"/>
      <param name="resultFetchSize" value="1000"/>
      <param name="extractorTimeout" value="100"/>
      <param name="extractorBackLogSize" value="100"/>
      <param name="excerptProviderClass" value="org.apache.jackrabbit.core.query.lucene.DefaultHTMLExcerpt"/>
      <param name="supportSimilarityOnStrings" value="true"/>
      <param name="supportSimilarityOnBinaries" value="false"/>
    </SearchIndex>

    <ISMLocking class="org.apache.jackrabbit.core.state.FineGrainedISMLocking"/>
  </Workspace>

  <Versioning rootPath="${rep.home}/version">
    <FileSystem class="org.apache.jackrabbit.core.fs.db.MSSqlFileSystem">
      <param name="dataSourceName" value="repositoryDS"/>
      <param name="schemaObjectPrefix" value="version_"/>
    </FileSystem>

    <PersistenceManager class="org.apache.jackrabbit.core.persistence.pool.MSSqlPersistenceManager">
      <param name="dataSourceName" value="repositoryDS"/>
      <param name="schemaObjectPrefix" value="version_"/>
      <param name="externalBLOBs" value="true"/>
      <param name="consistencyCheck" value="false"/>
      <param name="consistencyFix" value="false"/>
      <param name="bundleCacheSize" value="64"/>
    </PersistenceManager>

    <ISMLocking class="org.apache.jackrabbit.core.state.FineGrainedISMLocking"/>
  </Versioning>

  <Cluster>
    <Journal class="org.apache.jackrabbit.core.journal.MSSqlDatabaseJournal">
      <param name="dataSourceName" value="repositoryDS"/>
      <param name="databaseType" value="mssql"/>
      <param name="schemaObjectPrefix" value="repository_"/>
      <param name="revision" value="${rep.home}/revision.log"/>
      <!-- clean up journal table -->
      <param name="janitorEnabled" value="true"/>
      <param name="janitorSleep" value="86400"/> <!-- a day in seconds -->
      <param name="janitorFirstRunHourOfDay" value="3"/>
    </Journal>
  </Cluster>

  <DataStore class="org.apache.jackrabbit.core.data.db.DbDataStore">
    <param name="dataSourceName" value="repositoryDS"/>
    <param name="minRecordLength" value="1024"/>
    <param name="maxConnections" value="5"/>
    <param name="copyWhenReading" value="true"/>
  </DataStore>

</Repository>

Example repository.xml for Repository Consistency Checker

The Repository Consistency Checker requires its own repository.xml configuration. Use the following example for Microsoft SQL Server:

<?xml version="1.0"?>

<!DOCTYPE Repository 
          PUBLIC "-//The Apache Software Foundation//DTD Jackrabbit 2.6//EN" 
          "http://jackrabbit.apache.org/dtd/repository-2.6.dtd">

<Repository>

  <DataSources>
    <DataSource name="ds1">
      <param name="driver" value="com.microsoft.sqlserver.jdbc.SQLServerDriver"/>
      <param name="url" value="jdbc:sqlserver://DBHOST:DBPORT;DatabaseName=DBNAME"/>
      <param name="user" value="DBUSER"/>
      <param name="password" value="DBPASS"/>
      <param name="databaseType" value="mssql"/>
      <param name="validationQuery" value="select 1"/>
      <param name="maxPoolSize" value="10"/>
    </DataSource>
  </DataSources>

  <FileSystem class="org.apache.jackrabbit.core.fs.db.MSSqlFileSystem">
    <param name="dataSourceName" value="ds1"/>
    <param name="schemaObjectPrefix" value="repository_" />
  </FileSystem>

  <Security appName="Jackrabbit">
    <SecurityManager class="org.apache.jackrabbit.core.security.simple.SimpleSecurityManager"/>
    <AccessManager class="org.apache.jackrabbit.core.security.simple.SimpleAccessManager"/>
    <LoginModule class="org.apache.jackrabbit.core.security.simple.SimpleLoginModule"/>
  </Security>

  <DataStore class="org.apache.jackrabbit.core.data.db.DbDataStore">
    <param name="dataSourceName" value="ds1"/>
    <param name="minRecordLength" value="1024" />
    <param name="copyWhenReading" value="true" />
    <param name="tablePrefix" value="" />
    <param name="schemaObjectPrefix" value="" />
  </DataStore>

  <Workspaces rootPath="${rep.home}/workspaces" defaultWorkspace="default" maxIdleTime="2"/>

  <Workspace name="${wsp.name}">

    <FileSystem class="org.apache.jackrabbit.core.fs.db.MSSqlFileSystem">
      <param name="dataSourceName" value="ds1"/>
      <param name="schemaObjectPrefix" value="${wsp.name}_" />
    </FileSystem>
    <PersistenceManager class="org.apache.jackrabbit.core.persistence.pool.MSSqlPersistenceManager">
      <param name="dataSourceName" value="ds1"/>
      <param name="schemaObjectPrefix" value="${wsp.name}_" />
    </PersistenceManager>

  </Workspace>

  <Versioning rootPath="${rep.home}/version">
    <FileSystem class="org.apache.jackrabbit.core.fs.db.MSSqlFileSystem">
      <param name="dataSourceName" value="ds1"/>
      <param name="schemaObjectPrefix" value="version_" />
    </FileSystem>
    <PersistenceManager class="org.apache.jackrabbit.core.persistence.pool.MSSqlPersistenceManager">
      <param name="dataSourceName" value="ds1"/>
      <param name="schemaObjectPrefix" value="version_" />
    </PersistenceManager>
    <ISMLocking class="org.apache.jackrabbit.core.state.FineGrainedISMLocking"/>
  </Versioning>

  <Cluster>
    <Journal class="org.apache.jackrabbit.core.journal.MSSqlCleanOnCloseDatabaseJournal">
      <param name="dataSourceName" value="ds1"/>
      <param name="schemaObjectPrefix" value="repository_"/>
    </Journal>
  </Cluster>

</Repository>

Verification

After completing the configuration:

  1. Start Tomcat and the Bloomreach Experience Manager application.
  2. Monitor the logs for errors related to database connectivity or schema creation.
  3. Confirm that the repository initializes and operates as expected.

Troubleshooting

  • If you encounter database connection errors, verify the JDBC URL, username, password, and driver class.
  • Ensure the database user has the required roles (db_datareader, db_datawriter, db_ddladmin).
  • Check that the Microsoft SQL Server JDBC driver is available to the Tomcat container.
  • Review Tomcat and application logs for detailed error messages.
Share Feedback
Page: /deploy/on-premise-deployment/configuration/configuring-brxm-for-microsoft-sql-server
Section: Deploy
Category *