20.2.1 Setting up a Direct SQL Connection Shared Database

This section has instructions for setting up a new Shared Database. For instructions on how to connect to a Shared Database from Geneious Prime refer to section 20.1.3 .

Supported Database Systems

To use a database as a Shared Database, Geneious requires that it support transactions with an isolation level set to READ COMMITTED. Supported database vendors include Microsoft SQL Server, PostgreSQL, Oracle and MySQL. It is possible to use other database vendors if you provide the database driver, see section 20.2.1 .

The following SQL database versions (with the default configurations) are currently tested as Shared Databases:

• Microsoft SQL Server 2019
• PostgreSQL 13.2
• Oracle Database 19c
• MySQL 8.0

Document Size Limitations

For Direct SQL Connection Shared Databases, document size is limited by the database’s maximum binary large object (BLOB) size. This varies between 1-4 GB. Refer to the documentation of your database system for the maximum BLOB size.

Recommended SQL configurations for the Shared Database

In most cases, the default settings for whatever SQL database system you are using are sufficient. The character set encoding for MS SQL should be set to UCS-2, all others should use UTF-8 to avoid any potential problems indexing documents containing unusual characters.

To avoid connection failures when many users access the Shared Database, the default connection pool should be adjusted to accommodate 6 connections for each user.

There are a few exceptions where we recommend changes to the default configuration, listed below:

MYSQL

Some changes you may need to make to the MySQL configuration file (my.cnf) to improve performance are as follows:

• innodb_buffer_pool_size=[On a dedicated database server you may set this parameter up to 80% of the machine physical memory size]
• innodb_flush_log_at_trx_commit=2
• query_cache_limit=2M
• query_cache_size=128M
• max_allowed_packet= 1073741824 (Note this is the maximum size for max_allowed_packet and can prevent errors when handling large files)

Further optional improvements:

• innodb_log_file_size=256M
• innodb_log_buffer_size=16M

Note: If you change the innodb_log_file_size then you will need to delete the current log files before the server will start up again.

Microsoft SQL Server

The character encoding for MS SQL should be changed to UCS-2. Other default settings for MS SQL server are generally sufficient. However, we strongly recommend using Snapshot Isolation to avoid deadlocks. These can occur when multiple users use the shared database at once and may lead to failure of some actions. This setting becomes necessary if there will be more than a handful of users using the database concurrently. Read more about Snapshot Isolation here.

Setting up an SQL database to use with Geneious Prime

Follow these steps to set up a Direct SQL Connection shared database to use with Geneious Prime. The instructions in this section require knowledge of how to create and administer an SQL database and are intended for your IT department or system administrator. For instructions on connecting to your shared database once it has been set up, refer to section 20.1.3 .

1. Create database
   • Install a supported database management system if you do not already have one (see section 20.2.1 ).
   • Create a new database with your desired database name. Make sure that you have a user that has rights to create tables.
   • Make sure to create the database with a suitable character set and collation - UTF8 (or UCS-2 for Microsoft SQL Server) is recommended.
   • You should consider possible limitations to document size due to the database’s maximum BLOB size when selecting a database system.
2. Add users to your database using the database management system
   • Make sure all users of the database have SELECT, INSERT, UPDATE and DELETE rights, otherwise they will not be able to use the Shared Database as intended.
3. Set up database with Geneious Prime
   • Run Geneious Prime and click on the Shared Database icon in the Sources panel, then click the New database connection button to connect to your database. Geneious will automatically set up the database when you connect, if it has not yet been set up.
   • This will only succeed if you have permission to create tables on the database.
4. Set the first Database Admin
   • To create your first Database Admin, right click on the root folder of your database and select Make the current user a Database Admin. This option is only available when your shared database does not have any Database Admins.
   • Additional Database Admins can be added by setting the user’s role in the group Everybody to ADMIN.
5. Set up sharing permissions for your database
   • By default, all folders in your database will be shared with all users. This will allow you to use your Shared Database like a shared local database. If this is what you want, no further setup is required.
   • If you want to restrict access to particular folders you can do so using Groups and Roles – refer to section 20.2.2 for more details. Groups can only be created by a Database Admin.

Your database should now be ready to use with Geneious Prime. Users can connect to the database by clicking on Shared Databases in the Sources panel and then clicking New database connection. This will bring up a dialog for the user to enter in the database details. If you wish to preconfigure the connection settings for users, you can do so via the geneious.properties file (see section 22.3 ).

Supplying Your Own Database Driver

Shared Databases were designed with the supported databases in mind and packaged with database drivers for them. However, Geneious allows you to supply your own jdbc database driver if you want to, for example if you have an updated driver, or want to use a driver for an unsupported database. It is not guaranteed that Shared Databases will work with an unsupported database system but it is likely that it will if you provide the correct driver.

You can supply your own driver under More Options in the New database connection dialog, or the Edit Account Details dialog (available by right clicking on the root folder of your shared database) for an existing shared database.