Hi Guys,
I want to run a configuration by you to see what you think.
We have event data that we need to log to SQL, event because it is trigger by the PLC. Customer will like to have a backup system in case of a failure. So we have 3 computers in our config
Computer 1 - Primary FSQL and Kepware
Computer 2 - SQL Server and Web Server
Computer 3 - Backup FSQL, Kepware, SQL and Web Server.
We have a Heartbeat group between FSQLs, Databases and PLCs so that we know that they are up and running.
For every Event we have two triggers one for the Primary and one for Secondary and only one is set at a time. The Primary triggers are set when the primary server is running, triggering the groups on the Factory SQL which will then excute a stored procedure to insert the data to the Primary database and secondary database(from with in the Primary database stored procedure). If there is a failure and we loose the hearbeat to the Primary database then the triggers for the secondary will be the one set making the Backup FSQL start storing the information to the backup SQL. If the Primary database goes back up after the Secondary went active it requires user intervention to swap the Primary back to its role.
Then what I am planing to do develop a gui where the user will trigger a replication to push the Secondary data to the Primary and activate the Primary to it role.
Do you guys see an issue with this ? I hope I was able to explain it
Thanks
Hi-
That sounds perfectly fine. You’re basically taking care of redundancy yourself in the PLC, as opposed to using FactorySQL redundancy. Currently fsql redundancy does not have a way to communicate state through the PLC, so if you already have everything set up, I’d say go for it. The only down side is keeping the projects synchronized- you have 2 very similar but different projects (one using the primary trigger, the other the secondary), so I’ll have to keep that in order. If your project doesn’t change much it shouldn’t be a big (if you DO expect it to change, you could also probably use CSV export and a script to generate the secondary project).
Regards,
Hi Colby,
The only thing I am trying to sort out the is the replication part.
Thanks for your respond, I trying to shared my ideas with someone to make sure I did not miss anything. I love this forum and you guys quick respond and to shared ideas, again thanks for your help and support.
Julio Delgado
Colby,
I have a couple of questions:
If I used the built in redundancy of FSQL. How does it switch back to the primary ? Automatically once it sees the primary up an running.
In my situation I plan to have real time data storage (no caching). So I will have replication setup between the Primary and Secundary, where I am struggling is when we start logging data from secondary or backup server how do we take that data back to the primary. On the replication configuration my primary server is my publisher and my backup is my subscriber. I know this is not a FSQL question but I wondering what you guys seen from your other user how they “attact” this scenario when they need to push back to the primary database the data collected from the backup.
Thanks
Julio D
Like you said, this isn’t really an FSQL issue, so ultimately you’ll be in the same spot if you use FactorySQL’s redundancy or your own setup through the PLC.
In some ways, it may be easier to use your PLC method with 2 active FactorySQLs, mainly because you can have your secondary FactorySQL write to a different table, which might make things easier. That is, imagine your primary history table is called “plant_history”. Now, imagine that your secondary factorysql writes to an identical table called “plant_history_secondary” on the secondary machine. When the primary goes down, the secondary system logs. When the primary comes back up, synchronizing could be as simple as running a query on the primary like:
“INSERT INTO plant_history SELECT * FROM plant_history_secondary” (that’s mysql syntax, may not work on sql server, and may need to be a tad more complicated, but it’s to illustrate a point).
The data would be inserted in the table, replicated to the secondary, and everything would be sync’d. You could then delete the data in the “plant_history_secondary” table to get ready for the next down event.
OTHERWISE… you might be looking at a circular replication setup or clustering, both of which will be a bit more difficult/error prone.
Maybe some other people (cough kyle, nathan) might have some ideas on this…
Regards,
Thanks colby, we have similar thinking because that is one option I was experimenting.
Colby,
I made one more change to the designed that is instead of creating the additional tables I just created a table to hold the time the backup server goes active and added an additional fields to the tables to hold the timestamp from when the data was inserted and then I use this field to filter the fields I want to send back to the primary server.
I was thinking that might cause problems if the primary tries to then replicate the data BACK to the secondary, but if not- great!
Regards,