Enterprise IT Consultant Views on Technologies and Trends

Dec 5 2011   1:46AM GMT

Integration using Direct Data Access

Sasirekha R Profile: Sasirekha R

Integration using Direct Data Access

Data Integration is where the applications integrate at the logical data layer by allowing the data in one application (the source) to be accessed by other applications (targets).

In Data Integration, Direct Data Access involves directly accessing the source database using SQLs from the target application. Direct Data Access is to be used very sparingly – as it doesn’t provide isolation and even when there is a minor change in the source data structure, all the target application programs having such direct access have to be modified.

Direct Data Access may have to be used ONLY in certain cases where the alternatives are difficult to use. Direct Data Access is an option to be considered, say in cases like the source application has:

  • Tightly coupled business and presentation logic making business logic not accessible externally (or)
  • Business logic implemented in a specific way without support for remote access (This is likely to be true for legacy applications – that rely on technologies that are more than decades old ).
  • Doesn’t provide any API or web services to access its data


Direct Data Access Patterns

Shared Database

Distributed Database

All applications read data directly from the same database

Maintain copies of the application’s database so that other applications can read the data (and potentially update it).

ß         No Isolation – Applications need to use a common schema

ß         No Isolation – similar to Shared Database

ß         If applications do not use a common schema, the individual programs have to take care of data transformation

ß         If no common schema, data transformation has to be taken care of – similar to Shared Database

Ý         No Latency

§  Medium Latency – as the copy accesses may not be current (Latency depends upon the replication and synchronization frequency)

ß         Database may become a performance bottleneck and can impact QoS

Ý         Solves Shared database performance and network latency issue

ß         For geographically dispersed applications, may cause excessive network latency

ß         Distributed databases bring in the complexity of synchronization and replication logic (to be handled at DBMS level).

ß         May violate security constraints implemented at Business logic

§  May violate security constraints implemented at Business logic – with lesser impact due to latency

§  Efficient as there is no overhead – in terms of application logic – which accessing the data directly

Ý         Efficient as there is no overhead – similar to Shared database – with the added benefit of lesser load in the copy accessed.

ß         Not to be used for Updates – as it would be risky and imply lack of data ownership.

§  Updates can be considered – though the problem of lack of data ownership would remain

ß         Updates may corrupt the database as it bypasses validation. Could have adverse impact on the source application.

§  Problem similar to Shared database – but impact on application can be reduced as there is latency (Before updating the source copy, the data validations and checks can be created to avoid possibility of corruption.)

Ý         No Conflicts – Concurrency and transactional integrity is handled by the DBMS itself.

ß         Complex conflict resolution mechanism required involving multiple versions.

Ý         Least Complex to Build

ß         Very Complex to build

ß         High Risk

§  Medium Risk

Direct Data Access – Recommendation

§  Use ONLY where other alternatives are not possible (or) and if it is an interim short-term solution

ß         Avoid – Too complex, expensive & less benefits

§  Consider when both the source and target application are under the control of the same team (Before updating the source copy, the data validations and checks can be created to avoid possibility of corruption).

ß         Needs detailed Infrastructure level planning.


If Direct Data Access is the only option, then also avoid hard-coding SQLs accessing other application databases in each of your application programs. Create a separate program with appropriate naming convention and reuse the program (as a function call) in multiple programs. This approach can provide certain level of isolation as the integration program can handle the impact of any changes in the source and still appear the same to all the calling programs.

 Comment on this Post

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

Share this item with your network: