Linked Server vs. Database Connection for connecting to different server.


We are trying to layer our architecture.  We want to put outsystems on a single server, and use it to connect to our databases that we house on other servers.  More importantly, we have chosen to interact with our databases primarily through stored procedures.  To accomplish this, I set up a database connection in Service center connecting to our DEVSERVER, then I import entities into an extension, and then make a call like this one in an advanced query.  In my case, we are using the Person entity.

/* %LogicalDatabase%=GetLogicalDatabase({Person}) */
EXEC OSTest.dbo.GetData 'Person' 

This is all working well. I am just wondering if the end result would be the same 
as if we had made this call in the advanced query using a linked server set up in 
the sql server instance where the outsystems database resides.

EXEC DEVSERVER.OSTest.dbo.GetData 'Person'
Hello Adam

In fact it is not the same.

OutSystems Database Connections features creates direct TCP/IP connections to the remote database servers, and using it's own transactions, executes queries over the remote data model, while when using a LInked Server from the OutSystems Main database, a TCP/IP connection is established to the main database, and then through the linked server form the main database to the remote database server. Further more, it will share the transaction between the main database and the remote database server, which in some situations is not desirable due to performance issues.

My recommendation is to use the database connections instead of linked servers for better performance.

Regarding the stored procedures, I understand the use case and the business requirements for such approach, but do keep in mind that the Advanced Query node was not designed to run stored procedures, but queries instead (SELECT, INSERT, UPDATE, DELETE), and for that not all stored procedures may execute correctly within an Advacned Query. If the stored procedure output is the same as a query output (SELECT) with supported datatypes, it shouldn't be a problem.


Miguel Simões João