By Marcel Heitlager - 6/15/2009
We have an older application using VFP tables. We have a website that's been migrated from VFP tables to SQL Server. Before when we pushed/pulled data we would just Secure FTP it the local drive and then import it into app.
The tables have anywhere from 400-30,000 records, about 20 fields. We we're thinking of using ES to handle the transfer, over a fast connection. It involves clearing the server tables, and then creaing new records in BO with VFP data, and when done, save all the records to server. With the given scenario would that be something within the capabilities of ES?
Also, to add one more variable to the mix, the SQL Server tables are more normalized than the VFP tables. So new keys have to be generated for the SQL server tables, and there are some parent child relationships. In this case would it be better to just use GUID's for those fields (I've read up on them in the forum already), so that it will avoid a round trip to the server to get the AutoIncrement values, or would AutoInc's not be much of an issue because of the size of the tables? We're trying to minimize the length of time this takes so it can be completed within about 15 minutes (at the most no more than 30, but quicker is better )
Thanks,
Marcel
P.S. - Is it common practice to use GUID's as ForeignKeys or do they usually only use GUID's for the parent tables and all child tables contain INT keys? INT's might cause issues for me, so that's why I'm asking.
|
By Trent L. Taylor - 6/15/2009
With the given scenario would that be something within the capabilities of ES?
From what I can tell, you are trying to do a conversion. So this would be a one time conversion, correct? Whenever we do this, and we use the ES extensively, we try to give ourselves the best chance of success...especially when FoxPro is in the formula!
The ES is going to compress the data better than any type of direct connection, no question there, but if you could get the VFP tables at the same location as the SQL, that would always be the best scenario. Otherwise, here is what you need to do in order to get speed. Instead of retrieving all records at once, if possible, bring back 1000 at a time, convert them, then save. This is a far safer approach with the VFP tables.
so that it will avoid a round trip to the server to get the AutoIncrement values, or would AutoInc's not be much of an issue because of the size of the tables?
if you are going from VFP to SQL, then the autoinc fields should just be part of your query to retrieve the records to convert. If you are going to use GUIDs from this point forward, then you are going to want to create a dictionary of the old and new PKs so that you can line up all of the records. This would just be part of your conversion program.
Is it common practice to use GUID's as ForeignKeys or do they usually only use GUID's for the parent tables and all child tables contain INT keys? INT's might cause issues for me, so that's why I'm asking.
If you are using GUIds as your PKs, then be consistent. Otherwise you have some GUIDs and some INT PKs which would be a mess. Go with one or the other. INT or BIGINT is always going to be easier (and smaller) to work with as a PK, but if you have the need for GUIDs, then use them for all PKs.
|
By Marcel Heitlager - 6/15/2009
Thanks for the helpful reply.
It will not be a one time conversion. The data will be updated on a daily basis, to keep the old application in sync with the new website.
Basically the VFP app. is an administrative program and the website is accessible to public. Like School Administrator and Students web access.
When the old VFP application has been migrated at some future time then all this transfer stuff won't need to happen anymore. Admins will be able to access data on server using ES and smart app.
We needed to update the website and we didn't want to have to redo it again when migration happens. Hence the "brute force" transfers on a daily basis. Also this will be done by a number of different admins at different locations.
It seems though that using ES will be the best option. They (not me), have tried to do it through ODBC, but because of security issues, it requires that we open a hole in the firewall for each admin trying to connect (not gonna do it).
|
By Trent L. Taylor - 6/16/2009
Sounds good. Good luck 
|
|