By Nellaiappan L | Published on January 18th, 2017 | Last updated on July 1st, 2019 |
Importing a FoxPro DBF to SQL
Importing FoxPro database to SQL Server is a major concern as there are a lot of uncertainties associated with how well the application will survive the conversion. FoxPro is a file-server system that supports both Database Management System (DBMS) and Relational Database Management System (RDBMS), but it lacks in transactional processing. It is ever compelling to move on to SQL server backend as the client/server database is more efficient than the file server-based system. This is mainly because only short and carefully designed requests and results are traveling across the network increasing application performance.
Let’s take a look at the general methods employed for loading data from Visual FoxPro database files into SQL Server:
- Bulk Copy Program (BCP) quickly loads data into existing SQL tables. This is applicable only to comma-delimited text files that already have data in the right format. This method is advantageous as it bypasses the logging of new records.
- Data Transformation Services (DTS) has various options and works with different types of data sources. It employs VB-Script to transform data and can be scheduled to run at off-peak hours.
- VFP Upsizing Wizard automatically generates table structures, index, defaults, RI information and validation rules. There is no need for an SQL to exist at the time of running the wizard. The downside is, it does not upsize free tables or triggers.
- A custom converter is a final way to go as everyone writes their own conversion routines. DTS and the Upsizing Wizard don’t serve the practical need of transferring complex data into the SQL server.
The success or failure of a VFP DBF migration to SQL server will be reflected in the subsequent performance of the application. There are quite a number of challenges involved in importing VFP data onto SQL server as discussed below:
- Data Conversion Issues –
- SQL/VFP data storage capacity limitation that arises due to the row size difference.
- VFP applications are known to have normalization issues, which affect the performance of SQL server.
- Data Design Issues – We need to identify and map the equivalent datatype from VFP to SQL Server. SQL will not have the same datatype of VFP and it will have a better data type which can bring the optimization of the data reading capability.
- Indexing issues – Table indexing is key to optimization and fast queries with a direct impact on SQL server database performance.
Some helpful tips to be considered while importing VFP data onto SQL Server:
- Appropriately mapping the VFP database to the SQL server database by specifying individual tables to upsize brings about flexibility in the overall conversion and data synchronization process
- Several tools are available in SQL server (like the Query Analyzer, SQL Profiler, Index Tuning Wizard) that help improve query efficiency and optimize indexes for better SQL performance.
- Data handling issues that impact the SQL server performance can be overcome by having a defined connection class.
- Transaction processing efficiency is improved by having short transactions and referencing frequently used tables at the end of a transaction.
- Use the concept of roles in the SQL security model by providing conditions to restrict the user rights during the course of the connection.
At Macrosoft, we approach each VFP conversion differently, and often give our clients several options for how best to accomplish the task of importing VFP data into SQL server, based on the client’s needs and priorities. Moving your application to a .NET framework and relying upon SQL Server will provide many benefits, most importantly in the areas of security and performance. It is always wise to look at the future of the application and scalability before beginning conversion. By carefully considering these suggestions and having a suitable process in place you can seamlessly import VFP into SQL server with incredible performance.
Nellaiappan is the Application Delivery Manager for Macrosoft’s .NET migration team. Subsequently, Nellai leads the Migration Service to migrate client legacy systems to the latest technologies such as .NET. As a PMP certified professional, he has received accolades from clients for his efficient leadership. Incidentally, during his first migration project, Nellai accelerated the migration resulting in a new service offering from Macrosoft. Later, Nellai worked on an array of proprietary migration tools that form the backbone of the Migrations Practice at Macrosoft.
Nellaiappan holds a Masters in Computer Applications and has 17+ years experience in developing and leading Windows and Web based Applications using Microsoft Technologies.
