The first concern must always be on security. Integration points can also become a target for attacks, and must be hardened at least as much as the underlying systems. Best practices include the use of application and network firewalls and IDS/IPS, enterprise single sign-on systems, and strong auditing systems.
Another key decision to be made is real-time access to data vs. scheduled, batch updates. Batch updates tend to be somewhat easier to implement and also have less of a performance impact on the runtime of your application, but sacrifice the freshness of the data. However, because batch jobs tend to have more time to run, they can be used in systems where data cleanliness is an issue. Consider whether your application requires most up-to-date information and real-time instant updates, or whether a daily or other periodic refresh is sufficient.
Real-time data connections
Integrating data in real time can have a huge impact on an application's usefulness. Such real-time access can take many forms:
- Direct feeds to and from an application's database
- Calls to a system's APIs
- Web services, CORBA, or other remote-procedure calls
- Real-time screen scraping
Perhaps the most useful technique for real-time system integration is to use an Enterprise Service Bus, or ESB. An ESB is a system (and an architecture) that provides access to multiple data sources through a messaging-based, publish/subscribe paradigm. The term "Bus" is ESB alludes to its position in an enterprise's data flow, as it serves a similar role to a CPUs bus, which passes bits of information between separate subsystems.
To think of an ESB, it helps to imagine how systems are integrated without one. When tying together three systems (A, B and C), you would typically have to write 6 separate data exchanges: A->B, A->C, B->A, B->C, C->A and C->B
The problem with this is that as additional systems are added to the loop, the number of connectors increases geometrically. Adding a fourth system to the image above would require 24 separate connectors.
In an architecture with an Enterprise Service Bus, the number of connections decrease dramatically. Each system connects only to the ESB, and "publishes" its data in its native format. It then "subscribes" to messages it's interested in, and the ESB takes care of message routing, delivery, and translation.
Batch upload, ETL, and SSIS
Most batch upload processes have some sort of ETL (extract, transform, load) interface. Fundamentally, an ETL process takes data from System A, converts it into a format that System B can , and then loads it into System B. Although such process can be built with custom code, there are tools designed specifically for these interfaces.
For example, SQL Server Integration Studio (SSIS) packages are perfect tools for ETL tasks. The development tools, reusability, and control over execution are superior to home-grown methods. Based on supplied specs, either developers or DBAs can build the packages. SSIS has the following advantages:
- It scales well
- It has a graphical UI and thus requires very little coding
- Where it does need to integrate with custom code, it is seamless with ASP.NET
- It has a built in ADO.NET connector for connecting to mainframe
- Many SQL server licenses also come with a license for SSIS
- It has capabilities outside simple ETL
- Standardization - any developer who knows SSIS will be able to understand the SSIS package, whereas Horace Mann's custom ADO.NET solution requires someone to know its inner workings
- Performance wise, the Data Flow tasks are a major improvement over the old SQL Server 7.0/2000 DTS packages. The Data Flow task has been rebuilt as its own module in SSIS, primarily for performance reasons.
- SSIS packages are can be scheduled or programmatically executed via jobs or command line
- The use of connection manager makes it easy to move between environments
No matter what software package you choose, it is recommended to send the involved developers to training, as self-training or learn-by-doing often results in a sub-standard implementation. For example, SSIS has a steeper learning curve compared to its ETL predecessor, DTS.
Want to learn more?
Find out how Acsys can help you get the most out of technology.
Set-up a free 30-minute tech assessment now.
Thank you for considering Acsys.