Set Up a MS SQL Server Repository

These steps describe how to set up your repository on a MS SQL Server database:

  1. Copy all repository resources to a local folder using WebDAV or the limrepo export command in the Administration Utility (see the Administration section of the Spectrum Spatial Guide for instructions).

    The contents of the installed repository must be exported. This step only needs to be performed once, as the contents of the repository should be the same at this point for all instances of Spectrum™ Technology Platform.

  2. Back up the folder /<spectrum root>/server/modules/spatial/jackrabbit to a local directory or disk.
  3. Stop Spectrum on all nodes.
  4. On all instances of Spectrum™ Technology Platform, add the database JDBC driver to the RepositoryService.war in the WEB-INF/lib directory. The RepositoryService.war is located in /<spectrum root>/server/app/deploy. For SQL Server, this is the sqljdbcx-x.x.jar file.
  5. On all instances of Spectrum™ Technology Platform, edit the /<spectrum root>/server/modules/spatial/jackrabbit/repository.xml file to point the repository to a database and add clustering. There are four separate changes you need to make:
    1. Modify the two FileSystem sections within the Repository and Workspace sections of the file:
      <FileSystem class="org.apache.jackrabbit.core.fs.db.MSSqlFileSystem">
          <param name="driver" value="com.microsoft.sqlserver.jdbc.SQLServerDriver"/>
          <param name="url" value="jdbc:sqlserver://<hostname>:<port>;databaseName=<databasename>;"/>
          <param name="schema" value="mssql"/>
          <param name="user" value="<user>"/>
          <param name="password" value="<pwd>"/>
          <param name="schemaObjectPrefix" value="rep_"/>
      </FileSystem>
      
    2. Modify the Persistence Manager section within the Workspace section:
      <PersistenceManager class="org.apache.jackrabbit.core.persistence.pool.MSSqlPersistenceManager">
          <param name="url" value="jdbc:sqlserver://<hostname>:<port>;databaseName=<databasename>;"/>
      	<param name="schema" value="mssql"/>
      	<param name="user" value="<user>"/>
      	<param name="password" value="<pwd>"/>
      	<param name="schemaObjectPrefix" value="${wsp.name}_"/>
      	<param name="externalBLOBs" value="false"/>		
      	<param name="tableSpace" value=""/>
      </PersistenceManager>
      
    3. Enable clustering at the end of the file, right above the </Repository> tag. Each instance of Spectrum will need to have a distinct id to enable synchronization of clustering to work. The delay defines the time delay for synchronization in milliseconds.
      <Cluster id="node1" syncDelay="2000">
          <Journal class="org.apache.jackrabbit.core.journal.MSSqlDatabaseJournal">
      	<param name="revision" value="${rep.home}/revision.log" />
      	<param name="driver" value="com.microsoft.sqlserver.jdbc.SQLServerDriver"/>
      	<param name="url" value="jdbc:sqlserver://<hostname>:<port>;databaseName=<databasename>;"/>
      	<param name="user" value="<user>"/>
      	<param name="password" value="<pwd>"/>
      	<param name="schema" value="mssql"/>
      	<param name="schemaObjectPrefix" value="rep_"/>
      	<param name="databaseType" value="mssql"/>
          </Journal>
      </Cluster>
      
    4. Comment out the DataStore section:
      <DataStore class="org.apache.jackrabbit.core.data.FileDataStore"/>
  6. On all instances of Spectrum™ Technology Platform, remove the following folders from the /server/modules/spatial/jackrabbit directory: repository, version, workspaces.
  7. If your SQL Server database has previously had repository content added, you must remove tables from your database so a clean repository can be created. If you are starting with a new database, make sure the tables do not exist. The following tables need to be removed from the database:
    default_names_id_seq
    default_binval
    default_bundle
    default_names
    default_refs
    public rep_fsentry
    rep_global_revision
    rep_journal
    rep_local_revisions
    security_binval
    security_bundle
    security_names
    security_refs
  8. Start Spectrum on all nodes.
  9. Restore the resources by copying them from the local folder into the repository using WebDAV.

    Import the content of the repository you previously exported back into the repository. This step only needs to be performed on one of the Spectrum™ Technology Platform instances.