Indirect Data Access Strategy

Business sensitive data often have to remain in a consistent state. This is commonly achieved by having only one enterprise system accessing a database directly to update records. Other applications are restricted from performing a direct update operation. These applications simply use an intermediate placeholder to file required data changes.

Scope

Imagine a scenario where a Java EE e-commerce engine acts as the primary owner of the enterprise data that includes customer and product related information. The e-commerce engine executes the majority of the CRUD operations using optimistic or pessimistic locking, depending on the operation. Additionally, a .NET content management system has to occasionally modify the product-related information such as catalog items or promotional prices with the least disruption to the e-commerce engine. The content management application also has to be notified when the inventory falls low on certain promotional items.

Solution

The Indirect Data Access strategy defines a mechanism to exchange information with the Resource tier without directly connecting to the back-end systemand therefore without impacting the underlying data integrity. There are a couple design techniques you can use to implement this strategy. One way to implement the Indirect Data Access strategy is by using an intermediate file; another way is by creating an intermediate database table. With an intermediate file approach, a .NET content management system can store updates into a file rather than directly modifying the database. A Java EE application, which acts as the primary owner of the data, is authorized to directly access the data source to perform necessary updates. Figure 10-2 depicts an import file that stores changes required by the .NET application. The Java EE application would have a cron job to process the content of the import file.

Figure 10-2. Indirect data access

Aside from submitting updates to the database, the .NET content management system may have to receive notifications from the database. A product catalog may have to be updated if certain items don't sell as expected. Figure 10-2 also shows a possible notification technique that uses an export file. The database writes notifications to the export file. The .NET content management system can then poll this file to process incoming notifications or set a file system watcher to detect changes in the file. Commercial database management systems, including Oracle and Microsoft SQL Server, as well as various legacy applications, offer notification services. These services can be used instead of a custom solution. The SQL Server Notification Service [SQLServer_Notification] is designed to dispatch notification events to registered applications. Most of the commercial database and legacy applications are also capable of sending notifications via the SMTP protocol, which eliminates the need for the Export File. In the case of SQL Server HTTP-based notifications are included, so there can be a more direct and immediate methodology. This can be overridden to produce an HTTP POST or a Web service call with the notification, see [SQLServerNotificationFormatting].

Benefits and Limitations

Using an intermediate file/table for asynchronous query processing is a fairly simple solution, straightforward to implement, and is commonly used across the industry. In cases where there is an application that needs to make changes to the database and these changes are not time critical, this strategy fits very well. This strategy, however, will not scale if there are multiple distributed systems that require CRUD queries to be submitted asynchronously as part of a long-running business transaction. Maintaining and updating multiple cron jobs that process intermediate files/tables would be quite involved and time-consuming. Additionally, addressing security and transactional constraints across a multitude of systems can quickly become error prone and a difficult task to maintain.

Related Patterns

There are a number of patterns that can be applied to this strategy. Martin Fowler's File Transfer and Message Broker patterns, [EIP], are among a few relevant ones.

Example

For details on how to integrate Java applications with the SQL Server Notification Server, please refer to [Java_SQLNotifications].