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.
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. |
Create the required databases. Do not use System Databases for the installation. See more about SQL databases from Microsoft.
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. |
In Windows 7, Windows 10, Windows Server 2008 R2 or Windows Server 2012 or later:
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.
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
.
xa_install.sql
database script from the directory:
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.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.Here are some of the recommendations by our developers to assist the database administrator in setting up the MySQL server for optimized integration with .
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 |
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) |
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> |
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; |
my.cnf
file. This file is usually located under /etc/my.cnf
.default-time-zone=America/Los_Angeles,
Related articles |