Database configuration

From NComputing Knowledge Base
Jump to: navigation, search

NoTouch Center stores its data into a SQL database. The primary database is MySQL, but we also support Oracle (including the latest 12c) and Microsoft SQL Server. The NComputing Virtual Appliance comes with MySQL included, so actually the fact that a database is used is transparent to you and you don't have to worry about that.

If, and only if, you want to use an external database, be it another MySQL that you have installed somewhere, or an existing instance of Oracle or Microsoft SQL server this information is for you. Please also note that we can not support your own external database - if for instance you chose to use Oracle, you must have all the required knowledge and time to deal with Oracle!

Configuration properties

A NoTouch Center instance needs to know where its database is and how to connect. There is only one database configurable.

NoTouch Center is configured via Configuration properties. These are the relevant properties in the tcmgr.properties file:

  • lmc.dbhost= DNS host name or IP address of the database server. Default: localhost
  • lmc.dbname= name of the database that will be used on the database server. Default: easyadmin
  • lmc.dbuser= username that is used to connect to the database. Default: easyadmin
  • lmc.dbpass= password of the database user. Default: easyadmin
  • lmc.dbtype= a string value denoting the type of database used. Default: mysql
    • mysql for MySQL
    • jtds for Microsoft SQL Server (preferred)
    • sqljdbc is an alternative driver for Microsoft SQL Server, relating to Microsoft's SQL-JDBC driver (NoTouch 4.2.14 and later)
    • oracle for Oracle
    • derby for Apache Derby (unsupported)
    • hsqldb for HSQL (unsupported)
    • hsqldbmem for HSQL in-memory (unsupported - "in-memory" means that all contents are gone after reboot)
  • lmc.dbport= an optional TCP port number if it is non-standard for the given database type. Default: empty
  • lmc.dbPool= refers to the #Connection Pooling mechanism, can be c3p0, dbcp, dbcp2_basic, dbcp2_pooling. Default: empty (means C3P0 will be used)
  • lmc.dbUrlOptions= allows to add JDBC driver specific URL options. Default: empty

See tcmgr.properties for information on how to access the properties.file and edit NoTouch Center configuration properties.

External database operation

Database preparation

NoTouch Center needs its own "database" (sometimes referred to as tablespace or schema), and a user account name and password that has all privileges in this database, including modifying table definitions. Some databases call this "DDL administration".

By default, we suggest a database name of "easyadmin", a username of "easyadmin", and a password of "easyadmin".

MySQL

In MySQL, the commands to create and prepare such a database are:

  create database easyadmin;
  grant all on easyadmin.* to 'easyadmin'@'localhost' identified by 'easyadmin';

Microsoft SQL Server

In MS SQL Server Studio, first create a user named "easyadmin". Then right-click on the "Databases" entry in the tree view, and select "Create Database". Assign a name for your database (we suggest "easyadmin") and make sure the user "easyadmin" is chosen as the owner of the database.

Oracle

Please consult the Oracle documentation on creating users and tablespaces. If using Oracle XE, the database name will be hardcoded to "XE".

Initial set-up

When NoTouch connects first time to an empty database, it will create all database tables and fill in essential values needed for operation. Pay close attention to the log output (center.log or Console if starting in foreground). NoTouch Center will write clear and descriptive error messages to the log if it can not connect, there are configuration or database errors.

Disabling the built-in MySQL

The NComputing Virtual Appliance comes with MySQL. If you use an external database, you can deactivate MySQL.

NComputing Virtual Appliance

  1. Login to the Virtual Appliance via Secure Shell and gain root privileges as described
  2. Stop the MySQL services by executing these command:
    • stop mysql
  3. Make sure MySQL stays deactivated:
    • echo "manual" >> /etc/init/mysql.override


Database migration

Sometimes people have to move their database to a new server, for whatever reasons. In that case you need to let NoTouch Center know about the change so that it can connect to and use its database.

If you want to migrate your complete NoTouch Center installation (i.e. not just the database), there are easy-to-use migration paths:

Remember, this sections deals with migrating the database only. Please always use your database servers tool to import and export. So for example if you move the NoTouch Center database from an older MS SQL server to a newer version on a different server, properly export it on the old one using the Microsoft SQL Server tools and and import it on the new one. Make sure user account, password and privileges are intact on the new system as well.

NoTouch Center does not officially support cross-migration from one of its supported databases to another one, although the unsupported NoTouch Center XML tools work well in many cases. There is no guarantee that this will work, and even if something seems to work fine, problems may come up later.

To migrate your database, proceed as follows:

  1. Stop NoTouch Center
  2. Migrate your database to the new server, taking into account all the information on this page
  3. Edit the database configuration values of tcmgr.properties (see above)
  4. Start NoTouch Center

MySQL backup and restore

For any MySQL installation, be it those created by the NComputing Virtual Appliance or any custom/third-party installation you can easily get database contents "in" and "out".

To backup a MySQL database to a file, call:

  mysqldump -u easyadmin -p easyadmin >mydatabase.sql

To read a MySQL database file and store contents to the database, do

  mysql -u easyadmin -p easyadmin <mydatabase.sql

The latter command implies that the database has been prepared correctly and the user account was created and permissions granted (see above).

Note: Your MySQL binaries may not reside in the default PATH, i.e. your command shell may not find it. If you use the MySQL that gets installed by the NoTouch Center Windows installer, you must manually use the full path to the mysql\bin\ subdirectory of the installation folder. That means the complete path of the mysqldump.exe and mysql.exe tools is most likely something like

  C:\Program Files\NComputing\NoTouch Center\mysql\bin\

NoTouch Center Database XML files

The NoTouch Center database XML file format is a proprietary XML-based file format that contains all the tables, rows and columns of a NoTouch Center database, all in XML, with the goal of exporting from and importing to different database types. It is not possible to guarantee error-free restore of such a file, thus they are not a replacement for backups of the underlying database!

The command line tools "dbimport" and "dbexport" are located in the installation directory of NoTouch Center.

  • On Windows systems this is
  C:\Program Files\NComputing\NoTouch Center\lmc
  /opt/center

Exporting the database to a NoTouch Center XML file

The dbexport command is available as dbexport.cmd for Windows systems and dbexport.sh for Linux systems. Simply calling it will produce an XML file - check the console output for the file name, or supply your own desired file name using the -f parameter. By default, the tool will read out the database information from the etc/tcmgr.properties file. You can specify another properties file with the -c switch or add database parameters using the command line. We suggest to run the tool only after stopping NoTouch Center.

Options:
      -x | --help       ... print this help
      -f | --file       ... data file (zip or xml) for export (MANDATORY)
      -c | --config     ... configuration properties file
      -h | --dbhost     ... database host (overrides config file setting)
      -u | --dbuser     ... database user (overrides config file setting)
      -p | --dbpassword ... database password (overrides config file setting)
      -t | --dbtype     ... database type such as mysql, oracle, jtds (overrides config file setting)
      -d | --dbname     ... database name (overrides config file setting)
      -e | --dbport     ... database TCP/IP port (overrides config file setting)

Importing the database from a NoTouch Center XML file

The dbimport command is available as dbimport.cmd for Windows systems and dbimport.sh for Linux systems. The -f parameter followed by the file to import is mandatory. By default, the tool will read out the database information from the etc/tcmgr.properties file. You can specify another properties file with the -c switch or add database parameters using the command line.

dbimport works best if used on a new database without anything in it. Refer to the instructions about preparing a database. If NoTouch Center has only been run once, it will have already created its table structure and database constraints. This implies that NoTouch Center must not run during dbimport operation!

      -x | --help       ... print this help
      -f | --file       ... data file (zip or xml) for import (MANDATORY)
      -c | --config     ... configuration properties file
      -o | --onlyimportant  ... only mandatory tables, without peripheral inventory and log
           --nodelete   ... do not delete database contents before importing
           --noinstall  ... do not install tables
           --nocleanup  ... do not clean up ie delete records with missing foreign keys
      -r | --resetpw    ... reset admin password to default
      -h | --dbhost     ... database host (overrides config file setting)
      -u | --dbuser     ... database user (overrides config file setting)
      -p | --dbpassword ... database password (overrides config file setting)
      -t | --dbtype     ... database type such as mysql, oracle, jtds (overrides config file setting)
      -d | --dbname     ... database name (overrides config file setting)
      -e | --dbport     ... database TCP/IP port (overrides config file setting)
      -m | --maxrows X  ... import at most X rows per table (debugging option)
           --droponly   ... delete everything in database and exit (no importing done) DANGEROUS
           --noautoinstall  ... do not autoinstall (i.e. the new parameter definitions)

NoTouch Center Database XML file format

No complete documentation of the XML file format exists. The following example is given so that system administrators can verify the file type.

<?xml version="1.0" encoding="utf-8"?>
<LMCDATA ExportFormat="2">
        <METADATA>
                <About>This is an export of a NComputing database. The content of this file is proprietary and confidential.</About>
                <CreationDate TimeSource="Instance-Server">2015-06-15 01:40:56 +0000</CreationDate>
                <CreatorTablePrefix></CreatorTablePrefix>
                <ProductMetaData>
                        <Product>NoTouch Center</Product>
                        <ProductShortName>NTC</ProductShortName>
                        <Release>4.1.389</Release>
                        <Vendor>NComputing</Vendor>
                     [...]
                </ProductMetaData>
                <DatabaseMetaData>
                        <DatabaseProductName>MySQL</DatabaseProductName>
                        <DatabaseProductVersion>5.5.43-0ubuntu0.14.04.1</DatabaseProductVersion>
                        <DatabaseMajorVersion>5</DatabaseMajorVersion>
                        <DatabaseMinorVersion>5</DatabaseMinorVersion>
                        <URL>jdbc:mysql://localhost:3306/easyadmin</URL>
                     [...]
                </DatabaseMetaData>
               [...]
        </METADATA>
        <TABLES>
             [...]
        </TABLES>
            [...]
</LMCDATA>

Connection Pooling

NoTouch Center does not directly deal with the database, it uses an intermediary module providing "connection pooling". The reason is that opening connections to a database is a relatively expensive operation. Thus, a pool of existing connections is formed, and whenever a transaction occurs, it is done using one of the existing connections. NoTouch supports different connection pooling mechanisms, configured by the lmc.dbPool property. See above for more information on these properties: #Configuration properties. The values in parentheses are the lmc.dbPool configuration settings:

  • C3P0 (c3p0). C3P0[1] is the default connection pooling mechanism that NoTouch Center uses.
  • DBCP (dbcp). Apache DBCP 1 [2] is deprecated and does not really work well. We do not recommend this.
  • DBCP2 Basic (dbcp2_basic). Apache DBCP2 [3] is a rewrite of DBCP and much different to DBCP.
  • DBCP2 Pooling (dbcp2_pooling). This is the real pooling mechanism of Apache DBCP2. Use this if you experience problems that you believe originate from C3P0.

C3P0 configuration

See tcmgr.properties for information on how to access the properties.file and edit NoTouch Center configuration properties. The C3P0 properties begin with the lmc.c3p0 prefix - you can supply any C3P0 property, not just the ones mentioned here!

Connection testing

Connection testing means that the pool makes sure connections are still live before they are handed to the application. Why would connections expire? That is beyond our and possibly your control - firewalls, server and database settings may make connections time out. The C3P0 homepage has more information on connection testing and the tradeoffs used herein [4].

  • lmc.c3p0.idleConnectionTestPeriod= Default: 30
  • lmc.c3p0.testConnectionOnCheckin= Default: true
  • lmc.c3p0.testConnectionOnCheckout= Default: false

Unreturned connections

NoTouch Center is carefully engineered to return connections to the pool after completing a transaction. However, given the wide variety of database types we can not be sure that e.g. deadlocks occur. If you experiences problems, please use these options to debug the issue like described here [5]

  • lmc.c3p0.debugUnreturnedConnectionStackTraces= Default: false.
  • lmc.c3p0.unreturnedConnectionTimeout= Default: 0

In a debugging scenario, one would set debugUnreturnedConnectionStackTraces to true and assign a sensible timeout like 60 seconds to unreturnedConnectionTimeout. Do NOT assign a very short timeout (anything below 30 seconds) - this would chop off legimitate transactions, crippling NoTouch Center operation!

If you use this, please keep an eye on NoTouch Center operation, read the log files frequently (do not rely on the Web GUI Log view) and be ready to reset these two options to default.