Contact Us 1-800-596-4880
  1. Homepage
  2. Mule Management Console (3.7)
  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.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.5.0. 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

Prior to configuring the Management Console to persist data to the MS SQL Server, you need to ensure that the MS SQL Server and logins are properly configured and available.

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.

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:

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

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 Persistent 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.5.0. 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.5.0.

  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 sqljdbc_<version>_enu.exe file, then follow the installation wizard

If using a Unix or Unix-based system:

  • uncompress and unpack the file sqljdbc_<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.5.0/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:

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.

For example:

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

For example:

This will uncompress and unpack the contents of the file in the current directory.

If MMC is deployed to Websphere

If you have deployed MMC to Websphere, you can copy either sqljdbc4.jar as detailed above, or sqlserver.jar, which is the ConnectJDBC type 4 driver provided by IBM. You do not need to copy both files.

For further information, see the IBM documentation for data source minimum required settings for Microsoft SQL Server.

Configuration for Environment Data

Configuring MMC to store data on a 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 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>

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

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

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

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

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

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