Sage 500 Integration Styles – Part 2

Direct Database Data Exchange

This post is part of a 4-article series covering integration styles for Sage 500 ERP (formerly MAS 500).  The purpose is to introduce the subject of Integration Architecture, provide an overview of common approaches found today, and discuss how each can be achieved with Sage 500.

If you haven’t read part 1, we suggest you take a quick peek as it provides an overview of the “Big 4 Integration Styles” at a high level. Other posts in this series cover File Based Data Exchange, Remote Procedure Invocation, and Messaging.

In this article, we’re going to take a look at the 2^nd of 4 integration styles which is Direct Database Data Exchange.

Direct Database Data Exchange

Direct Database Data Exchange refers to one of the most common styles of integrating to Sage 500 (and indeed many small and mid-sized ERP solutions). This is a derivative of Fowler’s Shared Database style that more closely resembles how we integrate at the database level in practice.

In this integration style we are connecting directly to the ERP system’s database. Here we may perform direct CRUD (Create, Read, Update, Delete) operations against the Sage 500 tables, or possibly through data views.

• Is it easy to implement? No, this approach is largely limited to experienced database developers. Also, it does require specific domain knowledge of the target ERP system (Sage 500 in this case) in order to accomplish successfully. Unlike the Shared Database pattern, where systems interact through externally defined tables in an intermediate database, in this pattern we are interacting directly with the Sage 500 schema so we must fundamentally understand the underlying data and business rules.

• What tools are available within Sage 500? In prior versions of Sage 500 there used to be available as part of the SDK a Schema Browser product that exposed a detailed ERD and table listing. In more contemporary versions this has been supplanted by less detailed help and mapping files within the product itself.

• Is this a tightly coupled solution? Yes. This style of integration is highly coupled to the underlying database objects, data, and business rules of Sage 500. If an entity or rule in the database changes it can easily break the integration.

• What are the error handling challenges? In the case of direct CRUD operations against the underlying Sage 500 tables, the error handling is limited to those messages you receive from SQL Server for violations of constraints and rules. However, the larger issue is that it is possibly to easily inject data that violates externally enforced (through the Sage 500 application code) business rules where the effects of bad data surface as user experienced error messages.

• Is it easy to maintain over time? No, directly interacting with the underlying Sage 500 schema requires a certain level of maintenance overhead because the developer must track changes to the underlying schema and rules.

• Is it scalable? To a degree. In larger scale Sage 500 implementations this style of integration can run into issues with table and row locking, and experiencing or causing blocking of other operations.

Additionally the developer must be aware of the virtualized locking scheme used by Sage for certain entities and processes. Additionally there is no way to meter or throttle access to the database, so the integration may increase the workload on the database server at inopportune times (during large posting or shipment commit operations for example).

This integration style does offer the lowest latency of any of the integration approaches, as data is read and written in real time against the actual Sage 500 database schema.

• Is it easy to secure? Relatively, within the scope of SQL Server’s security model. However we often see less than ideal practices here, such as using high-access users as the user for integration processes (hint: sa and Admin are not the ideal users for database integrations).

Sage 500 ERP offers an open, and well documented, database schema that allows developers to directly interact with the system’s tables, views, and other database objects.

However, this approach exposes a number of challenges - key among them being the responsibility of the developer to understand and observe the underlying data rules and business rules.

In part 3 of the 4-part series, we'll examine Remote Procedure Invocation as another integration style and option.

Sage 500 Integration Styles Part 3

Related Posts

Sage 500 Integration Styles: File Based Data Exchange
Sage 500 Integration Styles: Remote Procedure Invocation
Sage 500 Integration Styles: Messaging