Microsoft SQL Server

Requirements

You can find the relevant system requirements here: https://hakom.atlassian.net/wiki/spaces/TES/pages/11174453

In addition to the requirements listed there it is also necessary to have SQL Server Management Studio installed.

Path to install package

All necessary scripts for SQL Server can be found under the following path of the installation package: \_Database Scripts\SQLServer

Prerequisites

Executing the Scripts

In order to execute the scripts it is required to have the following set up for your database:

• A database user with db_owner privileges

User Rights

Users can be authenticated by their Windows Active Directory account (Integrated Security=true) or by dedicated SQL Server users (Integrated Security=false).

Following rights must be assigned for all users on both databases:

• db_datareader
• db_datawriter

In case Integrated Security is deactivated, the user including the password must be specified in HAKOM.config.

PowerShell SQL Server Module

The execution of some SQL Server commands via PowerShell requires at least version 21.0.0 of the PowerShell Module SqlServer: https://www.powershellgallery.com/packages/SqlServer

The script execution will abort and provide an error message if the module is missing or the wrong version is installed.

New Installation

Creating a New Database

The database must be configured to use a case insensitive collation. This applies to both the "TSM Time series management" as well as the "ACL User administration" database.

The creation of the databases must be performed by a database administrator.

In order to manage time series and users in separate databases, two databases must be created by the DB-Admin:

• HAKOM_TSM (Time series management)
• HAKOM_ACL (User administration)

Executing the scripts

1. Place the "HAKOM TSM Standard" Package in a local folder.
2. Navigate to the folder ...\Database Scripts\SQLServer

3. Open the configuration.ini file and set the variables.

4. Run the batch file at: ...\Database Scripts\SQLServer\Install-Latest.ps1

Update

Preparation

At first the current database version of ACL, FW, Protocol and TSM objects has to be checked. To do that, check the entries of the table DBVERSION.

Example of DBVersion:

• Table DBVERSION:

• Interpretation:

Version HAKOM TSM 3.8.0.0

Version HAKOM Framework 3.8.4.0

Manual check scripts

Before the update scripts are installed, it is important to check if manual check scripts have to be installed. Manual check scripts are located in a dedicated sub-folder of the version they correspond to. In case manual check scripts have to be executed please import them to Microsoft SQL Management Studio and follow the instructions.

Executing the scripts

1. In ...\Database Scripts\SQLServer navigate to next higher version as currently installed.

2. Run the scripts one by one in SQL Server Management Studio.

   1. For the HAKOM ACL database run all scripts named
      • HAKOM_Acl.sql
      • HAKOM_Framework_Protocol
   2. For the HAKOM TSM database run all scripts named
      • HAKOM_Framework.sql
      • HAKOM_Framework_Index.sql
      • HAKOM_Framework_xxx.sql
      • HAKOM_TSM.sql
      • HAKOM_Framework_Protocol
3. In \Database Scripts\SQLServer navigate to next higher version as currently installed.
4. Run the scripts one by one in SQL Server Management Studio.

Additional Information

Due to a bug in SQLServer 2019, you may experience errors when using some functions. Until the bug is fixed by the manufacturer, you can prevent the problem by making the following setting in the database:

SQL

ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = OFF;

Further information on the subject can be found here: https://feedback.azure.com/d365community/idea/3eeae8de-5225-ec11-b6e6-000d3a4f0da0