Is the database a good decoupling point for the system?
We have two systems in which system A sends data to system B. The requirement is that each system can work independently of the other, and neither will explode if the other does not work. The question is, what is the best way for System A to communicate with System B by fulfilling the decoupling requirement.
System B is currently running a process that checks the data in the db table and processes any new rows that have been inserted.
One proposed project - for system A, simply insert the data into table b bb of the system and system B processes the new rows over the existing process. The question is, does this solution satisfy the requirement of decoupling the two systems? Is the database considered part of System B that could become unavailable and crash System A?
Another solution is for system A to put the data in an MQ queue and have a process that will read from MQ and then insert into the system B's database. But is this extra overhead? Ultimately the MQ is more fault tolerant than the db table?
source to share
Generally speaking, database sharing is tightly coupled and not preferred, except perhaps for speed purposes. Not only for accessibility purposes, but also because Systems A and B will be modified and updated at multiple points in the future and should have minimal dependencies on each other - messaging is an obvious dependency, whereas shared databases tend to bite you (or your heirs) in the background when they are least expected. If you go down the path of sharing a database, at least make the sharing interface explicit with dedicated tables or views.
There are four general levels of integration:
- Sharing a database
- File sharing
- Remote Procedure Call
- Messaging
which can be used and combined in different situations with different availability and maintainability. You have a great overview on the Enterprise Integration Patterns website .
As with any central integration infrastructure, MQ should be hosted in a highly available, fully rolled back and promoted environment. There are other queuing solutions that allow you to distribute the coordination of queues.
source to share
Use queues for communication. Do not transfer data from system A to system B via the database. You are using the database as a giant, expensive and complex message queue.
Use a message queue as a message queue.
This is not "extra" overhead. This is the best way to decouple systems. It's called Service Oriented Architecture (SOA) and messaging is absolutely critical to design.
An MQ queue is much simpler than a DB table.
Don't compare "fault tolerance" because the RDBMS uses huge (almost unimaginable) overhead to achieve a reasonable level of confidence in the correctness of your transaction. Locking. Buffering. Write queues. Storage management. Etc.
A robust implementation of Message Queuing uses some kind of backup store to preserve the state of the queue. The overhead is much less than an RDBMS. The performance is much better. And it's much, much easier to interact with.
source to share
In SQL Server, I would do it with an SSIS package or job (depending on the number of records and the complexity of what I was moving). Other databases also have ETL solutions. I like the ETL solution because I can keep logs of what was changed and what errors were handled, I can send records that for some reason won't go to the other system (data structures rarely match between two databases) before holding the table without killing the rest of the process. I can also make changes to the data as it flows to correct for differences in the database (such as lookup table values, for example completed status in db1 is 5 and 7 is in db2, or say db2 has a required field which db1 doesn't, and you need to add a default to the filed one if it's null).If one or the other server is down, work done with the SSIS package will fail and none of the systems will be affected, so it will not split the databases as there will be no triggering or replication.
source to share