USER MANUALS

Setting-Up an External Database

Note

Only global administrators and Solution Manager administrators can perform this task.

The Solution Manager server and the License Manager server store their information on a database:

  1. Solution Manager: environments, clusters and servers.

  2. Promotions: load balancing variables, revisions and deployments.

  3. License: global license and license usages.

They all use the same database to store this information. By default, it is a local database included with the installation (Apache Derby). You can configure these components to store this information on an external database. They all have to use the same database.

Note

When you change the database on which this information is stored, you will no longer have access to the existing promotions because they will remain on the local database, which will not be accessed anymore. The license will be automatically transferred to the new database. The information about environments, clusters and servers will be transferred during the process explained on this page.

Follow these steps:

  1. In the external database, create a catalog or a schema for the metadata of Solution Manager.

    Although you can use an existing schema, we recommend creating one to keep the tables separate from the tables of other applications; do not share this schema with other modules of the Denodo Platform either.

    Consider enabling the high availability features of the external database to meet higher uptime requirements.

  2. If you already defined environments/clusters/servers in the Tree Area, export them to a file. To do this, right-click on the root of this tree (on Solution Manager) and click Export.

    The information about revisions and deployments cannot be exported.

  3. Copy the jar file(s) of the JDBC driver of the database to the folder <SOLUTION_MANAGER_HOME>/lib/solution-manager-extensions.

  4. Restart these components:

    • License Manager server

    • Solution Manager server

    • Solution Manager Administration Tool

    You do not need to restart these components if the jar files of the driver were already in that folder.

  5. Click the menu Configuration > Solution Manager database. In the new tab, enter the following parameters:

    • Database: select one of the supported databases:

      • Apache Derby 10.15.2.0 or higher.

      • Microsoft SQL Server 2014, using the Microsoft JDBC driver (the jTDS driver is unsupported). Select this to use Microsoft SQL Server or for the Azure SQL Server-compatible edition.

      • Oracle 12c. To use this, copy the jar files of the directory <SOLUTION_MANAGER_HOME>/lib/extensions/jdbc-drivers/oracle-12c to <SOLUTION_MANAGER_HOME>/lib/solution-manager-extensions. Solution Manager requires the Oracle 12c drivers to connect to Oracle 12c or higher.

      • Oracle 19c or higher.

      • MySQL 5.7 or higher. Select this to use MySQL or for the MySQL-compatible edition of Amazon Aurora.

      • PostgreSQL 11 or higher. Select this to use PostgreSQL or the PostgreSQL-compatible edition of Amazon Aurora.

    • Driver Class: class name of the JDBC driver. E.g. for Oracle oracle.jdbc.OracleDriver.

    • URL: JDBC URL to connect to your database server.

    • User and Password: credentials to connect to the database. This user account needs privileges to create tables and insert/update/delete rows in these tables.

      Later, you can change this password modifying the configuration file of Solution Manager (see below).

Database configuration panel

Database configuration panel

Note

  • If you select the database Derby Embedded, the fields Driver Class, URI, Username and Password are not editable. It represents a connection to the Derby database included with the Solution Manager.

  • If you select MySQL, add these parameters to the URL: useUnicode=true&characterEncoding=UTF-8. For example:

    jdbc:mysql://mysql.acme.com:3306/denodo_solution_manager?useUnicode=true&characterEncoding=UTF-8

    They are necessary so any non-ASCII character can be stored in the database correctly.

    In addition, the schema of MySQL that the Solution Manager will use has to have these options: Default Charset = utf8 and Collation = utf8_unicode_ci.

    To set these options, connect to this MySQL and execute the statement below:

    ALTER DATABASE <database name>
CHARACTER SET utf8 COLLATE utf8_general_ci;

  1. Click save-btn. The Solution Manager will check that it can reach this database. You will be prompted to create the necessary tables if they do not exist. If you click Yes, they will be created automatically. Alternatively, you can create them manually using the scripts of the directory <SOLUTION_MANAGER_HOME>/scripts/solution-manager/sql.

    Note

    If the target database is Oracle and you are going to use an interactive tool such as SQL*Plus or Enterprise Manager to create these tables, you have to modify the script solutionmanager.oracle.sql: add a new line with the character / right after each statement CREATE OR REPLACE TRIGGER (there are several of these statements). For example:

    CREATE OR REPLACE TRIGGER acl_class_id_trigger
BEFORE INSERT ON acl_class
FOR EACH ROW
BEGIN
   SELECT acl_class_sequence.nextval INTO :new.id FROM dual;
END;
/

  2. Stop the License Manager server, Solution Manager server and Solution Manager Administration Tool.

  3. Start the License Manager and wait two minutes.

    The License Manager will automatically store the license information in the external database.

  4. Start the Solution Manager server and the Solution Manager Administration Tool.

    Important

    Start the License Manager before the other components of this installation because first, the License Manager has to store the license on the external database. If you do not do it in this order, the Solution Manager will not start because it will not find a valid license.

  5. Log in to the Solution Manager. If you already had defined environments, clusters and servers, now they are not there. The reason is that only the license is automatically copied to the new database.

    To restore the environments, clusters and servers, right-click on the root of tree and click Import. Select the file you exported on step #1.

Changing the Password of the External Database

If you already configured Solution Manager to store its configuration on an external database, and you want to change the password that Solution Manager uses to connect to this database, you have two options:

  1. Modify a configuration file of Solution Manager. This section explains how to do this.

  2. Or, use the web interface (the one explained above).

To do it modifying a configuration file, follow these steps:

  1. Stop the License Manager server, Solution Manager server and Solution Manager Administration Tool.

  2. Edit the file <SOLUTION_MANAGER_HOME>/conf/license-manager/LMConfigurationParameters.properties and change these properties:

Property Name

Meaning

com.denodo.solutionmanager.storage.GenericStorageManager.password

Password of the external database.

We recommend you to store the password encrypted. To encrypt the password, execute the command <SOLUTION_MANAGER_HOME>/bin/encrypt_password "<new password>" and copy the value and put it in the file.

The output of this script is the encrypted password.

com.denodo.solutionmanager.storage.GenericStorageManager.password.encrypted

Set to ENCRYPTED if you provide the password encrypted. If you store the password in clear, leave it empty.
Example of properties when providing the password encrypted
com.denodo.solutionmanager.storage.GenericStorageManager.password=FCAOAB8jiithfHYd5Oq8Q==
com.denodo.solutionmanager.storage.GenericStorageManager.password.encrypted=ENCRYPTED
Example of properties when providing the password encrypted in plain text.
com.denodo.solutionmanager.storage.GenericStorageManager.password=denodo
com.denodo.solutionmanager.storage.GenericStorageManager.password.encrypted=

  1. Start the License Manager server, Solution Manager server and Solution Manager Administration Tool.

Add feedback