Database Migration from DBF to SQL Server

Fast and reliable access to business data is extremely important for modern IT infrastructure of companies and organizations. That's why migration from legacy systems to modern powerful platforms becomes a very popular task. One common example of such migration is transferring data from DBF files used by legacy DBMS Dbase, Xbase, Clipper, FoxPro and Visual FoxPro to SQL Server or Azure SQL database.

This whitepaper introduces benefits, bottlenecks and best practices of seamless migration from DBF to SQL Server.

Benefits of DBF to SQL Server Migration

1. Performance. Unlike database management systems supporting DBF format, SQL Server provides efficient and safe data retrieval due to native client-server architecture, indexing features and optimized query processing.
2. Security. Legacy DBMS supporting DBF format do not offer any significant security features. On the other hand, SQL Server provides such advanced capabilities as user access control, encryption and data masking.
3. Scalability. DBF systems were not designed to scale up due to file system and file level access limitations. SQL Server on-premises and Azure cloud offer powerful options of scalability to meet growing demands for data storage and concurrent users access.

Although DBF format does not include such complicated database entries as stored procedures, functions and triggers, there are still some bottlenecks in DBF to SQL Server migration. One of these bottlenecks is data types mismatch. For instance, there are two possible values for DBF logical type - True that is stored as 'T' and False that is stored as 'F'. SQL Server does not have logical data type, but there is a common option to use BIT type instead, providing 1 for True and 0 for False. This may lead to the related type mapping rule, however some migration projects may require to preserve data 'as is'. For such cases CHAR(1) with CHECK constraint is the most suitable type mapping:

IS_DELIVERED CHAR(1) CHECK (IS_DELIVERED IN ('T','F'))

Another issue is missing or incorrect information about character set used by the source DBF file. Usually, encoding is stored in the header of DBF file, but sometimes it may be empty or corrupted. In those cases, correct codepage conversion may be obtained by analysis of the migrated text data. When the codepage is converted properly, text data is readable. Otherwise, migration must be run again using another codepage.

Due to these issues, it is not recommended to migrate from DBF to SQL Server manually. Use the suitable database migration software instead.

DBF to SQL Server Migration Step-by-step

1. Analyze structure and content of DBF files. Exclude redundant data and indexes from migration. Review data types, relationships between tables and any specific requirements for the upcoming database migration.
2. Set up the target database. Make sure that SQL Server is accessible and you have sufficient permissions to create database objects and write the data. Create new SQL Server database or select an existing one suitable for migration.
3. Choose and install database migration software. Connect to MySQL and PostgreSQL database servers. Enter other migration settings.
4. Customize types mapping. It is extremely important to map data from DBF files into the corresponding SQL Server table properly. Inaccurate or wrong mapping may cause data loss or corruption.
5. Run the migration. The migration software must indicate the migration progress and track real-time warning and potential errors into the log file, so the user can investigate all issues requiring attention.
6. Validate the results of migration. After migration is completed, carefully verify data types, data and indices of the target SQL Server database. Ensure data accuracy and run thorough integrity checks.

DBF to SQL Server Converter

There are software tools to simplify DBF to SQL Server database migration ensuring it is seamless and accurate. For instance, DBF to SQL Server converter produced by Intelligent Converters automates types mapping. It carefully analyzes the structures of DBF tables and the data in order to choose the most appropriate equivalent for every DBF column among SQL Server data types.