FoxPro Programming
Realtime sync of DBF and SQL tables
Gravatar is a globally recognized avatar based on your email address. Realtime sync of DBF and SQL tables
  Larry M
  All
  May 22, 2020 @ 03:26pm

I'm not sure which forum to post this question to. We have decades worth of in-house VFP applications still using DBFs as the primary data source. After years of failed .NET re-engineer efforts, and not wanting to rewrite all of the VFP code to use SQL instead of the DBF files, we are trying to find a way to keep the DBF and SQL tables synced in real time. We already read and write to SQL, but about 70% of the production data gets copied overnight using SSIS packages, and any new .NET developement has to keep both the SQL and DBF files updated. Don't know what the solution is, we are in this situation now because the .NET replacement for VFP was always "right around the corner" for the last 15 years. Do you offer any products that would facilitate the real time data sync?

Gravatar is a globally recognized avatar based on your email address. re: Realtime sync of DBF and SQL tables
  Rick Strahl
  Larry M
  May 22, 2020 @ 06:12pm

I'm not sure that such a thing exists. You can perhaps look into SQL Server replication with external data sources connected to DBF tables, but I think that'll end up being way more complicated and probably not very reliable especially with legacy data.

It sounds to me based on your description that your way forward is to migrate the data to SQL Server so that you can more easily manage data both in a FoxPro application and other non-Fox applications.

This isn't transparent obviously, but it is a way to get to a consistent data environment that's more easily shared. I don't think there are any shortcuts. Any sort of synchronization scheme is likely to have problems with data conflicts and the potential for data corruption in that scenario is pretty high. Even if there were tooling for that I'd be hard pressed to trust it - anything but a specific sync solution that knows about your business rules seems like it would be incomplete.

No easy answers here in my opinion.

+++ Rick ---

Gravatar is a globally recognized avatar based on your email address. re: Realtime sync of DBF and SQL tables
  FoxInCloud Support - Thierry N.
  Larry M
  May 23, 2020 @ 06:46am

Hi Larry,

We at FoxInCloud have something that may fill your need: a VFP database synchronisation class that works over the LAN or the Internet. So far it works with VFP tables on both ends, however the critical code that really updates data is 300 LOC out of 4,000 so it should not be so hard to adapt to a SQL database on either end.

It's been running in production for over 10 years so I can assume it's bullet-proof

Here is it does:

  • syncs the tables from the top to bottom of the table hierarchy
  • rollbacks the whole sync if any error occurs
  • matches records on primary or candidate key (the latter case being useful of both ends have different primary keys schemes)
  • handles the linked files (identify file pathes and sync the file based on size and/or date)
  • logs everything in details
  • can send e-mails in case of a fatal error
  • client and server run the same class, client initiates the sync.
  • you can ignore some tables and/or fields

Here is it what you need:

  • Each table must have (no constraint on names):
    • a primary or candidate key (unique)
    • a field "last modification date-time" T
    • a field "last modified by synchronization" L
  • Relations between tables must be declared (data integrity rules not needed)

Of course, if some table structure differ on both sides, we'll need to add some mapping rules.

You can sync as a scheduled task as often as you want. Conflicts may occur at the record level; in case of a collision, one side has the 'last word' and overwrites the other side. Combined with very frequent syncs, this usually makes no problem; If it did in your case we could log the conflicts to be fixed manually before doing a successful sync.

I can post the class signature if a closer insight is of interest to you.

years of failed .NET re-engineer efforts

With FoxInCloud you can move your VFP app. to the Web and slightly move your server to another technology. I other words, rather than moving both client and server (and perhaps database) to another tech (doomed to fail, known for decades), you can move your app piece by piece.

Gravatar is a globally recognized avatar based on your email address. re: Realtime sync of DBF and SQL tables
  Anish Abraham
  Larry M
  Nov 19, 2020 @ 11:03am

Hi Larry, Were you able to get a solution? If not let us know and I would be willing to share some information which would help you.

To give a background,

We were facing a similar problem with VFP and DBF in backend. There was about 4000 locations from where we need to collate the data. We used to get the DBF files from all these 4000 locations and used SSIS to load the data to SQL Database which was a PAIN and often it failed and required manual intervention.

Finally we developed a custom solution which synced the data - almost real time. First time sync took some time, but once sync-ed any subsequent insert/update would be available almost instantly in SQL DB. Our central SQL DB had more than 40 Million data rows in multiple tables (i.e. DBF files).

Was happy to see a similar requirement and would be more than happy to assist if a similar solution would be of any help to you.

Thank You, Anish

© 1996-2020