Skip to content
Documentation

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.

  • Version 2016 SP2, 2017, 2019
  • SQL Server Management Studio 18

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”
  1. Login to your Azure account via https://portal.azure.com.

  2. Click on the Create a resource icon to open the Azure Market Place.

    Azure portal home page with the Create a resource icon

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

    Azure SQL entry in the Azure Market Place with the Create button

  4. 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.

    Azure SQL databases resource type selection with the options Single database, Elastic pool and Database server

    We will select and describe Database server. If you select Single database, continue with step 7.

  5. 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.

    Azure Create SQL Database Server form with subscription, resource group, server name, location and administrator account fields

  6. 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:

  7. 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).

    Azure Create SQL Database form with subscription, resource group, database details and Compute + storage settings

  8. 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”
  1. In Azure (in your Resource Group) select your SQL server.

    Azure SQL server overview page with the Show firewall settings link

  2. Click on Show firewall settings.

  3. 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.

    Azure SQL server Firewalls and virtual networks page with the Save, Discard and Add client IP buttons

Now you are ready to access the SQL server.

Server name (required for database updates and connecting to Microsoft SQL Management Studio) and connection strings can be accessed via the following steps:

  1. Open your Resource Group in Azure.

  2. Navigate to the database and click on it.

    Azure SQL database overview page showing the server name and the Show database connection strings link

  3. The server name can be taken from tag Server name.

  4. To get the connection string for PowerTSM or any other application, click on Show database connection strings.

    Azure SQL database Connection strings page showing the ADO.NET connection string with the server name and port 1433

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.

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_datareader
  • db_datawriter
  • db_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;
GO

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 the HAKOM.xml file.

  1. Place the PowerTSM installation package in a local folder.

  2. Navigate to folder ...\Database Scripts\SQLServer.

  3. Open file configuration.ini and 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 Data
    dbLoginName=username
    dbLoginPassword=pwd
  4. Call the batch file at ...\Database Scripts\SQLServer\_specific scripts\CreateDB_initial_3_8_0_to_latest.cmd.

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.

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.

flask 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.0
  • HAKOM Framework: version 3.8.4.0

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.

  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 ACL database (HAKOM_ACL), run all scripts named
      • HAKOM_Acl.sql
    2. For the time series database (HAKOM_TSM), run all scripts named
      • HAKOM_Framework.sql
      • HAKOM_Framework_Index.sql
      • HAKOM_Framework_xxx.sql
      • HAKOM_TSM.sql
  3. Repeat steps 1 and 2 for every version between the installed version and the target version, in ascending order.
© 2026 Volue.Version v0.6.38017 November 2026