can integrate with an external MySQL or MS SQL installation through the standard TCP/IP connection. By default, these custom database installations may not have some of the services/protocols enabled. 

MS SQL Installation

Here are some of the recommendations by our developers to assist the database administrator in setting up the MS SQL server for optimized integration with 

If the MS SQL database takes part in mirroring or AlwaysOn Availability Groups, the database administrator must set the READ_COMMITTED_SNAPSHOT or "Is Read Committed Snapshot On" database option to ON or "True" before  is installed.

Prerequisites

Follow these steps

  1. Install MS SQL.
  2. During installation, select Mixed Authentication.
  3. Create the required databases. Do not use System Databases for the  installation. See more about SQL databases from Microsoft. 

  4. Enable MS DTC for XA transactions.
  5. Configure the JDBC Distributed Transaction Components.
  6. If MS SQL is installed on a different computer than the machine where is being installed, the RegexEvaluator.exe file must be located on the computer with the MSSQL server. For example, if you were installing at C:\Agiloft, RegexEvaluator.exe should be installed on the computer with the MS SQL server in the directory with this path: C:\Agiloft\resources\java\resources\sql\mssql\RegexEvaluator.exe.

If MS SQL is already installed with a different authentication method, you can follow these steps to change to Mixed authentication.

Enable MS DTC for XA transactions

In Windows 7, Windows 10, Windows Server 2008 R2 or Windows Server 2012 or later:

  1. Select Control Panel > Administrative Tools > Component Services.
  2. In  Component Services, double click Computers, double click My Computer  and select Distributed Transaction Coordinator
  3. Right-click Local DTC and then select Properties.
  4. Click the Security tab in the Local DTC Properties window.
  5. Select the Enable XA Transactions check box, click OK. This will restart the MS DTC service.
  6. Click OK again to close the Properties window, and then close Component Services.
  7. Restart SQL Server to ensure that it syncs up with the MS DTC changes.

Configure the JDBC Distributed Transaction Components

  1. While the  installer is running, and at the stage of asking for the MS SQL credentials, navigate to the Agiloft\resources\resources.zip\resources\java\lib\mssqlxa directory and open the files inside your zip viewer. 

    1. Copy the  sqljdbc_xa.dll  file from the directory to the Binn directory of the SQL Server computer.

      Note: For a default SQL Server install, the location is  C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Binn .

    2. Run the  xa_install.sql database script from the directory:
    3. In the command prompt, enter sqlcmd -i xa_install.sql. This script installs the extended stored procedures that are called by  sqljdbc_xa.dll . These extended stored procedures implement distributed transaction and XA support for the Microsoft SQL Server JDBC Driver. You will need to run this script as an administrator of the SQL Server instance. You can ignore errors about being unable to drop procedures that don't exist.
  2. Change the password and database name (sw2_std) if needed in the aluser.sql script and run it the same way as xa_install.sql. The script creates an  user with SysAdmin role and the ability to use XA Transactions. For best results, do not revoke the SysAdmin role for this user.
  3. Close the SQL Server Management Studio. If this is not closed, the  installer will not be able to get an exclusive lock to set the database options, and will wait forever. 
  4. Continue with the  installation. 

MySQL Installation

Here are some of the recommendations by our developers to assist the database administrator in setting up the MySQL server for optimized integration with 

Prerequisites

Follow these steps

  1. Download the latest MySQL version, and use the steps at the MySQL Installation Guide to install it to your server.
  2. Verify that the database user has sufficient privileges to run custom scripts. The user will need System Administrator privileges at least.
    1. To create a new database user with system administrator privileges to integrate with , the following script format can be used:

      create login aluser with password='Qwerty1';
      go
      create user aluser for login aluser;
      go
      exec sp_addsrvrolemember 'aluser', 'sysadmin';
      go
  3. In MySQL, create a custom database named sw2_std, specifying UTF8 character set. You can use the following commands to do this: 

    create      # or alter 
    database sw2_std DEFAULT CHARACTER SET utf8;
    mysql> use sw2_std;
    Database changed
    mysql> SHOW VARIABLES LIKE 'character_set%';

    This will return something like the following:

    +--------------------------+----------------------------+
    | Variable_name | Value | 
    +--------------------------+----------------------------+
    | character_set_client | utf8 |
    | character_set_connection | utf8 | 
    | character_set_database | utf8 | 
    | character_set_filesystem | binary | 
    | character_set_results | utf8 | 
    | character_set_server | utf8 | 
    | character_set_system | utf8 | 
    | character_sets_dir | /usr/share/mysql/charsets/ | 
    +--------------------------+----------------------------+
     8 rows in set (0,00 sec)
  4. Configure InnoDB. The following parameters are recommended for the my.cnf  configuration file. Pay attention to the comments, as some of these values can vary depending on your environment. At a minimum, the max_allowed_packet must be set to 16M. For more information, see InnoDB Configuration:  

    [mysqld]
    #================mandatory values===============
    port = 3306  # This value can be changed. Ask your admin.
    bind-address = 0.0.0.0  # This value can be changed. Ask your admin.
    character-set-server = utf8
    collation-server = utf8_general_ci
    explicit_defaults_for_timestamp = 1
    innodb_locks_unsafe_for_binlog = 1
    innodb_strict_mode = 0
    sql-mode = NO_AUTO_VALUE_ON_ZERO
    innodb_file_format = Barracuda
    innodb_flush_log_at_trx_commit = 0
    default-storage-engine = InnoDB
    innodb_file_per_table = 1
    #================recomended minimum values==============
    max_connections = 384
    max_allowed_packet = 96M
    query_cache_type = 1
    slow_query_log = 1
    slow_query_log_file = <SLOW_QUERY_LOG>
    log_error = <MYSQL_ERROR_LOG>
    #================optional values================
    tmp_table_size = 64M
    key_buffer_size = 48M
    query_cache_size = 64M
    net_read_timeout = 1000
    table_open_cache = 2048
    connect_timeout = 20
    innodb_lock_wait_timeout = 150
    innodb_log_files_in_group = 3
    innodb_flush_method = O_DIRECT
    net_write_timeout = 1000
    user = enterprisewizard
    innodb_file_io_threads = 4
    innodb_log_buffer_size = 16M
    innodb_mirrored_log_groups = 1
    innodb_log_file_size=<INNODB_LOG_SIZE>
    tmpdir = <AGILOFT_TMPDIR>
    innodb_buffer_pool_size = 14000M
    innodb_additional_mem_pool_size = 100M
    sort_buffer_size = 5M
    query_cache_limit = 5M
    read_buffer_size = 1M
    join_buffer_size = 5M
    basedir = <MYSQL_DIR>
    innodb_data_home_dir = <MYSQL INNODB DIR>
    innodb_log_group_home_dir = <MYSQL_LOG_DIR>
  5. In Linux, load the MySQL time zone table using the following command on the server where MySQL is installed: 

    mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql --host=127.0.0.1 --port=3306 -u root -p <password> mysql;
  6. Add the default-time-zone entry to the my.cnf file. This file is usually located under /etc/my.cnf.
  7. Set the time zone value to match the server time zone. For example, if the server uses the Pacific time zone, set: default-time-zone=America/Los_Angeles,
  8. Restart the MySQL service.


Related articles