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:
https://www.oracle.com/de/database/technologies/appdev/jdbc-downloads.html
see loader.path in Reference of application-wide parameters for loading external libraries
MSSQL
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
<repoName>.metaservice.databasev2.driver=com.microsoft.sqlserver.jdbc.SQLServerDriver <repoName>.metaservice.databasev2.url=jdbc:sqlserver://localhost:1433;databaseName=CONTENTSERVER;sendStringParametersAsUnicode=false <repoName>.metaservice.databasev2.transactionIsolation=TRANSACTION_READ_UNCOMMITTED
Depending on your configuration and version of MS SQL Server you need additional properties in the connection string:
encrypt=true;trustServerCertificate=true;
<repoName>.metaservice.databasev2.driver=com.microsoft.sqlserver.jdbc.SQLServerDriver <repoName>.metaservice.databasev2.url=jdbc:sqlserver://localhost:1433;databaseName=CONTENTSERVER;encrypt=true;trustServerCertificate=true;sendStringParametersAsUnicode=false
Azure SQL Server
<repoName>.metaservice.databasev2.driver=com.microsoft.sqlserver.jdbc.SQLServerDriver <repoName>.metaservice.databasev2.url=jdbc:sqlserver://<servername>.database.windows.net:1433;database=<dbname>;user=<sqladminuser>@<servername>;password=<sqladminpassword>;encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30;
<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)
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
Database-URL for the repsitory.cfg
<repoName>.metaservice.databasev2.driver=com.microsoft.sqlserver.jdbc.SQLServerDriver <repoName>.metaservice.databasev2.url=jdbc:sqlserver://<servername>.database.windows.net:1433;database=<dbname>;authentication=ActiveDirectoryMSI;encrypt=true;
First: Prepare your MSSQL-Database in the Portal to allow managed Identity:
In case you are using a user assigned managed identity, add it on the same page accordingly.
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
Log into a SQL-Console with the existing Database-Administrator and create a new User for the specific database/table. (See: https://learn.microsoft.com/en-us/samples/azure-samples/azure-sql-db-who-am-i/azure-sql-db-passwordless-connections/ )
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”
alter role db_datareader add member [<app-service-name>]; alter role db_datawriter add member [<app-service-name>];
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)
For production environments, we recommend to add specific user to your database with the roles above.
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.
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
Don’t get confused: The App ID shown is another than selected: In Azure, Objects can have multiple identifier: a buisness identifier and a object identifier.
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