Microsoft Azure SQL
This page describes how to prepare a Microsoft Azure SQL database for PowerTSM, how to install a new PowerTSM database on it, and how to update an existing PowerTSM database to a higher version. Target versions are 3.8.0 or higher.
Prerequisites
Section titled “Prerequisites”- Version 2016 SP2, 2017, 2019
- SQL Server Management Studio 18
Preparing Azure SQL
Section titled “Preparing Azure SQL”Create New SQL Database Resource
Section titled “Create New SQL Database Resource”The following steps will show how to create a new Azure SQL database ready to be used with PowerTSM on Azure.
Creating a Database Server with a New SQL Database
Section titled “Creating a Database Server with a New SQL Database”-
Login to your Azure account via https://portal.azure.com.
-
Click on the Create a resource icon to open the Azure Market Place.

-
Search for Azure SQL in the Market Place and click on Create when found.

-
Depending on your needs either select “Single database” (to install one database in an existing server), “Elastic pool” (to dynamically distribute a fixed server resource among multiple databases) or “Database server” to host multiple databases.

We will select and describe Database server. If you select Single database, continue with step 7.
-
Select your Subscription, Resource Group or select Create New to add a new Resource Group, fill in your preferred Location, server name and Administrator account credentials.

-
Click on the Review + create and then the Create button to create your new server.
Database server is ready, let’s create a database within:
-
Select your Subscription, Resource Group (will be prefilled with the first resource having a Database Server), enter your Database details, you may also change Compute + storage (we will do it in a separate step).

-
Click on the Review + create and then the Create button to create your new database.
In order to access to Azure SQL from your local machine with Microsoft SQL Management Studio 18 or its command line SQL tool, you will need to add your IP address to the SQL server firewall.
Unblock Your IP in the SQL Server Firewall
Section titled “Unblock Your IP in the SQL Server Firewall”-
In Azure (in your Resource Group) select your SQL server.

-
Click on Show firewall settings.
-
Add Rule name, Start IP and End IP of your local machine with Microsoft SQL Management Studio 18 or higher and press the Save button.

Now you are ready to access the SQL server.
Where Is My Connection String?
Section titled “Where Is My Connection String?”Server name (required for database updates and connecting to Microsoft SQL Management Studio) and connection strings can be accessed via the following steps:
-
Open your Resource Group in Azure.
-
Navigate to the database and click on it.

-
The server name can be taken from tag Server name.
-
To get the connection string for PowerTSM or any other application, click on Show database connection strings.

Properly Size Your Database and Web App
Section titled “Properly Size Your Database and Web App”Azure provides various database and web app service plan types. Below we compare standard Database Transaction Unit (DTU) pricing model with the performance of a local barebone Microsoft SQL Database combined with a PowerTSM HTTP API (also running as Azure Web App vs. local Windows Service).
Learn more about the local hardware used…
HTTP API App Server
| Component | Specification |
|---|---|
| OS | Windows 10 Enterprise x64 bit Version 10.0.17763 Build 17763 |
| CPU | 2 x Intel Xeon X5675 3.06 GHz (2 logical processors) |
| RAM | 8 GB |
Database Server
| Component | Specification |
|---|---|
| Server Version | Microsoft SQL Server 2016 (SP2-GDR) (KB4293802) - 13.0.5081.1 (X64) |
| OS | Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600) |
| CPU | 2 x Intel Xeon X5675 3.06 GHz (2 logical processors) |
| RAM | 16 GB |
Azure Sizing Matrix
The below Azure sizing costs are as of 2019-03-15.
| DTU | S2 (50) | S3 (100) | S3 (100) | S4 (200) | S4 (200) | S6 (400) |
|---|---|---|---|---|---|---|
| App Service Plan | S2 | S2 | P1V2 | P1V2 | P3V2 | P3V2 |
| DTU Costs | 63.26 | 126.49 | 126.49 | 253.01 | 253.01 | 506.01 |
| App Service Plan Costs | 75.22 | 75.22 | 125.44 | 125.44 | 501.90 | 501.90 |
| Total Costs per Month | 138.48 | 201.71 | 251.93 | 378.45 | 754.91 | 1,007.91 |
| Score* | 244.52% | 184.85% | 146.45% | 100.15% | 72.16% | 60.95% |
*The Score is a result of combined performance measures of write (20% weight), read (50% weight) and interval transformation (30% weight) requests for the different Azure pricing models divided by local benchmarks. Hence the lower the score, the better Azure will perform compared to a local installation.
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.
Further instructions
If the PowerTSM databases are to be created, 2 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_datareaderdb_datawriterdb_owner
This can be done with the following script:
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;GOUser Rights
Section titled “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_datareaderdb_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 the HAKOM.xml file.
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. -
Open file
configuration.iniand set the following variables accordingly:;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 (in Azure SQL the same db is used for time series and access control list):dbACLName=TSM_DBNAME;DB-Login DatadbLoginName=usernamedbLoginPassword=pwd -
Call the batch file at
...\Database Scripts\SQLServer\_specific scripts\CreateDB_initial_3_8_0_to_latest.cmd.
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”Checking the current database version of ACL, FW, Protocol and TSM objects. Check the entries of the table DBVERSION. The interpretation of the entries is explained in the following example.
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. 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 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.sql
- For the time series database (
HAKOM_TSM), run all scripts namedHAKOM_Framework.sqlHAKOM_Framework_Index.sqlHAKOM_Framework_xxx.sqlHAKOM_TSM.sql
- For the ACL database (
- Repeat steps 1 and 2 for every version between the installed version and the target version, in ascending order.