FoxPro to SQL Server migration experience
At first I was going to write a detailed white paper about the conversion effort. But someone has already done it. There is a very good guide at:
https://nationalcom.com/home/download/Conversion-VFP-SQLServer.pdf
Since FoxPro is nearing its end of life, the company I was working with needed to convert the system to newer technology. They chose Microsoft SQL Server and Microsoft .NET. There was over 1 terabyte of data being processed in FoxPro.
The project was fairly easy as far as the data conversion goes. There were a few decisions to make and one thing we learned about the difference in the NUMERIC data type between FoxPro and SQL Server.
The end result is pretty exciting. Some of the processing jobs that took over 1 day in FoxPro now take 25 minutes in SQL Server. The improvement is due to several facts: 1) The FoxPro code was doing row-at-a-time processing and the Transact-SQL code was written to do set processing; 2) FoxPro has a file limit of 2GB and with over 1TB of total data there were almost 1000 files to open and read through.
The most difficult part of the project was taking years worth of FoxPro code from different developers. The code had started with DOS FoxPro and had been converted to Windows FoxPro and upgraded by different people with different coding styles over the years. And as usual, the documentation was the code. J
Here are the notes from the data conversion effort. I’m only covering the data types that we had in our schema, which was not a very wide range. If you have more data types they are most likely covered in the guides noted above.
1. If you have MEMO data type in FoxPro you should use VARCHAR(MAX) in SQL Server. We could have used TEXT but we know that is being phased out in some future version in favor of VARCHAR(MAX). And VARCHAR(MAX) has better performance.
2. There are many DATE types in FoxPro. The decision was pretty easy to convert them all to DATETIME in SQL Server 2005. In SQL Server 2008 there are more DATE types so you will have to study which FoxPro types map to the new SQL Server 2008 types. DATETIME data was exported in character format using YYYYMMDDHHMMSS.
3. In FoxPro NUMERIC(5,2) allows a range of -99999 to 99999. In SQL Server using the same precision the range is -999.99 to 999.99. This was the only data type in our schema that needed to be altered at the data type level. So in SQL Server we had to use NUMERIC (7,2) to get the same range.
4. FoxPro LOGICAL fields were either exported as T/F or 0/1. They either went into a BIT data type in SQL Server or a TINYINT. The TINYINT was chosen if we wanted to do any calculations on the field in the future; i.e. SUM, AVG, etc.
Not knowing much about SQL Server, the database guys made a pretty good choice to dump the data to flat files in character mode, then used a simple Transact-SQL script to loop through the files and import them using the BULK INSERT command. A quote from one of the lead developers on the project: “We knew about Integration Services and could have used that but with our lack of experience with this tool we decided to use T-SQL scripts instead”. The scripts and data conversion took one person about 3 weeks to write and tune. This same person converted most of the other server side processing scripts to Transact-SQL in about 2 months.
Since FoxPro has a limit of 2GB file size and there was over 1TB of data in total, there were almost 1,000 files to convert. Everything was exported from FoxPro as character using a tab delimiter. It was determined that this was a safe delimiter after having to go through the memo fields to make sure there were no tab characters embedded.
Just as a side note, the FoxPro screens were all rewritten and reengineered using ASP.NET and C#. I will let someone else write about that experience if they wish.
Thanks to Spyros Christodoulou and Petros Hadjigeorgis from Nielsen EMEA for their assistance in writing this blog and for making the project successful.
kevin cox