September 5, 2012 at 10:27 am
I am working in an environment where there exists a foxpro database (business application), which has to be synchronized to various remote sites at least three times a day. This is accomplished via a manual import/export process currently. I am working on a way to automate this process. (oh and the link between the sites is slow with high latency.., which is why i am choosing merge replication) I have an idea how to make it work, but i would like any comments or suggestions from the community.
first, i have 186 foxpro tables which require sync (although only about 60 which actually see frequent updates).
my thought was to use SSIS to import the data into SQL (currently 2012), use merge replication between the sites, and have another SSIS package push the replicated data to the local foxpro instances.
pieces of puzzle:
1. getting info from foxpro to SQL. I already have the OLE DB provider, which works just fine. The foxpro tables have an update timestamp on them, so i can query for 'new changes'. biggest challenge here is writing the import/lookup/insert/update process for EACH table. I can do it, but it makes a very long workflow. Does anyone know of a way to script this into something like a foreach construct, and feed in the various column definitions via variables?
Once the record from foxpro has been imported, in foxpro, it's 'new changes' flag is updated to a site ID (origin site)
2. SQL replication.. this one is straight forward, setup the sites. replicate.
3. data back into foxpro. - this step i'm still trying to figure out. I only want to push the updated records back into each local foxpro instance. I see two ways of doing this. via SSIS, i could do something similar to the reverse of the import process, where i look at each table, do a lookup against the local foxpro, and update/insert accordingly. This would probably be the most robust, as it would just make the foxpro data sync with the sql data, but would require another huge and ugly SSIS package.
Other way i was thinking about. Could add a trigger onto the SQL tables, and upon an insert or update, it will create a record in a SyncControl table, including generating an sql update statement. I would then also replicate this sync control table to the other sites, where the ssis package would just read the sync control table, and execute those commands against the local foxpro database. so almost like log shipping. makes for a very simple SSIS package, but if anything gets screwed up in the underlying foxpro database, this would never really fix that.. (ie. missing records.) Also i then have to maintain all the logic in the triggers, but as the logic and column definitions are different per table, that kinda makes sense anyway.
Or kinda between the two, the triggers could just update a control table with an indication of table and row, and the SSIS package on the end would use logic to run a sync/compare only against the tables in the sync control table. would still make for a large and ugly SSIS package...
Thoughts, questions, comments, better ideas?
Jared
September 6, 2012 at 6:40 am
I think an old-school system like FoxPro requires an old-school solution. Using SQL is complicated.
Andre Evangelista
September 6, 2012 at 8:28 am
OK, do you have an old school solution to share? I would be more than willing to look at this problem from a different angle all together, but I am unaware of any other product which will reliably and automatically sync changes over 4 different sites.?
September 6, 2012 at 8:33 am
I used to do this a long time ago. The way I accomplished it was to write a FoxPro program that chopped the file up into chunks based on row numbers. So if the FoxPro table was 250,000 rows i would make 5 files of 50,000 rows each. Then use a Windows Scheduled Task and xcopy to copy them onto the remote file share. In my case, it was just one table though. You'll probably need something slightly different to handle the 186 tables but xcopy is a good place to start.
I can't remember the FoxPro code now and I no longer have the program but that's how we used to do it 10+ years ago. I'm thinking it was a for/while loop but that's about all I remember.
Edited to add some more detail.
September 6, 2012 at 9:05 am
Unfortunatly a simple copy wont do for the following reasons:
approx 3.5GB worth of data. that would saturate the available bandwidth for many hours to do a single copy.
bi-directional sync. Changes from 4 different instances are synced back and forth (well, more of a circular sync pattern, but anyway..)
It's currently being done by an export executable (which i dont have source code for), which ties into email, file shares, etc. very unflexable. The program scans tables for changes, packages changed records up in a zip, and sends that along to the next site..
bad part is it's an entirely manual process which basically consumes a person's resources. That's all they do for 80% of their day. yuck.
So my SSIS import step would basically be doing what the existing executable is.. scan the foxpro tables for changes, and pull those changes into SQL. then i am using built in replication to do what the old package and email step was doing. then i still need to get the info back into foxpro on the far end. that's where it's getting tricky for me (or at least, trying to choose which way to go..)
September 6, 2012 at 9:18 am
Right, forgot about the part where you mentioned the site-to-site links are slow, high latency. How does executable know about a change? Does it assume the copy at your site is the parent and the other sites are children? This does sound like something you could accomplish with SSIS. Is it possible to connect to FoxPro using one of the existing connection managers? I'd wager you could do so using a standard OLE DB connection, no?
September 6, 2012 at 9:45 am
each table in foxpro has an update sequence field. when a change is made via the front end application, that field is updated to a timestamp value. the export process looks for those values, and grabs the records. once synced, the update sequence field is set back to a two digit site id (where the record originated from).. so looking for new records is a simple 'select where uptsqe > 100' for each table.
yes OLEDB access is working fine.
my issue is i have to define a separate query and column mappings for EACH of the tables. so some way to script that would be beneficial.
the second part of the problem is writing the updated records back out to the individual sites. because the updates have to go out to multiple sites, i cant just use the same select statement then reset the seq field within the SQL database, cause then i would have only updated one of three sites. So i was thinking a trigger on the tables which would write a record into a syncControl table. this table would have the table, the destination site, and potentially more.
so the outbound SSIS package would then read the syncControl table for it's local site, and update only the new records based on the contents of the syncControl table.
but that's where i could go a few different ways... if i am creating triggers on every table anyway, i could have the trigger actually generate a dynamic update statment, which i could store in a text field of the syncControl table. then the SSIS package would literally only have to read that table, and execute the various commands against the destination foxpro database. BUT... that doesn't handle memos or other large data types very well.... so i could create an update statement using a join to the replicated table..
OR i could just store the table/rows in syncControl, and execute a pre-formed SSIS lookup/update/insert structure for the destination table. but then i have to build that structure for each table as well.. yuck.
So again, any ideas on how to script out the SSIS packages to different table definitions would be great.
September 6, 2012 at 10:57 am
Or... you could rewrite the entire application in MS SQL 😉
Whichever way you decide to go, it sounds like it's going to be a lot of work.
September 7, 2012 at 2:11 am
Ok, here's my old-school Foxpro solution which can be fully automated:
1. A process copies all the tables into a folder, but with newer records only.
2. The data is then Zipped into a file.
3. The Zip file is emailed to the sites.
4. The sites receive their emails, unZip the data, and update their files.
I use a toolkit for Visual FoxPro that has smtp and pop3 functions.
Andre Evangelista
September 7, 2012 at 8:50 am
How about just keeping the front-end part of the Foxpro application and have all the data in SQL Server? Lose the Foxpro database part? This would require pointing the Foxpro app to the SQL Server tables and then you would use the replication power of SQL Server to merge the data between the sites.
I don`t know if you can modify the application but this could be a different approach.
:unsure:
September 7, 2012 at 9:00 am
Yvan Bouchard (9/7/2012)
How about just keeping the front-end part of the Foxpro application and have all the data in SQL Server? Lose the Foxpro database part? This would require pointing the Foxpro app to the SQL Server tables and then you would use the replication power of SQL Server to merge the data between the sites.I don`t know if you can modify the application but this could be a different approach.
:unsure:
Foxpro - and particularly the more recent releases - talks almost native SQL Server. Converting an app from VFP backend to SQL Server backend can be quite a lot of work but it's not rocket science, and there are plenty of folks lurking around here who've done it a few times. Once the data's in SQL Server, the world is your lobster.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 7, 2012 at 9:15 am
ChrisM@Work (9/7/2012)
Yvan Bouchard (9/7/2012)
How about just keeping the front-end part of the Foxpro application and have all the data in SQL Server? Lose the Foxpro database part? This would require pointing the Foxpro app to the SQL Server tables and then you would use the replication power of SQL Server to merge the data between the sites.I don`t know if you can modify the application but this could be a different approach.
:unsure:
Foxpro - and particularly the more recent releases - talks almost native SQL Server. Converting an app from VFP backend to SQL Server backend can be quite a lot of work but it's not rocket science, and there are plenty of folks lurking around here who've done it a few times. Once the data's in SQL Server, the world is your lobster.
interesting idea.. the app is native VPF.. i just made the assumption that it only really talks to a foxpro back end. That being said, thats probably not a project i would want to take on, as i'm not horribly familiar with the front end, and the vendor no longer supports this version (they have moved to sql, go figure.. but new version doesn't support multi database bi-direction sync.. i THINK they were too liberal in the use of identities, and now cant figure how to make that work.)
like many large corps.. stuck with an old working solution, and no easy upgrade path in our existing environment.
any articles, blogs, or something you could direct me at for converting the back end while maintaining the existing app? worth a look at the least.
(and outside of anything else, it is rather nice having a copy in sql.. at least we can start dumping the access reports in favor of sql reporting.)
September 7, 2012 at 9:19 am
jared 11265 (9/7/2012)
ChrisM@Work (9/7/2012)
Yvan Bouchard (9/7/2012)
How about just keeping the front-end part of the Foxpro application and have all the data in SQL Server? Lose the Foxpro database part? This would require pointing the Foxpro app to the SQL Server tables and then you would use the replication power of SQL Server to merge the data between the sites.I don`t know if you can modify the application but this could be a different approach.
:unsure:
Foxpro - and particularly the more recent releases - talks almost native SQL Server. Converting an app from VFP backend to SQL Server backend can be quite a lot of work but it's not rocket science, and there are plenty of folks lurking around here who've done it a few times. Once the data's in SQL Server, the world is your lobster.
interesting idea.. the app is native VPF.. i just made the assumption that it only really talks to a foxpro back end. That being said, thats probably not a project i would want to take on, as i'm not horribly familiar with the front end, and the vendor no longer supports this version (they have moved to sql, go figure.. but new version doesn't support multi database bi-direction sync.. i THINK they were too liberal in the use of identities, and now cant figure how to make that work.)
like many large corps.. stuck with an old working solution, and no easy upgrade path in our existing environment.
any articles, blogs, or something you could direct me at for converting the back end while maintaining the existing app? worth a look at the least.
(and outside of anything else, it is rather nice having a copy in sql.. at least we can start dumping the access reports in favor of sql reporting.)
If it's a vendor app, it's unlikely you will have access to the source. Without that you're kinda stuck. There are workarounds but they're ugly.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 7, 2012 at 9:47 am
After taking a step back and re-reading your op it seems like the biggest hurdle here is scanning through all the tables. Or at least, setting that up for the first time in SSIS. I'm not sure how you would accomplish it but you could probably use a script task to do this in SSIS.
I also think it might be interesting, as others have mentioned, to convert that back end to SQL. Still sounds pretty daunting.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply