Best way to design a rest service with databases connections

[Anonymous]

Hi.

I have a REST web service where clients are asking for resources every time. For each resource , I have several connections to an azure database. I would like to know which is the best way to handle the connections (create, commit and close).

For example, this is my job: 

Should I create a SQL connection with tMSSqlConnection, use it for all the  components of the same resource and close it with tMSSqlCommit at the end ? If I had to do this, this means that I should create a tMSSqlConnection for each resource.

This is probably not the best option.

Should I create a tMSSqlConnection, us it for all the components and select the option AutoCommit?

This is what I had but I realized that I had some problems with the connections.

Or I create a tMSSqlConnection, use it for all  components without the "AutoCommit" function and I close it manually with tMSSqlCommit at the end of each resource.

This is what I wanted to try now but I prefer asking here because to change everything I need a lot of time and I want to be sure.

Thanks! 

[Jesperrekuh]

Oh boy/girl... it sounds like building a MSSQL loadbalancing/service job ...
Im not familar with it but take a closer look at : SQL Server Service Broker

If I was in need of a REST service I would build it in NodeJS . This could be load balanced pretty easy.
I would never ever ever build it in Talend... its possible but as far as I can tell just a nice feature of some components. From an architectural point of view... separation of concerns

You also need different strategies, simple insert transactions versus complex query based transaction.
The complex query transactions should be handled carefully and be setup based on your DB config and a specific user with a max of queries, some sort of queuing logic should be in place first. I assume the service broker is designed to do this.

Good luck!

[Anonymous]

Hello,

--

Or I create a tMSSqlConnection, use it for all  components without the "AutoCommit" function and I close it manually with tMSSqlCommit at the end of each resource.

Reffering to the third solution, for me the best is to use a shared connexion, and commit the transaction at the end of your subjob or job (depending on the requirements).

tMSSQLConnection.PNG

[Anonymous]

I am still lost on the solution but ... I think that I will use the first option. Creating a database conection for each resource and close it at the end with tMSSqlCommit.

If someone have a better idea, I would appreciate the help. I have to develop this with TOS.

[Jesperrekuh]

Yep go for a controlled commit environment if no other options are available...

For the sake of argument ... sorry to say ... but talend is designed to handle data processes its not a webservice handler neither a db resource manager. Who ever is demanding you to do so...
And if its a demanding webservice its doomed for failure.