Microsoft SQL Server
This page describes how to set up new PowerTSM databases on Microsoft SQL Server and how to update existing PowerTSM databases to a more recent version.
Prerequisites
Section titled “Prerequisites”You can find the relevant system requirements here: System Requirements.
In addition to the system requirements it is also necessary to have SQL Server Management Studio installed.
In order to execute the scripts it is required to have the following set up for your database:
- A database user with
db_ownerprivileges
User Rights
Section titled “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_datareaderdb_datawriter
In case Integrated Security is deactivated, the user including the password must be specified in the HAKOM.xml file.
Installation
Section titled “Installation”Creating a New Database
Section titled “Creating a New 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 Installation Scripts
Section titled “Executing the Installation Scripts”- Place the PowerTSM installation package in a local folder.
- Navigate to folder
...\Database Scripts\SQLServer\_specific scripts\. - Open file
configuration.iniand set the variables. - Run the script at:
...\Database Scripts\SQLServer\_specific scripts\CreateDB_initial_3_8_0_to_latest.ps1.
Update
Section titled “Update”Updating an existing database is done in two steps: determine the version that is currently installed, then run the update scripts for every version in between, in order.
Preparation
Section titled “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.
Reading the version from the DBVERSION table
Section titled “ Reading the version from the DBVERSION table”Content of the DBVERSION table:
| ID | NAME | DESCRIPTION | CREATIONDATE | REVISION | PATCH | MAJOR | MINOR |
|---|---|---|---|---|---|---|---|
| 1 | HAKOM TSM | Initialscript | 2020-07-01 | 0 | 0 | 3 | 8 |
| 2 | HAKOM Framework | - | 2020-07-01 | 4 | 0 | 3 | 8 |
Interpretation:
HAKOM TSM: version 3.8.0.0HAKOM Framework: version 3.8.4.0
Manual Check Scripts
Section titled “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 Update Scripts
Section titled “Executing the Update Scripts”- In
...\Database Scripts\SQLServernavigate to next higher version as currently installed. - Run the scripts one by one in SQL Server Management Studio.
- For the ACL database (
HAKOM_ACL), run all scripts namedHAKOM_Acl.sqlHAKOM_Framework_Protocol
- For the time series database (
HAKOM_TSM), run all scripts namedHAKOM_Framework.sqlHAKOM_Framework_Index.sqlHAKOM_Framework_xxx.sqlHAKOM_TSM.sqlHAKOM_Framework_Protocol
- For the ACL database (
- Repeat steps 1 and 2 for every version between the installed version and the target version, in ascending order.