Sep 24, 2012

Query Service or Synchronized Data?

- When linking two systems, with one system providing data to another system, the providing system's physical environment must be sized for the capacity of the requesting system and the reliability of the requesting system. Another way of saying this is that the providing system must meet or exceed the quality of service or SLA of the requesting system.

- In the example I was reviewing, the HR system was the base for the desired information and was sized for the user capacity of the HR department and the reliability impact of an outage of the HR department. If it is to be used as a real-time providing system for Department B, it's capacity must be increased from the HR department (10 users) to the capacity of the Department B user base, the main business area (3,000 users). It must also have it's redundancy increased to provide no outages.

- Alternatively, when there is a mismatch between capacity and reliability of the providing system and requesting system, the data may be effectively de-normalized by building a copy or synchronization mechanism between the systems – the providing system sending a copy of the needed data set into the requesting system for local use. This may also be appropriate if the systems operate in different security domains, in different networks or network segments where bridging is a problem, or where the systems are separate by geographic areas where network performance [speed or capacity] are an issue. It's also frequently required where a packaged application is the requesting system, as most packaged applications will only query the data in their expected local format and cannot be redirected to a web service or other remote query.

- One other concern in such integrations is the lifecycle of the information. Is it static, reference information, infrequently updated, frequently updated or transactional or computed? Frequently updated information has dangers of synchronization problems or being out of date, and transactional or computed items can only be queried from their source system.

We generally hear that de-normalizing, whether in the database itself or across systems, is a crime (across systems would make it an integration crime?)  Yet the circumstances above can make it preferred or even necessary.  This is not a bad thing when done for the right reasons. and in a reasonably reliable way.  (Noting that every synchronization operation should have a full re-sync option that should be run periodically [monthly or yearly or as appropriate].)