Database for environment data
Persisting MMC Data to MS SQL Server
Mule Management Console (MMC) was deprecated in December 2015. Its End of Life is July 15, 2019. For more information see the MMC Migrator Tool or contact your Customer Success Manager to determine how you can migrate to Anypoint Runtime Manager. |
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.
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:
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 From the Management Console host, run: telnet ip.of.sql.server 1433 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:
-
Navigate to ` <MMC_HOME>/`WEB-INF/classes/quartz.
-
Locate the SQL script
tables_sqlServer.sql
. -
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:
-
Ensure that the Tomcat servlet container is not running.
-
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
. -
Perform the modifications described in this page: copying the
sqljdbc4.jar
file and modifying the appropriate configuration files. -
Ensure that there is no directory named
mmc-data
anywhere in the Tomcat directory tree. -
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 tar -xvf yourfile.tar This will uncompress the file, whose filename extension will change from For example: Some versions of the 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 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
-
In the directory
<MMC_HOME>/WEB-INF
, locate the fileweb.xml
, then open it for editing. -
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>
-
Delete the string
env-derby
, then replace it withenv-mssql
, as shown below.
<context-param>
<param-name>spring.profiles.active</param-name>
<param-value>tracking-h2,env-mssql</param-value>
</context-param>
-
If you are also planning to Configuration for Transaction Data to MS-SQL Server, delete the string
tracking-h2
and replace it withtracking-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
-
In the directory
<MMC_HOME>/WEB-INF/classes/META-INF/databases
, locate the filemmc-mssql.properties
, then open it for editing. -
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
andenv.dbschema
.
Parameter | Description | Default |
---|---|---|
|
Driver to use for connecting to the database |
|
|
Script to use for creating the tables in the target database |
|
|
Database user |
|
|
Password for the database user |
|
|
Hostname or IP address where the database server is listening |
|
|
Port where the database server is listening |
|
|
URL for connecting to the database |
|
|
Database to connect to |
|
-
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
-
In the directory
<MMC_HOME>/WEB-INF
, locate the fileweb.xml
, then open it for editing. -
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>
-
Delete the string
tracking-h2
, then replace it withtracking-mssql
, as shown below.
<context-param>
<param-name>spring.profiles.active</param-name>
<param-value>tracking-mssql,env-derby</param-value>
</context-param>
-
If you are also planning to Configuration for Environment Data to MS-SQL Server, delete the string
env-derby
and replace it withenv-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
-
In the directory
<MMC_HOME>/WEB-INF/classes/META-INF/databases
, locate the filetracking-persistence-mssql.properties
, then open it for editing. -
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
andmmc.tracking.db.dbname
.
Parameter | Description | Default |
---|---|---|
|
Do not change this value. This is the cleanup script query for the MS-SQL database. |
|
|
Type of database server to connect to |
|
|
Driver to use for connecting to the database |
|
|
Hostname or IP address where the database server is listening |
|
|
Port where the database server is listening |
|
|
URL for connecting to the database |
|
|
Database user |
|
|
Password for the database user |
|
|
Database to connect to |
|
|
Number of characters from a Business Events exception that will be stored in the tracking database. The maximum allowed is 261120. |
|
-
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
-
Read more about MMC setup.
-
Review the Architecture of the Mule Management Console.