Microsoft SQL Server Setup - EcoSys - 9.0 - Installation & Upgrade - Hexagon

Microsoft SQL Server Prerequisites

1. Enable the "Allow remote connections to this server" setting.

2. Enable SQL Server Authentication mode.

EcoSys Collation Support

The EcoSys Standard Products database is built with the collation set to SQL_Latin1_General_CP1_CI_AS.

EcoSys supports:

• Running on an SQL Server instance with any collation set at the instance level.

• Changing the EcoSys database collation to other collations is supported if the target collation is Case Insensitive (CI).

Setting Up the SQL Server Database

All scripts in this section should be executed as SA or a privileged user.

Helpful information can be found at the top of the database scripts.

1. Edit the ...\EcoSys\database\sqlserver\setup\fm_ss_create_db_and_users.sql script:

   1. Change the two "FILENAME" parameters to match the location of the database data and log files. Do not change the names of the files (esfm_dat.mdf and esfm_log.ldf).

   2. If a non-default password will be used for the "fmuser" login, modify the portion of the script that creates the "fmuser" login and password.

2. Execute the fm_ss_create_db_and_users.sql script.

3. Import the EcoSys Standard Products Database .bak file.

4. Execute the ...\EcoSys\database\sqlserver\utils\fm_sqlserver_attach_users.sql script. The user and password in the script are hardcoded. Edit as necessary.

5. Execute the ...\EcoSys\database\sqlserver\utils\fm_ss_loopback_fix.sql script.

6. Execute the ...\EcoSys\database\sqlserver\utils\fm_ss_updateNextKeyProc.sql script.

7. Execute database statistics update. For additional information on this script, refer to Updating EcoSys Database Statistics

Enabling Memory Optimized Tables in SQL Server

EcoSys supports making the Cost Object Security Access table Memory Optimized in SQL Server. Using the Memory Optimized tables, the rows in the table are read from and written to memory which improves the performance.

Memory Optimized tables are supported by the SQL Server versions supported by this EcoSys release:

Steps to enable Memory Optimized Tables:

1. Edit the script ...\EcoSys\database\sqlserver\utils\fm_ss_frsec_memory_optimized.sql

2. Replace the <path> parameter to match the location of the database data and log files.

   Do not change the names of the files (esfm_mod).

3. Execute the fm_ss_frsec_memory_optimized.sql script.

4. For SQL Server 2017 and above, execute the fm_ss_table_types_memory_optimized.sql script as SA or privileged user.

5. Restart the application server.