Anyone know of nodes that can assist with Database (MSSQL, MySQL, Oracle, etc) store and forward functionality?
For example it will appear I am storing data to Table1 in Database1 on Server1. If I am unable to store a record in Table1, Database1, Server1 (server down, network down, etc.) I begin storing in Table1, Database1 on Server2. When the first server or issue is resolved I continue to log to Table1, Database1, on Server1 but all data temporarily stored in Table1, Database1, Server2 is "forwarded" to Table1, Database1, Server1.
I am sure with quite a bit of code I can build this functionality. I was hoping there is a known node or set of nodes to accomplish this without much or any script code.
Thanks!
Edit - Please note that this functionality is similar to those found in SCADA and other client based systems.
You will first have to determine which of your server(s) are available, a ping is probably not enough.
You could use an exec node with telnet server port, if it answers, store it as an object in a flow/global variable, if not, test next server. Use the variable as the server.
Thx Bakman2 for your response. I don't think you appreciate the situation. We aren't talking about 1 variable. This could be hundreds or more pieces of data that must be stored as they are in a database. This may include database side functionality which needs to execute to manipulate data, enter timestamps, etc.. This is why there is a need for two distinct Servers with identical databases. In a perfect world when nothing is wrong server2 is basically empty of data. In the worst case, server 2 will have to store and hold days, weeks or even months worth of data. Then, when server1 is back online, backfill the data to server1 as it was entered in server2 temporarily. Then server2 no longer holds any data again.
Classic store and forward as it exists in many SCADA packages and other software solutions..
Bakman, I am looking for a node or nodes that "Store and Forward", including primary and secondary database capabilities not building it from scratch. I think you are still trying to build it from scratch...
Ok. Bakman....If it is so simple. Set up MSSQL server #1 and #2 and provide your simple flow example that stores and forwards between two external databases....
Yes, the store and forward would be handled by Node-red. FYI, this is functionality that should not necessarily be handled by the databases. In this case the databases are not allowed to be connected to each other, but node-red can connect to both databases.
I’m not aware of any existing node or even flow that will do what you want. Your best bet is to build on yourself. You should take under consideration the volume of data and pick a platform that is apropriate (i.e a rpi zero w is probably a bad choice)
This is a peculiar constraint, but if there are policy or firewall issues involved, you might want to see whether Node-RED can be used as a simple pass-through between the two databases, while fooling them into thinking they are communicating directly. I'm no database expert, but MySQL at least (and perhaps the others you mention) has pretty robust features for database replication, failover, etc. that could be configured to deal with your use case. The idea of using Node-RED to do the heavy lifting of failure detection, master/slave assignment, synchronization, etc. seems like a lot of effort for something the databases already know how to do.
What you are asking for isn't simple in any way. Especially if you are looking for something that would work with multiple db server types.
You need to take into account the volume of data that might need to be temporarily stored - what happens if your temp store fills up?
You also need to think about sequencing. If your records need to be written in the right sequence then you must always be using the temp store as the primary for all writes.
In an enterprise environment, this type of issue would typically be dealt with using an Enterprise Service Bus (ESB).
There is nothing as far as I know that is available in Node-RED to support this directly. Certainly it would be possible to build something but I would be far from convinced that Node-RED would be the right tool to do that for anything other than a prototype or very simple requirement set and low throughput.
Well, it seems no one responding is actually familiar with what "Store and Forward" functionality is. This a VERY common feature in SCADA environments.
For those focused on making two databases directly talk to one another. You are obviously not appreciating the cyber security issues as well as the needs of having many concurrent Store and Forward scenarios to independent databases.
Although I appreciate having comments, I would appreciate more comments and responses from those who understand "Store and Forward" as it pertains to SCADA and then replicating the same functionality in node-red.
Given the fact that dedicated software exists to handle these scenarios and no nodes or flows exist to cover your request, you will have to build it yourself. What happens if your node-red instance is down ?
I understand your frustration, but please consider a few things. First, your questions here are seen by some of the most experienced users of Node-RED, as well as the developers. If you don't get a response like "it's been done, and this is how you do it", it is likely that your problem has not been solved before, and you will be breaking new ground. Second, if a number of us suggest, however gently, that Node-RED might not be the right tool for the job, that opinion deserves some respect until you are able to show that it is wrong. Finally, you will rarely, if ever, find anyone here taking that sort of tone when posting. Still, if no offense is meant, none will be taken.
In your title, you mention database and so it isn't surprising that you have received answers related to databases. In the db world store and forward isn't a "common feature". Indeed, I believe that it has been deprecated in SQLserver. As I said, this type of capability is generally implemented via an ESB in enterprise environments.
There are, of course, other ways to achieve the same effect without ESB involvement. The approach is typically to distribute transaction logs between db servers. This ensures that transactions are written in the correct order.
Again, I would point out that getting this kind of capability right is not trivial for the reasons I pointed out before. If you want a SCADA system, I'd suggest using one.
Perhaps you would have received better responses had you mentioned your interest in SCADA systems from the outset rather than appearing to criticise people who are trying to help.
@drmibell I think I have taken you and others out of their comfort zone with this posting. Although there are experienced people with node-red posting here, I was very clear that I required "Store and Forward". Suggestions are appreciated when there is an appreciation for the topic at hand. It would seem that pointing out that some of the responses indicated a lack of knowledge of "Store and Forward" has hit a nerve with some. I don't know what to say other than, please respond on topic.
