Microsoft SQL Server Setup - EcoSys - Version 8.6 - Installation - Hexagon PPM

EcoSys Installation

PPMProduct
EcoSys
PPMCategory_custom
Installation & Upgrade
Version_EcoSys
8.6

Microsoft SQL Server database prerequisites and setup.

Microsoft SQL Server Prerequisites

  1. EcoSys is supported on SQL_Latin1_General_CP1_CI_AS collation.

  2. Enable the "Allow remote connections to server" setting.

Setting Up the SQL Server Database

  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 password other than "fmuser" will be used for the "fmuser" login, modify the portion of the script that creates the "fmuser" login and password (CREATE LOGIN fmuser WITH PASSWORD = 'fmuser').

  2. Execute the fm_ss_create_db_and_users.sql script as SA or a privileged user.

  3. Import the EcoSys Standard Products Database .bak file using Microsoft SQL Server Management Studio.

  4. Execute the \EcoSys\database\sqlserver\utils\fm_sqlserver_attach_users.sql script as SA or a privileged user. The user and password in the script are hardcoded as "fmuser" so edit prior to running if necessary.

  5. Execute the \EcoSys\database\sqlserver\utils\fm_ss_loopback_fix.sql script as SA or a privileged user.

  6. Execute the \EcoSys\database\sqlserver\utils\fm_ss_updateNextKeyProc.sql script as SA or a privileged user.

  7. The EcoSys Standard Products Database is provided at EcoSys major release (.01) schema versions. If a non-major release of the EcoSys application is being installed, refer to Determining the Database Upgrade Path and Upgrading the Database to determine which database upgrade scripts need to be run,if any.

  8. Execute the \EcoSys\database\sqlserver\utils\fm_ss_job_update_stats.sql script as SA or a privileged user. For additional information on this script, refer to Updating EcoSys Database Statistics section.

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 that helps in improving the performance.

Memory Optimized tables are supported only for the SQL Server versions as mentioned in the table below:

Version

SQL Server Enterprise Edition

SQL Server Any Addition

SQL Server 2012

No

No

SQL Server 2014 SP3 or later

Yes

No

SQL Server 2016 or later

Yes

SP1 or later

SQL Server 2017 or later

Yes

Yes

Steps to enable Memory Optimized Tables

To enable the Memory Optimized Tables, follow the steps below to upgrade your existing database.

  1. Upgrade the EcoSys database to the latest Schema Version

  2. Run the script \EcoSys\database\sqlserver\utils\fm_ss_frsec_memory_optimized.sql

  3. Change the "FILENAME" parameters to match the location of the database data and log files.

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

  4. Execute the fm_sqlserver_frsec_memory_optimized.sql script as SA or a privileged user

  5. Restart EcoSys.