Visual Foxpro Tables to SQL Server Databases
I would like to know if there is someone who had the need to migrate an application using Visual Foxpro free tables into a SQL Server (it may be the express version).
What was the approach that worked best?
What were the impacts on the application you had; what were the lessons leaned and things to avoid?
Did you staged data through a VfoxPro database? Which database conector have you used. MS ODBC?
If there's someone who challenged this with another kind of database, it would be useful too to know their experience.
Kind regards
Lucilia Coelho
What was the approach that worked best?
What were the impacts on the application you had; what were the lessons leaned and things to avoid?
Did you staged data through a VfoxPro database? Which database conector have you used. MS ODBC?
If there's someone who challenged this with another kind of database, it would be useful too to know their experience.
Kind regards
Lucilia Coelho
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I am guessing that the topic may already be covered in some of the references above, but just in case - keep in mind that not all VFP data table field types are supported in SQL Server data tables.
Most often encountered are Date fields in a VFP data table that will need to be converted to DateTime fields in the SQL Server data tables (or some other field type) and your application code will need to take this into consideration. Maybe if what Olaf says above: At least the date only field type is available since 2008 is true, this issue may be gone by now.
And SQL Server queries on Character fields are typically not case-sensitive. So your application might need to take that into consideration also.
Also how SQL Server queries deal with NULLs is sometimes hard to figure out.
While using VFP Remote Views first appeared to be temptingly easy to implement in the last back-end migration I did, I found them to be intermittently problematic. I ended up converting all of those Remote View data acquisitions to ODBC with SQL Pass-through which proved, in my case, to be a lot more reliable.
My own most recent experience has involved moving the backend data tables for an application on a functional module-by-module basis one-at-a-time.
In that way I was able to keep the rest of the application running as-is against the original VFP data tables while moving and confirming that the new SQL Server tables related to that specific functional module were working as expected.
Depending on how isolated your functional modules are from the data table perspective - that might be an option. Some cross-functional module table use can be dealt with relatively easy. But if your modules are totally and integrally intertwined from a data perspective, then this approach may not work for you.
Good Luck
Most often encountered are Date fields in a VFP data table that will need to be converted to DateTime fields in the SQL Server data tables (or some other field type) and your application code will need to take this into consideration. Maybe if what Olaf says above: At least the date only field type is available since 2008 is true, this issue may be gone by now.
And SQL Server queries on Character fields are typically not case-sensitive. So your application might need to take that into consideration also.
Also how SQL Server queries deal with NULLs is sometimes hard to figure out.
While using VFP Remote Views first appeared to be temptingly easy to implement in the last back-end migration I did, I found them to be intermittently problematic. I ended up converting all of those Remote View data acquisitions to ODBC with SQL Pass-through which proved, in my case, to be a lot more reliable.
My own most recent experience has involved moving the backend data tables for an application on a functional module-by-module basis one-at-a-time.
In that way I was able to keep the rest of the application running as-is against the original VFP data tables while moving and confirming that the new SQL Server tables related to that specific functional module were working as expected.
Depending on how isolated your functional modules are from the data table perspective - that might be an option. Some cross-functional module table use can be dealt with relatively easy. But if your modules are totally and integrally intertwined from a data perspective, then this approach may not work for you.
Good Luck
I was doing very much that. Starting from VFP DBC, migrating to SQL2005. While that database already migrated to SQL2008R2, the app was redone in VFP9 and still is, soon in it's 7th version.
The application design was not impossible to change to sql server, still it evolved over 7-8 years from vfp6 to vfp9 without using some framework as a basis, and so we decided to do both a database redesign and a reimplementation using VFP9 plus some framework.
1. What was the approach that worked best?
I can't answer you "what approach worked best", as that was the only approach we took, the customer did decide we should use a certain framework, another software vendor also working for them uses. So that and data access via cursoradapter of this framework were predefined. The decision for cursoradapter (CA) is something I'd had done anyway, on the basis of it being the most new and comprehensive technology of remote data access. You know there are also remote views, but the view designer and view properties and events are limited. I also use SQL Passthrough (SPT), eg to send sql scripts doing database updates and a few other things, this can be mixed without problems, even using the same connection.
2. What were the impacts on the application you had; what were the lessons leaned and things to avoid?
The application was not extended during the migration process, which took a year, that was the plan at least. Of course there were some updates of the legacy app, while the new app was redesigned a bit different and also had some new features, overall it was a slight step backwards in features in the end, but that was a compromise on the migration time.
A portion of the new version wasn't well tested with migrated data, so we had the effect of data driven workflows being broken, when they were depending on the migrated data, while newly started workflows were ok. That was not so much because of migration errors, more because of a more complex redsign in that application domain. So don't forget you need to test the quality of the migrated data, especially with a redesign not fully compatible. Again, that was also a compromise of time vs quality. We did at least get rid of problems with corrections, but that was a lesson learned hard.
Positivley the new version of the application was faster. There is no single figure that could be a measure for that, but subjectivly people talked of a factor about 2. Your mileage may vary, but I'd say a decent dimensioned sql server given, the server side execution of sql has it's advantages.
3. Did you staged data through a VfoxPro database? Which database conector have you used. MS ODBC?
We did stage the data from the original dbc into the redesigned database done in another DBC, which included adding GUIDs to the integer ids, as that is what we use for primary keys in sql server (uiqueidentifier field type). That blew serveral tables over 2GB and thus needed to be done in chunks. We then staged that data into txt files, which were imported through bcp utility.
We use the SQL Server ODBC driver, which in my oppinion works best with cursoradapter, as it does not have the overhead of creating an adodb.recordset additional to the cursor. Also this allows to use the same connection for CA and SPT. Just watch out, that you use the cursorschema property of the CA class, otherwise varchar(max) can be converted to C(0) fields in the foxpro cursor, if the first retrieved varchar(max) field is empty or null.
You also need to take care, that sql server doesn't know empty dates. At least the date only field type is available since 2008, but not an empty date, so that needs to be ex-/imported as either some ultimo date, like 01/01/1900, which is smaller than any other date in the data, or as NULL. Besides that take care of how sql server interprets N(x,y) in a different way than VFP, resulting in a differing range of allowed values. I decied for float on the sql server side.
Bye, Olaf.