Microsoft SQL Server Setup - EcoSys - 8.8 - Installation & Upgrade - Hexagon PPM

EcoSys Installation

Language
English (United States)
Product
EcoSys
Search by Category
Installation & Upgrade
EcoSys Version
8.8

Microsoft SQL Server database prerequisites and setup.

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 from SQL_Latin1_General_CP1_CI_AS to other collations as long as it is Case Insensitive (CI).

For information on how to change the database Collation, refer to the Customer Support portal.

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. When Ecosys is installed, the EcoSys Standard Products backup file name includes full version name including the date. For example, EcoSys EC<Standard Product version><EcoSys version><Database platform and version>.zip. This will be the minimum version of EcoSys that the backups will be compatible with. If you want to use the database with a later version of EcoSys, refer to Determining the Database Upgrade Path and Upgrading the Database sections of the EcoSys Upgrade Guide to determine which database upgrade scripts need to be run after it is restored, if any.

  8. 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 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 2014 SP3 or later

Yes

No

SQL Server 2016 or later

Yes

SP1 or later

SQL Server 2017 or later

Yes

Yes

SQL Server 2019 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. Edit 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.

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

    If the SQL Server database version is 2014 Enterprise Edition, use fm_ss_table_types_memory_optimized_sql2014EE.sql.

  5. Restart EcoSys.