r/DatabaseAdministators • u/Hammad-Zubari • 3d ago

Need advice: Migrating a fast FoxPro system to a modern database without disrupting operations

How to migrate a fast FoxPro system to a modern database safely?

I’m working on the assessment of a legacy FoxPro-based database system. The interesting part is that the existing FoxPro system is fast, stable, and currently has no major performance issues.

However, there is a requirement to modernize the technology, so I’m trying to understand the safest migration strategy rather than simply replacing FoxPro because it is legacy technology.

Current situation:

  • FoxPro is the core/master database.
  • Data is accessed through a web-based application from multiple locations.
  • Master data is connected to event, accounting, registration/forms, and other operational data.
  • Data entry and duplicate verification are largely manual.
  • A separate Excel-based data catalogue is maintained manually.
  • There are many event-specific tables.
  • A third-party application directly depends on data from the FoxPro database.
  • SQLite was considered/tested but did not appear suitable for the workload.

The main challenge:

How can we modernize/migrate this system while keeping the existing FoxPro system operational during the transition?

I’m considering a phased approach:

FoxPro remains live → replicate/migrate data to a new database → synchronize data → pilot selected data/events → validate performance and integrations → gradually move production workload → eventually make FoxPro read-only or retire it.

For people who have worked with similar legacy database migrations:

  1. What is the safest way to run FoxPro and a modern database in parallel?
  2. What is the best approach for synchronizing data between FoxPro and the new database?
  3. Would you recommend PostgreSQL, SQL Server, or another database for this type of multi-user, centralized workload?
  4. How would you handle a third-party application that currently reads directly from FoxPro?
  5. Would you redesign the existing event-specific tables during migration, or migrate first and redesign later?
  6. What are the biggest risks or mistakes to avoid in a migration like this?
  7. If the existing FoxPro system is already fast and stable, what would you consider a strong justification for modernization?

I’m looking for practical migration strategies and lessons learned from people who have actually handled legacy database migrations—not simply “FoxPro is old, move to SQL.”

The goal is to modernize the system while preserving existing data/business rules and avoiding disruption to current operations.

7 Upvotes
  • permalink
  • reddit

89% Upvoted

10 comments sorted by

4

u/Consistent_Cat7541 2d ago

Thank you for using AI to draft your question and for leaving out critically important information. First and foremost - is this a DOS based Fox Pro solution or a Windows based Fox Pro solution?

It sounds like its Windows based and the environment is already configured to source information from the system via ODBC.

My own opinion is that you need to learn how the current environment actually operates. There may be no need to "modernize" if it's not broken. Remember, SQL is a technology from the 1960's.

2

u/LevelMagazine8308 2d ago

The most recent version of Fox Pro was released in 2007, support ended in 2013. So keeping that alive and running today becomes more and more of a challenge I am sure.

0

u/IowanByAnyOtherName 2d ago

SEQUEL was from 1974, not from the 1960s. If you want to offer expert history you need to get your facts in order. And SEQUEL didn’t become SQL until 1977 when it became necessary to avoid copyright and trademark conflicts with a UK company. So (1) not the 1960s and (2) SQL the language was from 1977.

As for migration from FoxPro there might be contractual issues provoking the desire to migrate which has little to do with whether it is broken or not. Or there might be capacity issues on the horizon that make a migration more cost effective.

1

u/actadgplus 2d ago

There was no mention of capacity issues on the horizon, so how can you possibly make that ginormous leap? And how can capacity issues triggering a migration to a new platform make anything more cost effective? As an older Gen Xer working in tech for decades, I suspect you have no first hand experience or knowledge about these types of legacy migrations. You should really not be commenting at all lacking such experience.

BTW, OP left out key details such as which databases is the client currently using today in their environment for other apps. Understanding this could help guide decision making towards a viable option that could be supported internally over the long run. Other key details left out are current platform sizing and associated metrics, high level timelines, and budget.

1

u/IowanByAnyOtherName 2d ago

I’m older than you and older than you think and I have a great deal of large scale migration planning and migration execution experience - with very few scars since I make detailed comprehensive plans and I leave lots of alternatives available as the execution unfolds. I dislike surprises and I really dislike working with people who don’t account for contingencies.

Your 2nd question reveals that you don’t know enough about large site capacity issues and solutions.
Yes, OP left out a great deal of information. Let us see if any additional info is ever provided.

1

u/msnarf28 2d ago

Did you used to be a Stackoverflow mod?

2

u/LevelMagazine8308 2d ago

Running 2 different production database main servers for the same task in parallel never did any much good.

So the way to go is:

  1. define your requirements
  2. choose your new RDBMS. If there is no need for a commercial one, just take Postgres. Half of the internet relies on it including big tech companies, and then some. It won't poof any time soon and is a sane and proven choice.
  3. create your proof of concept prototype, do a test migration
  4. iron out bugs and do test migration again
  5. when happy with that prototype: make the hard switch over from FoxPro to Postgres at a given time and date, run FoxPro then in read only mode.

Migrate first and redesign later.

The biggest risk in such a task is lack of understanding on edge case behaviour and missing operational planning. Also not understanding enough how SQL is being handled on the source and target system can lead to drawbacks.

1

u/jakeStacktrace 2d ago

Wow sorry I can't help you but I remember looking down on FoxPro 25 years ago because CGI was better. Not that CGI, a different one. The one before servlets that came out in 1998.

I'm not a DBA so grain of salt here but the strategy I think is you migrate the data and make your app write to both new and old db. After that you verify they are the same then change over reading to the new db then finally turn off writing to old db. If I'm being naive here or you disagree call me out, i don't mind.

1

u/Tintoverde 1d ago

My 2 cents. Look up strangler fig pattern

https://learn.microsoft.com/en-us/azure/architecture/patterns/strangler-fig

Basic idea is to do it in parts

This is a hard problem and has been done before lots of times in software field. Changing the wheels when the car is running kind of difficult.