LISTSERV Maestro uses a “system” database to store its working data – recipient profiles, job ID numbers, tracking information, and so on. An “internal” database (based on MySQL) is included as part of the application and may be used as the system database. Using this internal database will allow the application to run “out-of-the-box”. An optional external database may be configured in place of the default internal database if desired. Switching the system database from internal to external and vice versa can be done at any time as long as both databases are accessible to LISTSERV Maestro during the transfer.
Notes:
See http://www.mysql.com for details
about MySQL 4.1.7 and its features to determine if it meets your requirements
and expectations for a production database. Reviewing the features will
help you decide if you want to use the internal database or if you prefer
to install a different external system database.
The internal MySQL database is installed on the same server as the Maestro
User Interface component (if installation of MySQL was not deselected
during setup). You can find the database binaries, including the server
and client tools, in this folder on that server: [maestro_install_folder]/lui/database/bin
LISTSERV Maestro is also supports various other databases, of various versions.
· IBM DB2 Universal Database
° V7.2
° V8.2
· Microsoft SQL Server
° 6.5 (as user-database only)
° 7.0 (recommended as user-database only, see note below)
° 2000
° 2005
· MySQL
° 3.23.42 and later 3.23.x builds (recommended as user-database only, see note below)
° 4.0 (recommended as user-database only, see note below)
° 4.1
° 5.0
· Oracle
° 8i
° 9i
° 10g
Any comparable/compatible versions should also be usable. Any versions earlier than the ones mentioned here are not officially supported as system databases.
You can install any of these databases (or a compatible version) on the server where you have installed the Maestro User Interface component (or on any other server that is reachable over the network) and configure the Maestro User Interface to use this database instead of the internal database. (Please refer to the documentation of the database in question for details about how to install and configure the database).
Note: SQL Server 7.0, MySQL 3.23.x and MySQL 4.0 as a system database are also supported but for backwards compatibility with existing installations only. These versions are not recommended to be used with new installations and it is strongly recommended to upgrade existing installations to a newer SQL Server or MySQL version (or switch to one of the other supported databases).
In order to use an external database as the system database, the database must be prepared for use with LISTSERV Maestro, and LISTSERV Maestro must be configured to use the prepared database. LISTSERV Maestro must transfer all data from the old database to the new database in order to switch from the internal system database to an external database, even before LISTSERV Maestro is first used. Both databases must be running and accessible to LISTSERV Maestro during the transfer.
Important: If one external database is being switched for another external database, both external databases must be accessible to LISTSERV Maestro simultaneously so that the data can be automatically transferred.
Follow these steps to configure an external database:
1. Install the DBMS and prepare the database to be used. Follow the manufacturer’s instructions to install the DBMS. See Section 4.2 Preparing the System Database for details on how to create and prepare the system database. Some instructions are different depending on the DBMS in use.
2. If changing the system database for an installation that has been in use, make a backup before making any changes so that the current data may be recovered in case of errors. See Section 11 Saving and Restoring a Backup for more information.
3. Install the corresponding JDBC driver on the server where the Maestro User Interface (LUI) is installed. See Section 5.1 Available Database Plugins for details.
4. Register the appropriate “Plugin” in the Administration Hub (HUB) component. See Section 5.2 Registering a Database Plugin for more information.
5. Define the System Database Connection as follows:
a. Log into the Administration Hub. Click on the Global Component Settings icon. Next, click Maestro User Interface, and then click System Database Connection. The System Database Connection screen opens.
Figure 5 System Database Connection Screen
b. Select The following external database is used as the System Database.
c. Click the Database Plugin drop-down menu and select an external database to use as the System Database.
d. Fill out the database connection detail fields. These fields are different for each type of database selected.
e. Click [OK] to submit the change.
Figure 6 System Database Connection Details Screen for DB2
6. Shut down LISTSERV Maestro and then restart.
7. Wait for the transfer of data between the two databases to be completed. Depending on the amount of data to be transferred and other factors such as network speed and disk I/O, this could take minutes or hours. Check the LUI logs to verify the successful completion of the transfer before proceeding to the next step. Logs are documented in Section 13 Maestro Logs. Do not shut down the server while the transfer is taking place or you will have to restore from backup and start over.
8. If desired, delete the old LISTSERV Maestro database from the original database application. If the default internal database was previously being used, it can be disabled to save system resources. See Section 4.4 Removing or Adding the Internal Database for further instructions.
Under normal operation, it will never be necessary to revert from an external system database to the internal system database. However, if the need ever arises, follow these steps to restore the internal system database:
1. If the internal database was disabled, re-enable it. See Section 4.4 Removing or Adding the Internal Database for further instructions.
2. On the System Database Connection screen, select the Use the internal database as the System Database option.
3. Shut down LISTSERV Maestro and then restart.
4. Wait for the transfer of data to complete before deleting the external database.
Before the Maestro User Interface can be used together with a freshly installed system database, the database must be prepared in certain ways. Outlined below are explanations of the required preparation steps, followed by details specific to each of the supported database management systems.
LISTSERV Maestro must have its own database, separate from any other databases. The database may use the same database server as another database, but must not interact with the other databases on that server. Even if a database is created for storing recipient information for LISTSERV Maestro or for storing LISTSERV lists, it should be a separate database. LISTSERV Maestro can be given access to these recipient databases separately (see Section 5 Defining External Database Connections).
A user account must be created within the database server for LISTSERV Maestro to access the system database. This user will then be selected from the Maestro User Interface to connect to the database. Certain privileges are required for the user account, as described in the DBMS-specific sections below.
The database that is used as storage for the Maestro User Interface should be configured in a way that it allows dynamic growth because the data stored by the Maestro User Interface grows over time. The growth rate corresponds to the number and the size of the email jobs that are delivered. Large email jobs with a high volume of collected tracking events will use more database storage space than smaller email jobs.
Some examples of upper limits that might need to be adjusted for large volume environments are:
User space quota – Most databases limit the amount of space that a given user may store in the database. This limit should be set to "unlimited" or a sufficiently large value for an organization’s database usage.
Database or tablespace size – Many database vendors, especially those supporting larger database environments, support the sub-division of the database server in smaller areas, sometimes called "tablespaces" or a similar term (see the database documentation for details). Normally, each database account is assigned to one of these areas, which is then referred to as "default tablespace" or "standard tablespace". This part of the database should be configured in a way that it allows dynamic growth, if possible.
Note: It is possible to use the Maestro User Interface with a database that does not support this type of dynamic growth. To do so, an administrator should make it part of the daily or weekly routine to check the amount of space available for the Maestro User Interface, and then increase it manually when necessary.
File system size – Like other server applications storing persistent data on the file system, the database storing the Maestro User Interface data must reside on a server whose file system is monitored on a regular basis, either through automated system administration tools or by an administrator who regularly checks the system.
Important: Deleting or archiving old jobs from LISTSERV Maestro on a regular basis will prevent the database from becoming unnecessarily slow.
Tip: The amount of table space and file system space utilized by LISTSERV Maestro will vary drastically based upon the particular usage of the system. Regular monitoring of the database and disk usage are essential to ensure adequate storage space at all times.
In the SQL Server Management Console, create a new database for sole use by the Maestro User Interface. Please see the SQL Server documentation for details about how to create, configure, and optimize a database.
Important: No matter if you create a new database or use an existing one, make sure that the database uses a case-insensitive collation. L-Soft recommends using the Latin1_General_CI_AS collation. This means that if you want to use an existing database, then you can only do so if this existing database already uses a case-insensitive collation.
Once a new database has been created, create a user account that the Maestro User Interface can use to connect to the database. Create a new user with any desired name and give it the db_owner role for the created or selected database.
A new Oracle database for sole use by the Maestro User Interface must be created so that it uses UTF-8 as its database character set. The database character set UTF-8 is required and the Maestro User Interface will not work with a database that has a different character set. (See the Oracle documentation for details).
Use an Oracle administration tool (such as SQL*Plus), to create a new user. This new user must have the CREATE SESSION and the CREATE TABLE privilege and a sufficiently large table space quota in the user's default table space.
The Maestro User Interface does not require unusually large rollback segments. If duplicate elimination is performed for large email jobs, larger temporary segments are needed as duplicate elimination is performed with a database sorting operation. See the Oracle documentation for more details on how to configure and optimize databases.
The “maximum key length” value is a feature specific to Oracle. This value is an internal value inherent to each Oracle installation. It is determined mainly by the block size used by the database but may also be influenced by other factors, like the operating system.
For LISTSERV Maestro to be able to create its database table in an optimal manner, it needs to know the maximum key length value used by the Oracle database that is used as the system database. LISTSERV Maestro cannot query the database for this value. The administrator has to determine the maximum key length value used internally by the Oracle database installation and input the correct value.
If the value entered exceeds the actual maximum key length used by the database, runtime errors could result, and LISTSERV Maestro will not work correctly. If a value that is smaller than the actual value is entered, LISTSERV Maestro will tailor its database tables accordingly in order to meet this smaller value. As a result, the database tables will be created with a sub-optimal structure and the user may run into database column size limitations, which would be avoidable if the correct maximum key length value had been supplied on the System Database Connection Details screen.
Oracle documentation concerning which maximum key length value is used under which circumstances is sparse. A commonly used “rule-of-thumb” value is that with a block size of 4K, the maximum key length is 1578. With a block size of 8K, the maximum key length is double the value of 4K, or 3156. Generally, the maximum key length seems to be about 38% - 40% of the block size. If in doubt, please consult the Oracle documentation or contact Oracle support.
Use the DB2 Control Center application to create a new database and define UTF-8 as the database code set. The database code set UTF-8 is required and the Maestro User Interface will not work with a database that has a different code set. (See the DB2 documentation for details.)
Configure the database with at least one user table space and one system temporary table space with a page size of 32K each. It may also be necessary to create a buffer pool with a page size of 32K before the table spaces with 32K page size can be created. Next, create a new database user for sole use by the Maestro User Interface. This user must be configured to use a table space with 32K page size; otherwise the Maestro User Interface will not work. The new user needs the Create Table privilege.
If you are using DB2 V8.2, it is recommended that you use the V8.2 Thin Driver. If this is the case, then no further preparation is necessary.
If you are using DB2 V7.2, then the native driver is the only available driver. For the native driver, you need to create a database alias on the server that is running the Maestro User Interface component. In the Client Configuration Assistant, click the [Add] button to create a new alias. Select the Search the network option and continue with the wizard. For more details on IBM DB2 database administration and the definition of database aliases, see the IBM DB2 documentation.
Notes:
The name of this alias is the value for the Database name parameter
of the IBM DB2 database plugin that comes with the Maestro User Interface.
The DB2 V8.2 database can also be accessed with the native driver; however,
the V8.2 Thin Driver is highly recommended.
LISTSERV Maestro supports the older 3.23.x versions of MySQL (with 3.23.42 being the oldest officially supported build), as well as the current 4.0, 4.1 and 5.0 versions (5.0 being the most recent one at the time this was written). However, even though the older builds are supported, if you want to use MySQL as the system database, 4.1.7 or later is recommended.
To use the Maestro User Interface with MySQL, set up MySQL to use the InnoDB Tables table type (see the MySQL manual for more details). This table type supports transactions, and the Maestro User Interface requires a table type that supports transactions. Recent versions of MySQL (for example, version 4.1.7) come with a MySQL Server Instance Config wizard that lets the user specify settings for the database server. These settings will automatically be written to the MySQL configuration file. Within the configuration wizard, specify a database usage type that enables the transactional InnoDB storage engine. If no configuration wizard is available for your MySQL version, the InnoDB storage engine must be enabled manually in the configuration file. See the MySQL manual for more details.
In MySQL versions 3.23.50 and later, the InnoDB tablespace files can be configured to be “auto-extending”, i.e., they will grow automatically as needed. (This is the default when using the configuration wizard; otherwise, the auto-extend option should be manually specified in the configuration file.) For sites running earlier versions of MySQL, the InnoDB data files and log files must be created with sufficient size to accommodate the planned usage of LISTSERV Maestro. The database administrator should monitor the remaining capacity of the tablespace regularly and extend it as necessary.
After the MySQL database server is set up to use the InnoDB table type, create a new database specifically for use with the Maestro User Interface. Create a user to use with LISTSERV Maestro.
To connect to the database, start the MySQL client program mysql.exe from the bin folder of the MySQL binary installation (on Linux/Solaris, the client program is mysql.) To create a new database, enter the following command in the MySQL client: create database DBNAME; where DBNAME is replaced with the name of the database. Grant privileges by entering the following grant command for the username: grant all on DBNAME.* to NAME@HOST identified by 'PASSWORD'; where the uppercase values are replaced as follows:
· DBNAME: The name of the database to be used with the Maestro User Interface. (This is usually the same name used in the “create database” command; see above. It is recommended that you use an all-lowercase spelling for the database name.)
· NAME: The user name of the user to be created and granted privileges.
· HOST: The host name of the server where the Maestro User Interface is running that will access this database.
· PASSWORD: The password associated with the user name.
Using “grant all” as described above grants all privileges on the given database to the given user. This is usually acceptable if the particular database was created specifically for use with the Maestro User Interface. However, if there is concern about granting the full set of privileges to the user, use the following privilege list instead of “all”:
select, insert, update, delete, index, create, drop
The following general information about how the Maestro User Interface uses the database can help optimize the database installation for use with the Maestro User Interface.
· The Maestro User Interface does not use large transactions. Any transactions that are opened are then closed after a maximum of a few hundred inserts or updates.
· During normal usage, the Maestro User Interface behaves with OLTP (online transaction processing) characteristics. There is a constant switch between read and write on the database. However, if there are many reports running on the collected tracking data, the characteristics of the Maestro User Interface’s behavior shift more and more into OLAP (online analytical processing), where the amount of (complex) reads outnumbers the amount of writes.
Use this information to optimize the database after analyzing the usage of the Maestro User Interface to determine if it is working more with OLTP or OLAP characteristics.
If you connect the Maestro User Interface to an external database, there is no longer a need for the internal MySQL database to run together with LISTSERV Maestro. To reduce the resource usage of LISTSERV Maestro, disable the internal database after configuring the Maestro User Interface to use an external database.
To uninstall the internal database, you’ll need to run the LISTSERV Maestro Setup wizard on the server where the Maestro User Interface component is installed. (See the Installation Manual for further information.) Once you start the Setup wizard, simply modify the installation and deselect the Internal MySQL database component. Leave the other components as they were. The Setup wizard will automatically uninstall the internal database.
Important: After the internal database is disabled, it is no longer possible to select the Use the internal database as the System Database option on the System Database Connection screen of the Maestro User Interface (see Figure 5). If this choice is selected by mistake, and LISTSERV Maestro is restarted with no external database configured, the Maestro User Interface component will not start, as it will not be able to find the now disabled instance of the internal database. If, at a later point, the internal database is desired, it must be re-installed.