Configure Bloomreach Experience Manager for Microsoft SQL Server

Introduction

Goal

Configure Bloomreach Experience Manager's content repository and the Tomcat application container to use a Microsoft SQL Server database.

Background

This page assumes you've read the parent page on configuring databases for Hippo, if you haven't done so already, please start there. That page in turn assumes you're following the Linux installation manual. This page will contain some basic database specific information, and example configurations for use in that scenario.

Configuration changes

Datasource

URL

• jdbc:sqlserver://DBHOST:DBPORT;databaseName=DBNAME or

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

Driver

• com.microsoft.sqlserver.jdbc.SQLServerDriver

Validationquery

• SELECT 1

Repository

To use Microsoft SQL Server the repository configuration needs to be changed to use a couple of database specific changes. Below we provide complete example configuration for the most common uses.

It's needed to set the correct databaseType & classes for the FileSystems, Persistencemanagers, Journal & Datastore. Optionally you can also add the parameter tableSpace to those sections with the tablespace to use if this is different from the user's default.

• 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

Roles and privileges

The following roles and or privileges should give the user enough privileges for Hippo to be able to populate the schema and run:

db_datareader

db_datawriter

db_ddladmin

Example JNDI resource:

Typically, this is defined 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:

<?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 file. Below is an 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>