DAO/Webservice Consumption in Web Application

Question

I am currently working on converting a "legacy" web-based (Coldfusion) application from single data source (MSSQL database) to multi-tier OOP.

In my current system there is a read/write database with all the usual stuff and additional "read-only" databases that are exported daily/hourly from an Enterprise Resource Planning (ERP) system by SSIS jobs with business product/item and manufacturing/SCM planning data.

The reason I have the opportunity and need to convert to multi-tier OOP is a newer more modern ERP system is being implemented business wide that will be a complete replacement.

This newer ERP system offers several interfaces for third party applications like mine, from direct SQL access to either a dotNet web-service or a SOAP-like web-service.

I have found several suitable frameworks I would be happy to use (Coldspring, FW/1) but I am not sure what design patterns apply to my data access object/component and how to manage the connection/session tokens, with this background, my question has the following three parts:

1. Firstly I have concerns with moving from the relative safety of a SSIS job that protects me from downtime and speed of the ERP system to directly connecting with one of the web services which I note seem significantly slower than I expected (simple/small requests often take up to a whole second). Are there any design patterns I can investigate/use to cache/protect my data tier?

2. It is my understanding data access objects (the component that connects directly with the web services and convert them into the data types I can then work with in my Domain Objects) should be singletons (and will act as an Adapter/Facade), am I correct?

3. As part of the data access object I have to setup a connection by username/password (I could set up multiple users and/or connect multiple times with this) which responds with a session token that needs to be provided on every subsequent request.

   Do I do this once and share it across the whole application, do I setup a new "connection" for every user of my application and keep the token in their session scope (might quickly hit licensing limits), do I set the "connection" up per page request, or is there a design pattern I am missing that can manage multiple "connections" where a requests/access uses the first free "connection"?

   It is worth noting if the ERP system dies I will need to reset/invalidate all the connections and start from scratch, and depending on which web-service I use might need manually close the "connection/session"

Answer 1

The options you've provided lead me to think that continuing to use the SSIS jobs to import data into a database you control for your application looks to be the best option for both managing the data flow and performance for the end-user.

If you've got requirements that need more up to date data than that supplied via SSIS then I would go for using the web services at that point for those specific parts of the application.

There is nothing in the DAO pattern that requires the implementation to be a singleton, you'll have a much easier time testing the DAO classes if they are implemented as normal classes and then only use a single instances as a dependency when in production.

Finally as session maintenance is required i would use short lived instances given you need to maintain per-request and if connection/resource limits are required to be managed a resource pool pattern implementation could be used to manage checking out/in access to instances that maintain the session tokens (eg get a new token on check out, release token on check in, ensure use is short lived). There should be a number of existing resource pool implementations available, use one as there are many gotchas to implementing that type of pattern.