File transfer

• Secure: File transfer can be easily locked down, making it a relatively secure means to exchange data. FTP and SSH are both solid protocols meaning remote exchange over the internet is secure.

• Difficult to audit: Compared with other mechanisms files typically don’t have any natural means to audit or to trace audit.
• Remote servers require an FTP/SSH solution to exchange files, adding another chink in the chain of events.
• Fragile: Easy to get out of sync where a failure can require significant manual intervention to re-establish normal operation.
• CSV and other column based formats such as fixed width text and Excel suffer from brittleness, where file schema modifications (field additions and deletions) require significant re-configuration on the receiving end.

• One transaction per file: It’s much easier to identify a particular transaction, and re-processing is easy should something go awry.
• For CSV and other column based formats, always add new fields to the end of the file; never remove existing ones, better still don’t use these formats!
• Use a resilient API based data format such as Xml or JSON. Do not go overboard however.
• Try to avoid Xml schema where possible. We feel schemas add unnecessary constraints to the format, where a substantial amount of the inherent flexibility of the format is lost. They add a level of complexity not required in the SME space.
• Always use a recognised API/library when consuming and producing Xml/JSON data. This will at minimum ensure the format is standard compliant. We’ve seen several instances where organisations (that should know better) provide us with improperly escaped Xml, simply because they weren’t using an API to produce the Xml.
• After files are processed move them to an archive directory; don’t delete.

Database & staging tables

• SQL: Provides vendor and format independence to query and update a database and table schema(s) changes are usually non-breaking.
• Audit and Traceability: SQL/Databases provide an easy means to record and query any key data required for auditing purposes e.g. timestamps, error & warning messages.
• Durability and Recoverability: Databases are naturally durable and far less vulnerable to corruption. In the event of a failure recovery can be made quickly assuming a backup is available.

• Security: Where interchange take place over the internet the database needs to be open to the outside world leading to potential attacks. These can be mitigated by restricting the connecting IP addresses, use of complex passwords, amongst others.

• Always have the following fields in a staging table:
  
  • Status: Used to record whether a record is pending or processed. Always include an error status which allows you to identify erroneous transactions quickly. It is recommended that an index is setup on the status field to help speed querying.
  • Last Modified Date: A timestamp to record when the last update was made.
  • Last Update By: Used to record which application made the last update.
  • Reason/error/message: Where there’s an error or a warning generated as part of the integration or synchronisation it should be recorded against the original record. This then gives you a robust audit trail which can be used later to trace problems.
  • Foreign Record Id: Where a recipient application generates a transaction or record id (such as an Order, Invoice, or even Customer number) update the staging table with this reference.
• Backup frequently: Depending on the volume of data or the location of the database i.e. for high volume sites or where the database is publicly accessible this may be several times a day.
• Always update the staging table immediately after writing to the destination application.
• Where processing takes a long time, consider setting the status field to an intermediate value to prohibit other integration processes from processing the same data.

Message queue

• Windows Azure Service Bus
• Amazon SQS
• Storm MQ: Currrently in-beta this ‘free’ service looks promising.

• FIFO: Messages are guaranteed to be delivered in order.
• Robust and Transactional: Message queues can be backed up, and are naturally transactional.

• Message Queues are a Transport: The data format of the message still needs to be agreed between sender and recipient.

• Use a flexible data format such as Xml.
• File and database recovery is well known, message queues less so. Understand how to administer your message queue technology and how to recover on the event of a failure.
• Prevent message build up by having an application which is constantly listening for incoming messages.
• Use different queues for each data flow; don’t try to multiplex multiple message types onto a single queue.

No interchange

• Each application has its own interface i.e. webservice or a COM/Java/.Net API.
• The data usually requires some transformation for it to ‘fit’ with the other.

Summary