Using SQL Server instead of H2 database

Camunda 7 Topics Discussion & Questions
1

Hi,

I was trying to use SQL Server database instead of H2 database for my Camunda Application. I have executed the create script as described in the document and have also successfully changed the server.xml file. I tried to re-start the tomcat server which also worked fine and I was also able to view the Admin web application successfully but then suddenly because of some issue an error occurred and the connection got aborted. Attached is the Stack trace:

log.txt (39.1 KB)

I figured that I also have to run the following commands for SQL Server as per the documentation-
Command 1 - ALTER DATABASE CAMUNDA_BPM
SET ALLOW_SNAPSHOT_ISOLATION ON

Command 2 - ALTER DATABASE CAMUNDA_BPM
SET READ_COMMITTED_SNAPSHOT ON

Question 1: The first command executed successfully but the second command is taking very long to execute. It has already been 30 minutes and it is still executing. How long can this take?

Question 2: Can my issue be actually because of non-execution of the above stated commands?

I am not sure if this is actually a Camunda question or not but any help or suggestion would be deeply helpful for me.

Thanks in advance.

Regards,
Aakanksha

2

The problem got resolved after the Command 2 got executed leading to another situation wherein I am able to access the SQL Server Camunda Database successfully but unable to use the sqljdbc_auth dll for my Custom Application database with the error - Native Library sqljdbc_auth.dll already loaded in another classloader.
I have added the sqljdbc jar to the $TOMCAT_HOME lib folder and the DLL extension to the $TOMCAT_HOME bin folder and I am using the Camunda Tomcat Distribution for my Custom Application as well.

Could you please help me with where I could be doing it wrong?

3

Any comments on this?

4

I’m not so familiar with SQL server, but why is a dll file required in Tomcat to connect to SQL server? Isn’t the JDBC driver enough?

5

Actually, I am using Integrated authentication and for that sqljdbc_auth.dll seems to be as required.

6

Hm, ok. I’m afraid I am not familiar with it and therefore not of much help. Maybe you can get better assistance by SQL Server experts on how to use this correctly in a Tomcat setup.

7

Alright. Thanks for the response though.

Regards,
Aakanksha

8

Is it possible to specify the Sql Server configuration properties somewhere else other than the Tomcat server.xml file for using Camunda with Sql Server instead of inbuilt h2 database like creating an element like:

Employees Database for HR Applications
jdbc/EmployeeDB
javax.sql.DataSource
Container

in web.xml file?

Also, are there any major differences between the Camunda Apache Tomcat distribution and the Apache Tomcat full distribution? If not, could you please redirect me to the document which could help me to use Camunda with Tomcat full distribution?

9

For a shared engine, it is not possible to configure the engine from an application. The only alternative I can think of is creating the datasource programmatically (not sure if possible and how to do that) and configure the engine via a process engine plugin.

Camunda extends the following Maven artifact:

<dependency>
  <groupId>org.apache.tomcat</groupId>
  <artifactId>tomcat</artifactId>
  <version>${version.tomcat}</version>
  <type>tar.gz</type>
</dependency>

I don’t know if that is the full distribution or not.

Cheers,
Thorben

10

For a shared engine, it is not possible to configure the engine from an application. The only alternative I can think of is creating the datasource programmatically (not sure if possible and how to do that) and configure the engine via a process engine plugin.

What if I use an embedded engine? I am sorry for being confused on this question but need to ask how can I know which process engine is it that I am working on in the pre-packaged distribution.

11

Hi Thorben,

Is there someone in your team who could suggest me a better approach towards this issue.

My use case being:
I have a Camunda Application and a separate Custom Application which calls the process engine and initiates or executes BPMN related tasks using Rest API calls. Initially I continued using the inbuilt h2 database for Camunda and Sql Server database for Custom application and everything worked fine. But now I need to switch to Sql Server database for Camunda as well. And as described in the docs, when I do so I am able to successfully create a connection and use it for Camunda. But the problem arises when I also need to login to my Custom application and I am unable to do so because the sqljdbc_auth.dll(used for integrated authentication) is already in use as per the database configurations specified in the
tomcat->conf->server.xml file.

How should I handle such a situation?
As I understand, web.xml and config.xml files can be used for specifying independent properties of applications and shareable or default properties are specified in server.xml file of tomcat server So shall I create these files for the camunda-engine provided in the Camunda Apache Tomcat distribution? How should the server.xml file be changed then? Is there any other way to avoid such an issue?

Please guide me through this because normally I would have considered this a Tomcat server issue or an SQL Server issue but in the Camunda packaged distribution, I am unable to figure out where and when the database calls are being made and hence am unable to come out with a different approach.

Your support is deeply appreciated.

Regards,
Aakanksha

12

The pre-packaged distribution defines a shared engine. If you want to use an embedded engine, you will have to bootstrap the engine as part of your application and include the Camunda libraries in it. You will then no longer need the pre-packaged distribution and can deploy your application to any Java application server.

I’m sorry, this scenario exceeds my knowledge. I doubt this is a Camunda issue, though. All Camunda needs for integration is the JNDI name of the datasource. How that data source is created and configured is not part of Camunda, so there may be more than one way to do that. However, we are not experts on that.

If there is anyone else who has experience with this, then they will probably post here. Please understand that this forum is a free offering and we can’t analyse this particular problem in that level of detail that it would require. Note that you can get paid assistance from Camunda or a large group of Camunda partners.

13

@AakankshaTej: Maybe this stackoverflow thread can help you.

14

Hi @hawky4s - Thank you for the link. I already did try the stated solutions and am still stuck with the same exception. Please do suggest if you have got any other ideas or links for my reference.

Actually I do understand that database configurations in server.xml file of tomcat server is being used by the process engine for making database connections and I am not sure if that might be leading to this issue or not. So I was looking at alternatives because normally the dll files are shared among the war files in tomcat server but in this case that is not quite happening.

15

Thank you for the insight.

16

How does the deployment of your legacy app looks like, mainly the db configuration of it? Is it loading the database driver by itself instead of relying on a configured datasource in tomcat (server.xml) and acquiring it through JNDI?

17

Yes, my legacy app does not rely on tomcat(server.xml) instead using JDBC connection strings I am establishing a connection wherever required.

18

I have resolved the issue by not using integrated authentication anymore.

Thank you all for your help and suggestions.

19