Database Considerations

This page describes the different considerations and pre-requisites for the database back-end used by PReS Connect, whether using the MySQL instance provided by the installer, or pre-existing (external) instance.

Using the MySQL Instance from the Installer

The MySQL Instance provided in the Installation Wizard is already pre-configured with options to provide the most stable back-end setup.

These are the specific options that have been changed in our version of "my.ini":

  • max_connections = 200 : PReS Connect uses a lot of database connections. This number ensures that even in high volume environments, enough connections will be available.
  • max_allowed_packet = 500M : In some implementations, especially when using Capture OnTheGo, large packet sizes are required to allow transferring binary files. This substantial packet size maximum setting ensures that the data received by PReS Connect will be able to be stored within the database.
  • character-set-server = utf8 , collation-server = utf8_unicode_ci , default-character-set=utf8 : These indicate database support for UTF-8/Unicode.

Installing / Updating Connect Using an existing local MySQL instance

If MySQL Server is already present and you wish to use it, the following should be taken into consideration:

  • The MySQL account must have access to all permissions using the GRANT Command, including creating databases.
  • The database configuration must include the options detailed in the Using the MySQL Instance from the Installer topic above.
  • The SQL instance must be open to access from other computers. This means the bind-address option should not be set to 127.0.0.1 or localhost.

If you chose not to install the supplied MySQL database, and instead opt for using a pre-existing (External) database then you yourself must ensure that the External database is accessible to Connect.

Objectif Lune Inc. will take no responsibility for database connections to any but the supplied MySQL database.
Options available within the installer:

  • The Configuration page for the local MySQL is displayed.
  • MySQL settings are pre-filled with default values if no existing MySQL db configuration is found.
  • MySQL settings are pre-filled with existing db configuration settings, if they point to a MySQL db type.

Installing Connect using an existing Microsoft SQL Server instance

If Microsoft SQL Server is already present and you wish to use it, the following should be taken into consideration:

If you chose not to install the supplied MySQL database, and instead opt for using a pre-existing (External) database then you yourself must ensure that the External database is accessible to Connect.

Objectif Lune Inc. will take no responsibility for database connections to any but the supplied MySQL database.

Since PReS Connect version 1.6 the minimum required version of the MS SQL Server is SQL Server 2012.

  • When MS SQL is selected, the default values for root user are sa and 1433 for the port.
  • If db settings from a previous installation are found, the pre-exising settings will be displayed for the matching db type (for MS SQL settings, this will only work if they were created with Server Config Tool 1.5.0 or later, or the Connect installer 1.6.0 or later). If the db type is changed in the configuration page, the default values for this db type will be displayed. If the pre-existing db settings are set to Hsqldb, the default db type selection will be MySQL.
  • Selected db settings are stored in the preferences as usual (C:\ProgramData\Objectif Lune\Ol Connect\.settings\ConnectHostScope\com.objectiflune.repository.eclipselink.generic.prefs)

Updating With No Local MySQL Product

  • When updating a Connect installation from 1.5.0 which contains a Server Product but no local MySQL Product, the DB Configuration Page will detect which db type was set before (especially if the db configuration was switched from MySQL to MS SQL using the Server Configuration Tool), and default to those settings.
  • On Update from 1.4.2 or earlier, the DB Configuration Page will always default to MySQL connection settings, and if the installation was manually tweaked to connect to MS SQL Server, the user has to switch to "Microsoft SQL Server" type and enter connection details again.

When modifying Connect

  • If local MySQL is removed from an installation, the DB Configuration page will offer additionally the Microsoft SQL Server db type with respective default values.
  • If local MySQL is added to an installation, the usual MySQL Configuration page with default values will be displayed.

If the user has installed the Installer Supplied MySQL and then switches to an external Microsoft SQL by using the Server Configuration Tool, the supplied MySQL cannot be switched off. By design the installer adds a service dependency between Connect Server and the supplied MySQL service.

The Microsoft SQL selection capability will be available only with 1.6 version and upwards.
To remove this dependency the user needs to do the following
  1. Have a foreign Microsoft SQL running, ready for use with Connect Server.
  2. Use the Server Configuration Tool Database Connection preferences to switch the database to Microsoft SQL.
  3. Re-start the Connect Server Service, so that the modifications become active.
  4. Counter check that everything is working properly with Microsoft SQL.
  5. Open a command-line prompt with full administration rights.
  6. Enter the command sc config OLConnect_Server depend= /. This removes the dependency.

    Please be aware: The key word depend must be followed immediately by the equal sign, but between the equal sign and the forward slash there must be a space.

    Additional information can be found here: http://serverfault.com/questions/24821/how-to-add-dependency-on-a-windows-service-after-the-service-is-installed#228326.

  7. After the dependency has been removed, it is possible to stop the supplied MySQL service (OLConnect_MySQL).
If a Connect 1.5 user wants to use Microsoft SQL instead of MySQL for the Connect Server, there are several points to be taken care of:
  • IF there should possibly be available some foreign MySQL instance, which could be used intermediately, then this should be selected during the setup. This ensures, that no stuff gets installed. Otherwise the supplied MySQL needs to be installed and the switch to Microsoft SQL needs to be done as outlined above.

  • It is not possible to uninstall the supplied MySQL in this case via a Connect 1.5 modify.

Important

If a Server Product and a MySQL Product were selected to be installed on Connect 1.5.0, and then the Server Configuration Tool is used to switch the database used by the Server to an external Microsoft SQL, then the Update to 1.6 requires an extra step. The procedure is as follows:

  1. Run the Update to Connect 1.6. This will assume the local MySQL database needs to be updated and configured, so the user has to enter a root password on the MySQL Configuration Page (can be any password matching Connect security rules).
  2. After the update, the Connect 1.6 Setup needs to be run once more to modify Connect.
  3. On the Product Selection page, now the MySQL product can be unselected.
  4. When stepping forward in the Wizard, the DB Configuration page will be displayed which allows to configure the Microsoft SQL Server with appropriate settings.

After this modification, the local MySQL is removed, and also the service dependency from Server to MySQL is removed.

If Connect was initially installed not containing the local MySQL product (i.e. on 1.5 installation an external MySQL was configured as database), then the Update to 1.6 will allow to select either external MySQL or external Microsoft SQL on the DB Configuration Page.

  
  • Last Topic Update: 18, January, 2018 06:24 AM
  • Last Published: 23, May, 2019 01:55 PM