Database (tC)

Owned by Fabian Seifert
Last updated: Sept 10, 2024 by Matthias Rieck
4 min read

JpaMetaService type “database" is deprecated but still supported. For new projects, "databasev2" is recommended, switching from the old version to v2 requires a migration using a custom sql script.

Configurationsparameter in repository.cfg

<repoName>.metaservice.type=databasev2 <repoName>.metaservice.databasev2.driver= <repoName>.metaservice.databasev2.url= <repoName>.metaservice.databasev2.user= <repoName>.metaservice.databasev2.password= #<repoName>.metaservice.databasev2.read-connections.max= #default: 32, not after 2.7.0 #<repoName>.metaservice.databasev2.read-connections.min= #default: 32, not after 2.7.0 #<repoName>.metaservice.databasev2.read-connections.shared= #default: false, not after 2.7.0 #<repoName>.metaservice.databasev2.write-connections.max= #default: 32, not after 2.7.0 #<repoName>.metaservice.databasev2.write-connections.min= #default: 32, not after 2.7.0 #<repoName>.metaservice.databasev2.cache-statements= #default: false, not after 2.7.0 #<repoName>.metaservice.databasev2.cache-statements.size= #default: 50, not after 2.7.0 #<repoName>.metaservice.databasev2.minimumIdle= #default: 10 minimumPoolSize #<repoName>.metaservice.databasev2.maximumPoolSize= #default: 10 #<repoName>.metaservice.databasev2.connectionTimeout= #default: 30000 time after when a request for connection lease will time out #<repoName>.metaservice.databasev2.idleTimeout= #default: 600000 time after when idle connections will be closed #<repoName>.metaservice.databasev2.keepaliveTime= #default: 0 #<repoName>.metaservice.databasev2.maxLifetime= #default: 1800000 #<repoName>.metaservice.databasev2.scripts= #default: classpath:sql/metav2/migration/h2|sqlserver|oracle, dependening on database provider , 3.0.0+ #<repoName>.metaservice.databasev2.transactionIsolation= #default: TRANSACTION_READ_COMMITTED, 3.4.3+

As of version 2.7.0 we switched from EclipseLink to Hibernate, therefor some parameters have changed.

As of version 3.4.0 the connection pool handling was improved. It allows using a smaller number of connections, i.e. changing the parameter minimumIdle to 2 should lower the utilization of the database without significant impact on overall performance. (Local tests with 120 parallel threads and min and max set to 1 worked without error at a duration for a write with 200ms)

H2

<repoName>.metaservice.databasev2.driver=org.h2.Driver <repoName>.metaservice.databasev2.url=jdbc:h2:C:/h2db.db;FILE_LOCK=FS;TRACE_LEVEL_SYSTEM_OUT=0

Driver is included.

Oracle

<repoName>.metaservice.databasev2.driver=oracle.jdbc.OracleDriver <repoName>.metaservice.databasev2.url=jdbc:oracle:thin:@localhost:49161:xe

Driver is not in the delivery (ab 2.3.0), download with:

JDBC and UCP Downloads page | Oracle Deutschland

see loader.path in Reference of application-wide parameters for loading external libraries

MSSQL

Driver is included from Version 2.3.0, for older Version driver can be found here:
https://docs.microsoft.com/de-de/sql/connect/jdbc/download-microsoft-jdbc-driver-for-sql-server?view=sql-server-ver16

Optimal performance can only be reached when the connection string contains sendStringParametersAsUnicode=false . Is this parameter missing the created indexes cannot be used and queries will be significant slower!

On Prem

Depending on your configuration and version of MS SQL Server you need additional properties in the connection string:

  • encrypt=true;

  • trustServerCertificate=true;

 

Azure SQL Server

  • <servername>: enter resource group, take sql server instance name

  • <database>: enter resource group, click sql server: take db name from overview page

  • <user> : the sql server admin has to be the tia core app id.

    • tia core app id: enter resource group, click your container app, click left Settings/Identity, enable in tab system assigned Status on. take Object (principal) id.

    • select as sql server admin

      • enter resource group, enter sql server, Settings/MS Entra ID, set admin, in search box put tia core app id, select the app via checkbox, hit select, press save

Managed Identity (MSSQL Database)

 

Database-URL for the repsitory.cfg

 

 

First: Prepare your MSSQL-Database in the Portal to allow managed Identity:

 

image-20240909-090106.png

There are two options to grant access to the MSSQL-Database:

 

  • Add the Managed Identity as a Database-Administrator directly to Database (See: Step-by-Step: System Assigned Managed Identity)

OR

When doing it that way, its important to add the managed identity as a member to the role “db_datareader” andd the role “db_datawriter”

The App-Service-Name is the name of the managed-identity (in case of a server managed identity: this is equals to the name of the container app itself)

 

 

Step-by-Step: System Assigned Managed Identity set as Database-Admin

This Option grant full MSSQL Server Admin Rights to the system assigned managed identity (and therefore, to the app it belongs to).

1.) Create a system assigned managed identity in your container app by Setting the Status to “ON”.

In the background, Microsoft will create a Buisness App in your Entra-ID which is directly linked to this container app. System assigned managed identities “live” in the same lifecycle as the application itself. In case you delete the app, the system assigned managed identity is deleted as well.

 

image-20240909-090726.png

2.) Copy the Object (principal) ID from that freshly created system assigned identity to your clipboard.

3.) Change to your Database Server into “Settings” ->”Microsoft Entra ID” and Select “set admin”. A Dialog on the right will pop-up where you can paste your Object ID. It should give you only one result: an Enterprise app with the same name as your application. This is your server managed identity.

4.) Set the Checkbox and click on “Select”. “The Admin name” should change to the name of your application

 

 

5.) Save with the “Save” Button:

 

6.) Restart you Container App.

 

Objectstorage in databasesystem (Version 1):

Overy object produces this number of lines, space requirement depends on database system, configuration and content:

  • 1 line in ITEMS

  • 5 lines in ITEMFIELDS

 

Objectstorage in databasesystem (Version 2):

Overy object produces this number of lines, space requirement depends on database system, configuration and content:

  • 1 line in ITEMS

  • 3 lines in ITEMFIELDS