Using MySQL with Kepware/FSQL/FPMI and have about 14GB of data. Backups take 24hrs using the MySQL Admin tool, which is way too long. IT suggested I move to MSSQL because they have a backup agent with their backup tool set for MSSQL, so backups will be automatic.
So I need to migrate my existing production system to MSSQL. I want to run through it once so we took a database backup snapshot and imported it into MSSQL, and now I want to add a second project on a new machine to work with the new DB to see what the issues are. I was hoping for a little design help on the migration of my apps:
On the new computer, I won’t install Kepware. I will try to read OPC data from the original computer. We have firewalls in place to isolate the production / office networks, so a second OPC server will require IT to do more stuff which I would like to avoid. I think this is a DCOM issue that I will have to fight through. If I recall, a user/pass in FSQL will help get through this temporary roadblock for my testing. Long term, OPC will reside on the same PC as FSQL, or we will migrate to Ignition. But for now, it’s DCOM pain.
On the new PC, I will install FSQL and then import the backup config from production. But I need to take every tag (3k+) and tell them to use the remote OPC server. Not sure how to do this easily - do not want to remap every tag with all alarms, scaling, etc. I hope there is an easy way.
Configure FSQL to use the new MSSQL database by creating a new datasource (I probably have my terminology wrong). All the tables exist in the new DB, so hoping this goes smoothly. We have a bunch of FSQL triggers and such that interact with the database, but I will have to check each one and verify performance to standard expectation. This is the type of checking I need to do - the rest of the work is really a setup for this type of checking.
Install FPMI on the new PC and import the project. With data available over OPC and in FSQL from the previous two steps, this should be easy. Then verify all SQL code works on the DB - things like click to graph, our recipe system, etc.
Once these steps are done, then I have a roadmap for the real migration. Shut the system down, backup the DB, import into production MSSQL server, fix, the repeat the steps above on the production hardware. Then go live again.
Any comments / suggestions would be appreciated on my approach and any gotchas others have learned would be useful.