NoTouch Center stores its data into a SQL database. The primary database is MySQL. The Stratodesk 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 Microsoft SQL server this information is for you. Please also note that we can not support your own external database.
It is always recommended to keep regular backups of your Stratodesk database. See article: Retrieve a Backup of the Stratodesk NoTouch Center Database.
Contents
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;
create user 'easyadmin'@'localhost' identified by 'easyadmin';
grant all on easyadmin.* to 'easyadmin'@'localhost';My
Note: Older MySQL versions used to allow a shortcut that some people might still be familiar with. Does not work on MySQL 8 any more:
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.
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 Stratodesk Virtual Appliance comes with MySQL. If you use an external database, you can deactivate MySQL.
Stratodesk Virtual Appliance
- Login to the Virtual Appliance via Secure Shell and gain root privileges as described
- Stop the MySQL services by executing these command:
- stop mysql
- 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:
- Virtual Appliance Migration to migrate from one instance of the Stratodesk Virtual Appliance into another.
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:
- Stop NoTouch Center
- Migrate your database to the new server, taking into account all the information on this page
- Edit the database configuration values of tcmgr.properties (see above)
- Start NoTouch Center
MySQL backup and restore
For any MySQL installation, be it Stratodesk 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).
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! See article: Retrieve a Backup of the Stratodesk NoTouch Center Database.
The command line tools "dbimport" and "dbexport" are located in the installation directory of NoTouch Center.
On the Stratodesk Virtual Appliance it is
/opt/center
Exporting the database to a NoTouch Center XML file
The dbexport command is 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 Stratodesk 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>Stratodesk</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:
- Hikari (hikari). Hikari is the default connection pooling mechanism that NoTouch Center uses.
- C3P0 (c3p0). C3P0[1]another connection pooling mechanism.
- 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.