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 in MMC 3.4.2

  5. Persisting MMC 3.4.2 Data to MS SQL Server

Persisting MMC 3.4.2 Data to MS SQL Server

MMC Version 3.4.2 and Later

This page describes how to set up and configure the Mule Management Console to persist environment and transaction data to Microsoft 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:

  • MMC Version 3.4.2. 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.

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

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.

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.

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.2. 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.2.

  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.

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 will uncompress the file, whose filename extension will change 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 will uncompress and unpack the contents of the file in the current directory.

Configuration for Environment Data

Configuring MMC to store data on a MS-SQL Server databse involves two basic tasks:

  • Modifying the file web.xml to tell MMC to use MS-SQL Server instead of its default database

  • Modifying the file mmc-mssql.properties to set the parameters for connecting to the MS-SQL Server database

Modifying web.xml

  1. In the directory <MMC_HOME>/WEB-INF, locate the file web.xml, then open it for editing.

  2. Locate the spring.profiles.active section, shown below.

    <context-param>
<param-name>spring.profiles.active</param-name>
<param-value>tracking-h2,env-derby</param-value>
</context-param>

  3. Delete the string env-derby, then replace it with env-mssql, as shown below.

    <context-param>
<param-name>spring.profiles.active</param-name>
<param-value>tracking-h2,env-mssql</param-value>
</context-param>

  4. If you are also planning to Configuration for Transaction Data to MS-SQL Server, delete the string tracking-h2 and replace it with tracking-mssql.

The spring.profiles.active section in the web.xml configuration file allows you to define what external databases are used for storing environment and/or tracking data. For a quick instructions for all supported database servers, see Configuring MMC 3.4.2 for External Databases - Quick Reference.

Modifying mmc-mssql.properties

  1. In the directory <MMC_HOME>/WEB-INF/classes/META-INF/databases, locate the file mmc-mssql.properties, then open it for editing.

  2. The table below lists the settings contained in the file. Modify the values as needed. In general, the only values that you should need to modify are env.username, env.password, env.host, env.port and env.dbschema.

    Parameter Description Default

    env.driver

    Driver to use for connecting to the database

    com.microsoft.sqlserver.jdbc.SQLServerDriver

    env.script

    Script to use for creating the tables in the target database

    sqlServer

    env.username

    Database user

    mmc_status

    env.password

    Password for the database user

    mmc123

    env.host

    Hostname or IP address where the database server is listening

    localhost

    env.port

    Port where the database server is listening

    1433

    env.url

    URL for connecting to the database

    jdbc\:sqlserver\://${env.host}\:${env.port};databaseName=${env.dbschema}

    env.dbschema

    Database to connect to

    mmc_persistency_status

  3. Save the file with your modifications, if any.

Removing Local Database Files

For the configuration changes to take effect, before launching MMC you need to delete the local database files that MMC uses by default.

In the root directory of your Web application server, locate the mmc-data directory (for example, /var/lib/tomcat6/mmc-data), then delete the mmc-data directory.

Before you delete mmc-data, make a backup copy of this directory and store it in a safe location. If anything goes wrong with your new database configuration, you can use mmc-data to restore the old database configuration while you troubleshoot your new database config in a test environment.

At this point, MMC, is configured to store environment data on the external MS-SQL Server database that you specified.

Configuration for Transaction Data

Configuring MMC to store Business Events data on an MS-SQL Server database involves two basic tasks:

  • Modifying the file web.xml to tell MMC to use MS-SQL Server instead of its default database

  • Modifying the file tracking-persistence-mssql.properties to set the parameters for connecting to the MS-SQL Server database

Modifying web.xml

  1. In the directory <MMC_HOME>/WEB-INF, locate the file web.xml, then open it for editing.

  2. Locate the spring.profiles.active section, shown below.

    <context-param>
<param-name>spring.profiles.active</param-name>
<param-value>tracking-h2,env-derby</param-value>
</context-param>

  3. Delete the string tracking-h2, then replace it with tracking-mssql, as shown below.

    <context-param>
<param-name>spring.profiles.active</param-name>
<param-value>tracking-mssql,env-derby</param-value>
</context-param>

  4. If you are also planning to Configuration for Environment Data to MS-SQL Server, delete the string env-derby and replace it with env-mssql.

The spring.profiles.active section in the web.xml configuration file allows you to define what external databases are used for storing environment and/or tracking data. For a quick instructions for all supported database servers, see Configuring MMC 3.4.2 for External Databases - Quick Reference.

Modifying tracking-persistence-mssql.properties

  1. In the directory <MMC_HOME>/WEB-INF/classes/META-INF/databases, locate the file tracking-persistence-mssql.properties, then open it for editing.

  2. Modify the included settings as needed, according to the table below. In general, the only values that you should need to modify are mmc.tracking.db.username, mmc.tracking.db.password, mmc.tracking.db.host, mmc.tracking.db.port and mmc.tracking.db.dbname.

    Parameter Description Default

    mmc.tracking.db.events.query.deleteOlderThan

    Do not change this value. This is the cleanup script query for the MS-SQL database.

    DELETE p FROM EVENT_PROPERTIES p where p.id in (select e.id from EVENTS e where e.transaction_id in (select t.id from TRANSACTION_SUMMARIES t where t.timestamp < ?1))

    mmc.tracking.db.platform

    Type of database server to connect to

    org.apache.openjpa.jdbc.sql.SQLServerDictionary

    mmc.tracking.db.driver

    Driver to use for connecting to the database

    com.microsoft.sqlserver.jdbc.SQLServerDriver

    mmc.tracking.db.host

    Hostname or IP address where the database server is listening

    localhost

    mmc.tracking.db.port

    Port where the database server is listening

    1433

    mmc.tracking.db.url

    URL for connecting to the database

    jdbc:sqlserver://${mmc.tracking.db.host}:${mmc.tracking.db.port};databaseName=${mmc.tracking.db.dbname}

    mmc.tracking.db.username

    Database user

    mmc_tracking

    mmc.tracking.db.password

    Password for the database user

    mmc123

    mmc.tracking.db.dbname

    Database to connect to

    persistency

    mmc.max.events.exception.details.length

    Number of characters from a Business Events exception that will be stored in the tracking database. The maximum allowed is 261120.

    8000

  3. Save the file with your modifications, if any.

Removing Local Database Files

For the configuration changes to take effect, before launching MMC you need to delete the local database files that MMC uses by default.

In the root directory of your Web application server, locate the mmc-data directory (for example, /var/lib/tomcat6/mmc-data), then delete the mmc-data directory.

Before you delete mmc-data, make a backup copy of this directory and store it in a safe location. If anything goes wrong with your new database configuration, you can use mmc-data to restore the old database configuration while you troubleshoot your new database config in a test environment.

At this point, MMC, is configured to store tracking data on the external MS-SQL Server database that you specified.

See Also