Contact Us 1-800-596-4880
  1. Homepage
  2. Mule Management Console (3.4)
  3. Setting Up MMC
  4. Persisting MMC Data On External Databases

  5. Persisting MMC Data to MS SQL Server

Persisting MMC Data to MS SQL Server

MMC Version 3.4.1

This page describes how to set up and configure the Mule Management Console to persist environment and transaction data to Microsoft SQL Server.

Notes:

  • In MMC 3.4.1, persisting Management Console data on MS SQL Server is not fully supported. This page describes a configuration that has been tested and is known to work.

    The instructions on this page are valid only for MMC 3.4.1. MMC 3.4.2 and later versions use a simpler configuration in which data persistence on MS SQL server is fully supported. If persisting data on MS SQL Server, it is recommended to use MMC 3.4.2 or later. For details, see the latest version of this document, Persisting Data on MS SQL Server.

  • This document lays out the instructions for deploying the Management Console on a production-ready environment using Apache Tomcat. All of the instructions should work with other supported application servers as well, but this has not been tested.

Assumptions

This document assumes that you have installed:

  • Mule Enterprise 3.4.1. Download it from the customer portal if you need to upgrade to this version.

  • MMC Version 3.4.1. Download it from the customer portal if you need to upgrade to this version.

  • a Web application server such as Tcat, Tomcat, or JBoss. Please refer to the Hardware and Software Requirements for the latest information about hardware requirements and supported application servers, Java Runtime Environments, and browsers.

For practical purposes, this document mentions Tomcat as the servlet container; however, the instructions contained here apply also to Tcat or JBoss. If using Tcat or JBoss, make sure to unpack the Management Console .war file in the appropriate directory. For additional information, see Installing the Production Version of MMC.

Note: Known Issue with MS SQL and Java 6, Update 29:

There is a known issue with Java 6, Update 29, regarding an SSL configuration which causes connection attempts to the database to hang indefinetely, with no timeout, thus preventing MMC startup. MS SQL cannot be used with this Java version. Please upgrade to a later JVM.

Prerequisites

Prior to configuring the Management Console to persist data to the MS SQL Server, you will need to:

  1. Install a patch for your Management Console installation.

  2. Ensure that the MS SQL Server and logins are properly configured and available.

Patch

In order to use the Management Console with MS SQL Server, you will need to apply a patch to your Management Console .war file. To obtain the patch, open a support case via the MuleSoft Customer Portal.

MS SQL Server Configuration

This article assumes the following MS SQL Server configuration:

For Environment Data

Item Description

Database for environment data

  • Name: MMC_STATUS

  • Owner: MMC_USER

Login for environment data database

  • Name: MMC_USER

  • Default database: MMC_STATUS

  • Password expiration: No

  • Password policy: Not set

  • Password: Any

For Tracking Data

Item Description

Database for tracking data

  • Name: MMC_PERSISTENCY

  • Owner: MMC_TRACKER

Login for tracking data database

  • Name: MMC_TRACKER

  • Default database: MMC_PERSISTENCY

  • Password expiration: No

  • Password policy: Not set

  • Password: Any

Database Connectivity

MS SQL Server must be configured to allow logging with a user name and password instead of only with integrated authentication (i.e. the security options need to specify SQL Server and Windows Authentication Mode).

The database must be accessible from the host where the Management Console resides. When in doubt, confirm that the connection is available using a standard client from the Management Console host.

Tip: Connection testing using the telnet command:

If connection using a standard client fails due to a connectivity issue (not an authentication issue), confirm connectivity using the telnet command, if available on your system.

From the Management Console host, run:

telnet <database_host> <database_port>

If the connection succeeds, you should output similar to the following:

telnet 192.168.56.2 1433
Trying 192.168.56.2...
Connected to 192.168.56.2.
Escape character is '^]'.

The above output indicates that the connection was successful. A "connection refused" error usually indicates that nothing is listening on the specified host and port. Any other output, or lack of output, indicates a connectivity problem, such as a routing issue or firewall blocking requests between the Management Console host and the database host.

The telnet command only tests network connectivity, not authentication to the database.

Setting Up Database Tables for Environment Data

On the first run, JCR automatically creates all the tables needed to store persistent MMC information. However, if you are planning to persist environment data to an external database, you must manually create tables that store Quartz job information, or an error similar to the following will occur:

ERROR: relation “qrtz_locks” does not exist.

To create and insert the tables, follow these steps:

  1. Navigate to <MMC_HOME>/ WEB-INF/classes/quartz.

  2. Locate the SQL script tables_sqlServer.sql.

  3. Execute tables_sqlServer.sql on the target database ( MMC_STATUS ) as the database owner.

At this point, the database is completely defined.

MMC Configuration

Configuring the Management Console to use MS SQL Server requires that you copy files to, and modify files contained in, the Management Console directory structure. This directory structure is contained in the .war file for deploying the Management Console to Tomcat.

It is recommended that you extract the contents of the console .war file to the webapps directory of your Tomcat installation and apply configuration to the extracted contents. The complete sequence of steps is:

  1. Ensure that the Tomcat servlet container is not running.

  2. Extract the Management Console .war file to the webapps directory under the Tomcat root directory. After extraction, the contents of the .war file should reside in <TOMCAT_HOME>/webapps/mmc-3.4.1. For example, if the Tomcat root directory is /var/lib/tomcat6, the Management Console’s root directory should be /var/lib/tomcat6/webapps/mmc-3.4.1.

  3. Perform the modifications described in this page: copying the sqljdbc4.jar file and modifying the appropriate configuration files.

  4. Ensure that there is no directory named mmc-data anywhere in the Tomcat directory tree.

  5. Start Tomcat, starting your customized Management Console for the first time.

The following sections explain how to configure the Management Console for MS SQL Server, working with the files extracted from the original Management Console .war file.

sqljdbc4.jar Driver Library

To use the Management Console with MS SQL Server, you need to add the sqljdbc4.jar library to the Management Console.

Download the library from the Microsoft Download Center. You will have the option to download several files, including an executable file for Windows operating systems and a gzipped tar archive for Unix or Unix-based systems.

If using Windows:

  • find and run the qljdbc_<version>_enu.exe file, then follow the installation wizard

If using a Unix or Unix-based system:

  • uncompress and unpack the file qljdbc_<version>_enu.tar.gz file

  • in the newly-created directory tree, locate the sqljdbc4.jar file

On both types of operating systems:

Copy the file sqljdbc4.jar to the following directory:` <MMC_HOME>/WEB-INF/lib`. For example, /var/lib/tomcat6/webapps/mmc-console-3.4.1/WEB-INF/lib.

Tip: Unpacking gzipped tar files:

To uncompress and unpack the .tar.gz file, open a terminal and use the cd command to navigate to the directory containing the file. Then, run the following commands:

gunzip <file>

This uncompresses the file, whose filename extension then changes from .tar.gz to .tar. Unpack the file with the tar command, as shown below.

tar xvf <file>

For example:

cd Downloads
gunzip sqljdbc_4.0.2206.100_enu.tar.gz
tar xvf sqljdbc_4.0.2206.100_enu.tar

Some versions of the tar utility allow you to uncompress and unpack the file with a single command, as shown below.

tar zxvf <file>

For example:

cd Downloads
tar zxvf sqljdbc_4.0.2206.100_enu.tar.gz

This uncompresses and unpacks the contents of the file in the current directory.

Configuration for Environment Data

To store environment data on MS SQL Server, you need to modify the following files:

All these files reside in <TOMCAT_ROOT>/webapps/mmc-console-3.4.1/WEB-INF/classes/META-INF.

When modifying the files as described here, make sure to replace <MMC_USER_password> with the real password configured in MS SQL Server, as well as any other values you may have modified such as database host and port.

If the MS SQL Server instance to be used is not the default, append instanceName=<SQLServerInstanceName> after the databaseName in the URL specified in the configuration files, as shown in the example below.

...
datasource.url=jdbc\:sqlserver\://172.16.0.12\:1433;databaseName=MMC_STATUS;instanceName=SQLEXPRESS
...

Configuration File: mmc.properties

Open the file for editing, then edit it as shown below.

datasource.driver=com.microsoft.sqlserver.jdbc.SQLServerDriver
datasource.url=jdbc\:sqlserver\://<db_host_ip>\:1433;databaseName=MMC_STATUS
datasource.username=MMC_USER
datasource.password=<MMC_USER_password>
script=sqlServer
mmc.data=./mmc-data
plugin.directory=WEB-INF/plugins
console.inactivityTimeout=60
This file requires that you escape colons with a backslash.

Configuration File: applicationContext.xml

Open the file for editing, then look for the bean called internalDataSource. Edit it as shown below.

...
<bean name="internalDataSource" class="org.springframework.jdbc.datasource.SimpleDriverDataSource">
        <property name="driverClass" value="com.microsoft.sqlserver.jdbc.SQLServerDriver"/>
        <property name="url" value="jdbc:sqlserver://<db_host_ip>:1433;databaseName=MMC_STATUS"/>
       <property name="username" value="MMC_USER"/>
       <property name="password" value="<MMC_USER_password>"/>
</bean>
...

Configuration File: jackrabbit.repo.xml

Open the file for editing, then find the Workspaces configuration. Ensure that the workspace name coincides with what is indicated by the defaultWorkspace attribute. In the the example below, the workspace name is default.

...
<!--        location of workspaces root directory and name of default workspace    -->
    <Workspaces rootPath="${rep.home}/workspaces" defaultWorkspace="default"/>
    <!--        workspace configuration template:
        used to create the initial workspace if there's no workspace yet    -->
    <Workspace name="default">
...

Additionally, edit the sections listed below.

*Notes:

Explanation of snippets displayed below

The snippets listed for file jackrabbit.repo.xml define configuration for FileSystem and PersistenceManager, as described below.

For FileSystem, all references should:

  • use org.apache.jackrabbit.core.fs.db.MSSqlFileSystem

  • do not use the shutdownOnClose parameter

  • use values for driver, url, databaseName, user and password that correctly point to the MMC_STATUS database

  • set the schema as mssql and schemaCheckEnabled to true

For PersistenceManager, all references should:

  • use org.apache.jackrabbit.core.persistence.pool.MSSqlPersistenceManager, instead of org.apache.jackrabbit.core.persistence.pool.DerbyPersistenceManager

  • do not use the shutdownOnClose parameter

  • use values for driver, url, databaseName, user and password that correctly point to the MMC_STATUS database

  • set the schema as mssql, schemaCheckEnabled to true, and externalBLOBs to false

...
<FileSystem class="org.apache.jackrabbit.core.fs.db.MSSqlFileSystem">
             <param name="driver" value="com.microsoft.sqlserver.jdbc.SQLServerDriver"/>
              <param name="url" value="jdbc:sqlserver://<db_host_ip>:1433;databaseName=MMC_STATUS"/>
              <param name="schemaObjectPrefix" value="repo_"/>
              <param name="user" value="MMC_USER"/>
              <param name="password" value="<MMC_USER_password>"/>
              <param name="schema" value="mssql"/>
              <param name="schemaCheckEnabled" value="true"/>
<FileSystem>
...
...
<FileSystem class="org.apache.jackrabbit.core.fs.db.MSSqlFileSystem">
              <param name="driver" value="com.microsoft.sqlserver.jdbc.SQLServerDriver"/>
              <param name="url" value="jdbc:sqlserver://<db_host_ip>:1433;databaseName=MMC_STATUS"/>
              <param name="schemaObjectPrefix" value="workspace_"/>
              <param name="user" value="MMC_USER"/>
              <param name="password" value="<MMC_USER_password>"/>
              <param name="schema" value="mssql"/>
              <param name="schemaCheckEnabled" value="true"/>

</FileSystem>
...
...
<PersistenceManager class="org.apache.jackrabbit.core.persistence.pool.MSSqlPersistenceManager">
             <param name="schemaObjectPrefix" value="Jackrabbit Core_"/>
              <param name="driver" value="com.microsoft.sqlserver.jdbc.SQLServerDriver"/>
              <param name="url" value="jdbc:sqlserver://<db_host_ip>:1433;databaseName=MMC_STATUS"/>
              <param name="externalBLOBs" value="false"/>
              <param name="schemaCheckEnabled" value="true"/>
              <param name="user" value="MMC_USER"/>
              <param name="password" value="<MMC_USER_password>"/>
              <param name="schema" value="mssql"/>
</PersistenceManager>
...
...
<FileSystem class="org.apache.jackrabbit.core.fs.db.MSSqlFileSystem">
               <param name="driver" value="com.microsoft.sqlserver.jdbc.SQLServerDriver"/>
               <param name="url" value="jdbc:sqlserver://<db_host_ip>;databaseName=MMC_STATUS_NEW"/>
               <param name="schemaObjectPrefix" value="search_index_w_"/>
               <param name="user" value="MMC_USER"/>
               <param name="password" value="<MMC_USER_password>"/>
               <param name="schema" value="mssql"/>
               <param name="schemaCheckEnabled" value="true"/>
</FileSystem>
...
...
<FileSystem class="org.apache.jackrabbit.core.fs.db.MSSqlFileSystem">
             <param name="driver" value="com.microsoft.sqlserver.jdbc.SQLServerDriver"/>
             <param name="url" value="jdbc:sqlserver://<db_host_ip>:1433;databaseName=MMC_STATUS"/>
             <param name="schemaObjectPrefix" value="filesystem_version_"/>
             <param name="user" value="MMC_USER"/>
             <param name="password" value="<MMC_USER_password>"/>
             <param name="schema" value="mssql"/>
             <param name="schemaCheckEnabled" value="true"/>
</FileSystem>
...
...
<PersistenceManager class="org.apache.jackrabbit.core.persistence.pool.MSSqlPersistenceManager">
 <param name="schemaObjectPrefix" value="version_"/>

 <param name="driver" value="com.microsoft.sqlserver.jdbc.SQLServerDriver"/>
 <param name="url" value="jdbc:sqlserver://<db_host_ip>:1433;databaseName=MMC_STATUS"/>
 <param name="schemaCheckEnabled" value="true"/>

 <param name="user" value="MMC_USER"/>

 <param name="password" value="<MMC_USER_password>"/>
 <param name="schema" value="mssql"/>
 <param name="externalBLOBs" value="false"/>
</PersistenceManager>
...
...
<FileSystem class="org.apache.jackrabbit.core.fs.db.MSSqlFileSystem">
              <param name="driver" value="com.microsoft.sqlserver.jdbc.SQLServerDriver"/>
              <param name="url" value="jdbc:sqlserver://<db_host_ip>:1433;databaseName=MMC_STATUS_NEW"/>
              <param name="schemaObjectPrefix" value="search_index_"/>
              <param name="user" value="MMC_USER"/>
              <param name="password" value="<MMC_USER_password>"/>
              <param name="schema" value="mssql"/>
              <param name="schemaCheckEnabled" value="true"/>
</FileSystem>
...

Configuration File: scheduler.properties

Open the file for editing, and modify it as shown below.

org.quartz.jobStore.isClustered=false
org.quartz.jobStore.driverDelegateClass=org.quartz.impl.jdbcjobstore.MSSQLDelegate
org.quartz.jobStore.useProperties=true
org.quartz.scheduler.instanceId=AUTO

Configuration For Tracking Data

To store tracking data on MS SQL Server, you will need to modify the following files:

  • persistence.xml

  • applicationContextTracking.xml

Both files reside in <TOMCAT_ROOT>/webapps/mmc-console-3.4.1/WEB-INF/classes/META-INF.

Configuration File: persistence.xml

Open the file for editing, and locate the following parameters:

  • openjpa.jdbc.SynchronizeMappings

  • openjpa.jdbc.DBDictionary

Modify the parameters as shown below.

...
<property name="openjpa.jdbc.SynchronizeMappings" value="buildSchema(SchemaAction='refresh')" />
<property name="openjpa.jdbc.DBDictionary" value="org.apache.openjpa.jdbc.sql.SQLServerDictionary" />
...

Configuration File: applicationContext-tracking.xml

Open the file for editing, and locate the jpaAdapter and dataSource beans. Modify them as shown in the excerpts below.

...
<bean id="jpaAdapter" class="org.springframework.orm.jpa.vendor.OpenJpaVendorAdapter">
        <property name="databasePlatform" value="org.apache.openjpa.jdbc.sql.SQLServerDictionary" />
</bean>
...
...
<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="com.microsoft.sqlserver.jdbc.SQLServerDriver" />
        <property name="url" value="jdbc:sqlserver://<db_host_ip>:1433;databaseName=MMC_PERSISTENCY" />
        <property name="username" value="MMC_TRACKER" />
        <property name="password" value="<MMC_TRACKER_password>" />
...

See Also