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