Remote Procedure Invocation
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 Direct Database Data Exchange, File Based Data Exchange, and Messaging.
In this article, we’re going to take a look at the 3rd of 4 integration styles which is Remote Procedure Invocation.
Remote Procedure Invocation
Data Exchange by Remote Procedure Invocation actually refers to two patterns of integration into the Sage 500 ERP system. The first pattern is that of the Staging Table/Stored Procedure model, and the second is that of the Web Service model.
They are both examples of remote procedure invocation, but each uses a different technological approach.
The Staging Table/Stored Procedure model is utilized by most of the underlying SQL based APIs (Application Programming Interfaces) in Sage 500, both those provided by Sage and those added by third parties who have followed the Sage’s pattern. In this pattern we populate a staging table, or intermediate table, with the data we wish to process into Sage 500. We then execute a specific stored procedure that will process that staged data and report by via another table or return value the results of the operation. This is a successful pattern that is in widespread use today.
Less common is the approach of providing Web Services (SOAP or WCF in most cases) to wrap the process of pushing data to or pulling data from Sage 500. These can be low-level CRUD operations against a specific table, or more ideally higher level operations to create transactions or query transactions from the ERP system.
- Is it easy to implement? Relatively speaking yes, but with two caveats. The first is that the developer must be familiar with the technical approach (SQL based or web services). Secondly there is still a degree of domain knowledge required to interact with the interfaces. If the web services are designed from a Service Oriented philosophy they will be easier to approach than the staging table/stored procedure approach, but this is not an automatic assumption.
- What tools are available within Sage 500? Sage provides an extensive set of SQL based staging table/stored procedure APIs. Some examples include Sales Orders, Purchase Orders, and Timesheets. They do not provide any web services based interfaces directly.
RKL eSolutions has a rich set of extensions to the underlying SQL based APIs in Sage 500, adding numerous transactions and extending existing transactions with additional features. We also provide a .NET based set of SOAP web services that allow the development of service based integrations into Sage 500.
- Is this a tightly coupled solution? Yes. People often mistake Remote Procedure Invocation as an integration style that decouples applications. They are failing to recognize that a change to the underlying staging table layout, stored procedure, or web service definition immediately breaks their integration.
It is possible to engineer and manage remote procedure based patterns in such a way as to mitigate the inherent coupling of applications, however this requires deliberate choices that are sustained by the development teams over time.
- What are the error handling challenges? The calling application must deal with return error messages from the remote procedure call or stored procedure call. Mechanically this is easy, but from an application logic point of view this can be very challenging as the caller must interpret the returned error messages to determine the disposition of the error. There is no inherent opportunity to retry failed transactions or to offer the ability for the process owner in Sage 500 to review the error (as it is surfaced to the caller, not the target).
- Is it easy to maintain over time? It depends on the manner in which the owner of the staging tables/stored procedures or web services decides to revise the interface over time. Staging tables can be changed in a manner that retains backwards compatibility, and web services can be versioned so that consumers of both have time to transition to the new version without breaking existing integrations.
- Is it scalable? To a degree. The staging table/stored procedure approach offers the ability to batch groups of transactions together and work well for integrations that exchange data on a scheduled, periodic basis. The web services pattern works better for ad hoc/real time integrations but can run into issues with scaling into high volumes of transactions. In both approaches there is nothing inherent in the pattern to buffer the underlying ERP system from peaks in transaction volume. Both approaches are inherently synchronous, meaning the caller must wait for one transaction to fully process before sending the next. Latency in this approach is low as the remote procedure or service is usually interacting directly with Sage 500 tables.
- Is it easy to secure? Yes for the stored procedure based approach as you can leverage all the features of SQL Server for security. No for web services, it is easy to develop insecure services into an ERP system, securing these takes specific domain knowledge and deliberate design and implementation (especially for services exposed across the public internet).
Sage 500 ERP offers a rich set of SQL based APIs that follow the staging table/stored procedure model.
RKL eSolutions further extends these with additional APIs and features, combined with extensive experience in development and integrating with these tools. Additionally, RKL offers a rich set of .NET based web services for integrating or developing browser based applications with Sage 500.
In the final part 4 of the 4-part series, we’ll examine Messaging as another integration style and option.
Sage 500 Integration Styles Part 4
Related Posts
Sage 500 Integration Styles: File Based Data Exchange
Sage 500 Integration Styles: Direct Database Data Exchange
Sage 500 Integration Styles: Messaging