Migrate Project to new DB

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:

OPC
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.

FSQL
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.

FPMI
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.

Hi,

Seems like you have the right idea on most points. Here’s my take:

1. OPC - Don’t underestimate the difficulty of connecting remotely. DCOM config can be tricky, but there are all sorts of guides available. DCOM uses TCP port 135 (for firewall purposes).

2. FactorySQL - You can search and replace in stopped groups from the groups’ right click menu. So, stop all of your groups, then select everything in the root, right-click, and select “search and replace”. You can specify only the OPC Server field. Search for the previous server value (note it ahead of time, it will either be “KEPServ…” or “localhost:KEPServ…”. Replace it with the same value but with the remote name/ip address, ie “192.168.1.5:KEPServer…”.

With OPC working and the database switched over, you shouldn’t have any problem on the FSQL side. It will navigate the differences between MySQL and MS SQL.

3. FactoryPMI - Will possibly be the biggest problem. We’ve found that even without noticing it, it’s easy to use many database-specific SQL feature in your queries, and these will cause errors. Things like “now()” instead of “current_timestamp”, using “LIMIT” where MS SQL requires “TOP”, etc. Usually small things, but be prepared to spend some time going through your project and fixing query errors.

Hope this helps a bit, I’ll post back if I think of something else.

Thank you for the comments. In the middle of this now.

OPC went fine. Magic! I use the ability to put in my user/pass/domain in FSQL when adding a remote host and it just works. Not a long term solution because of password aging requirements, but short term no less than magic. Couldn’t get it to work otherwise, either this time or past attempts.

One gotcha in setting up datasource to new DB: IT has domain authentication set up, but I can’t enter a domain name in at datasource setup.

I tried

user
domain/user
domain\user

But it fails. I have requested a non domain-authenticated user, but I am not sure when that will be setup. I searched the forum, but couldn’t find any thing relevant. This limitation appears to exist in FPMI as well as FSQL.

What you’re referring to as “domain authentication” is “windows authentication” in SQL Server and around here in the forum.

It is possible to use windows authentication… but “SQL authentication”/mixed mode (just a username/password) will be easier.

Regards,

just a ? but I have a similar size mysql installation, and backup takes roughly 30 minutes, using the admin tool. However if you look for a program called SQLYog, its backup is waaaay faster. Another option is using a tool like mysqldump or xtrabackup. Is the MySQL server in Windows, Linux or Solaris?

It’s on Windows… and it takes over 24 hrs now. Not really sure why. On Linux, they have a perl script that really helps but I think that locks the tables, drops to a shell and does a file copy, then releases the lock. Moving this qty of data on fast hardware is not instantaneous, but it won’t be hours. But with the admin tool… over a day. The first time I locked all tables and that shut us down for the entire day. The cached filled and that was that. Now I do table by table, and made a large cache in FSQL so it appears we can keep up.

This is worst part of the whole project. I had no way to estimate the backup time of our database. I see the select statements showing up in the slow query log that are part of the backup process, and many of them can take a loooonnnnnnggggg time.

I know MySQL best, so I used that. Most here in the IT group use MSSQL. They didn’t care what I did it in, but when I ask for help, they really aren’t able to assist. So they suggested I migrate to a corporate MSSQL database and then at least for me backups are out of my realm of responsibility.

I keep getting an error asking me to

Select Index Column
FactorySQL could not find the expected Index Column

Can I ask what the expectation is so I can add it? Where is this stored?

FactorySQL expects each table to have a unique integer index column, and will ask you to choose it when starting a group if the default (“tablename_ndx”) isn’t present.

To set the index column manually, click on the “options” link above Table Name on the group’s action tab, and select “Edit Column Assignments”.

In FactorySQL these assignments are stored internally, linked to the table name & connection. This has been modified in Ignition, where it is now just a property of the group.

Hope this helps,