Installing HAKOM TSM on Microsoft SQL Server

Requirements

The system requirements are documented here: System Requirements

SQL Server Management Studio

Depending on whether a new HAKOM database is to be created or an existing one is to be updated, the sections New installation and Update must be selected. The new installation deals with the basic new installation of a HAKOM DB. The update describes the updating of an existing HAKOM DB to a higher version. Target versions are 3.8.0 or higher.

Path to install package

All neccessary scripts for SQL Server can be found under the following path: HAKOM TSM Standard_3.9.x.x\_Database Scripts\SQLServer

New Installation

Creating a New Database

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

Further instructions

In order to manage Time Series and User in separate databases, two databases must be created by the DB-Admin::

• HAKOM_TSM (Time Series Management)
• HAKOM_ACL (User administration)
• Additionally one user '"HAKOM" Server role: public

The newly created user must have following privileges on each database:

• db_datareader
• db_datawriter
• db_owner

Alternatively this can be done with the following script:

TEXT

EXEC sp_addrolemember 'db_datareader', 'HAKOM';
GO
  
EXEC sp_addrolemember 'db_datawriter', 'HAKOM';
GO
  
Create Role db_executor;
GO
  
EXEC sp_addrolemember 'db_executor', 'HAKOM';
GO
  
GRANT EXECUTE TO db_executor;
GO

User Rights

If the access of the users via Integrated Security=true is to be carried out by Windows users, the following rights must be assigned for all users on both databases:

• db_datareader
• db_datawriter

Alternatively, a dedicated user can be set up (Integrated Security = false). This user must also have the same authorizations.

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

Executing the scripts

1. Place the "HAKOM TSM Standard_3.8.x.x" Package in a local folder.
2. Navigate to folder ...\Database Scripts\SQLServer\_specific scripts\

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

   TEXT

   ;Insert initial data [true/false]
   insertData=true
   
   ;Add protocol package [true/false]
   addProtocolPackage=true
   
   ;Full server name:
   serverFullName=hostname,1433
   
   ;ZAMS database name (time series data):
   dbZAMSName=TSM_DBNAME
   
   ;ACL database name (access control list):
   dbACLName=ACL_DBNAME
   
   ;DB-Login Data
   dbLoginName=username
   dbLoginPassword=pwd

4. Run the batch file at: ...\Database Scripts\SQLServer\_specific scripts\CreateDB_initial_3_8_0_to_latest.cmd

Milliseconds support

To activate time series with intervals less than one second, a change must be made to all databases used by the TSM during installation. The database contains the function CA_InternalTimeUnit. It returns a fixed value:

• 0, seconds, beginn: 1972-01-01T00:00:00+01:00
• 1 (default),  seconds, beginn: 1970-01-01T00:00:00Z
• 2, milliseconds, beginn: 1970-01-01T00:00:00Z

On SQL Server the above function can be found under: "Programmability" → "Functions" → "Scalar-valued Functions"

The return value of the above function must be altered with the according numeric representation of the required time unit.

ALTER FUNCTION [dbo].[CA_InternalTimeUnit] ()
RETURNS smallint
AS 
   BEGIN
		--0: Seconds, HAKOM-Time
		--1: Seconds, Epoch-Time
		--2: Millisecond, Epoch-Time
		RETURN 0
   END

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. The interpretation of the entries is explained in the following example:

• 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. You can find them under ...\Database Scripts\SQLServer\V3.8.x\V3.8.x.x\_manual check scripts and if necessary, import them into SQL Server Management Studio and follow the instructions as described in the scripts.

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.

   

   Correct assignment of the scripts to the according database schemas

   ACL DB	HAKOM_Acl.sql	HAKOM_Framework_Protocol
   ZAMS DB	HAKOM_Framework.sql	HAKOM_Framework_Index.sql	HAKOM_Framework_xxx.sql	HAKOM_TSM.sql	HAKOM_Framework_Protocol

3. In HAKOM TSM Standard_3.3.8.x\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/forums/908035-sql-server/suggestions/39014263-functions-return-strings-in-else-if-are-cut-depen