Stored Procedure Vs Direct Mapping with Table when migrate a Legacy System

Hi ,
We are working on a project related to migrating a legacy system from an old SQL server to the latest version . Along with that , the Factory SQL , Old Ignition transaction groups also we are migrating on a new server.

There is a challenge we face related to this migration is that Legacy system extensively used stored procedures to get the data from the PLC and directly stored in to the legacy system DB. As per the new suggestion given by the PLC /SCADA team , They would prefer to store the row data in a Ignition DB and then suggesting us to take the data from the DB via any ETL tool.

As per the technical team , The suggestion given by the PLC team will increase the workload only because Ignition itself is having a built in mechanism to call the stored procedure directly from Ignition itself. However , the challenge here is , Incase if a failure happens in the stored procedure logic , the data will be lost coming from the PLC… What is the best way to migrate the legacy system from an old version to a new version.

Is Igntition capable for storing the data in a temporary location incase if a failure happens while calling the stored procedure directly from the Ignition ? Please share your thoughts as we are nearing to the project deadline

Thanks in advance


Ignition has a store/forward system to handle things like that, but my preferred method is to use a FIFO in the PLC.

Is there a reason for the stored procedure over a transaction group?

1 Like

Thank you Jordan for the response. There is no specific reason for using the stored procedures. The system was developed around 20 years back and many people worked on it. Mostly , all preferred using the stored procedure because there was no SQL database associated with Ignition to store the raw data. Also , The data is mainly used for the report…

As per the new plan , PLC team is planning to store all the raw data in a separate SQL server and suggesting the application team to take it from there using any ETL tool. The challenge we face is the short project duration and team found very difficult to rewrite all the logic to take the data from the new Ignition DB. Also , The PLC team removed duplicate PLC tags and merged many common tags into single transaction groups.

application team still prefer to call the stored procedure directly from the Ignition itself because as per them its a built in facility with in ignition. But then the question asked by PLC team is , what if the stored procedure fails …how do we fetch the lost data again because PLC flushes out the data in every 5 minutes.

Still we are debating with PLC team to find the best way . What’s the best suggestion as per you / the architects who worked on Ignition for so many years ? Please sugegst

THanks in advance

How did the old system prevent data loss? If it was FactorySQL running the stored procedures, then modern Ignition would have the same data loss behavior, and you are at status quo ante.

If you abandon the stored procedures in favor of regular transaction groups (INSERTs), then modern Ignition’s Store and Forward system will handle short database outages for you.

If your scope requires you to improve/reduce data loss while still using the stored procedures, then you will have to use the scripted stored procedure tools in Ignition and hold the data in your own (scripted) in-Ignition cache for retries.

If you really care about not losing any production data, then you must hold it in the PLC (like a FIFO per Jordan’s commen) until Ignition can report that the record is saved in the DB. This is most reliably done with scripts and echoed handshake triggers.

1 Like

Thanks for the suggestion [pturmel . Old FSQL , Ignition architecture has frequent data loss issues. This was one of the points highlighted by the PLC team

Sounds like you have some scripts to write. And your PLC team has a FIFO to implement. If the machine is cyclic, it should stop when the FIFO is full.

This recent topic might be helpful:

I have similar advice scattered through the forum archives.

1 Like

Thanks pturmel. We will verify and update .