Skip to the main content.
Downloads Thriftly Login
Downloads Thriftly Login
Group 762

Migrate and run DataFlex applications with Oracle, MS SQL Server, PostgreSQL, MySQL &  MariaDB.

flex2Crystal

Stuck in Crystal XI?  Upgrade and use the latest versions of Crystal Reports with DataFlex applications. 

BTR2SQL

Seamlessly convert from Btrieve transactional database to PostgreSQL, Oracle, and MS SQL Server.

thriftly-1

Quickly build multi-protocol web services with the same API. Supports JSON-RPC, REST, SOAP,  Thrift, and gRPC.

 Group 671-1

 

Why Mertech?

2 min read

Handling multi-directory data files with BTR2SQL

Handling multi-directory data files with BTR2SQL

A common situation in applications using Btrieve, Pervasive.SQL, and Actian databases involves a multi-directory structure for storing data files. This setup is not easily transferable to a SQL database, but it must be addressed for a seamless migration to an SQL backend. In BTR2SQL, we have developed an approach that simplifies this process and does not necessitate changes to your application.


The scenario pops up because applications using a multi-directory structure store general information in a top directory, and region-specific information in lower directories. Here’s an example of this structure:

multi-directory structure example: folders before migration


Btrieve to SQL migration options

To get around this potential for collisions, Mertech’s BTR2SQL GUI offers several ways to resolve this problem:

Migrate each folder to a separate server or database

If you choose this option, you’ll be migrating each of your company-specific directories to a separate server or database, so similarly-named tables don’t conflict with each other.


Pros:

    • This option allows you to easily load balance your application by distributing data across multiple computers, CPUs, hard disks, etc.
    • If you select this option, you can assign user credentials based on each server or database.


Cons:

    • Your transactions won’t cross multiple “customers,” since the driver will have a unique connection to the database for each data folder.
    • This option adds the administrative overhead of backing up and maintaining additional hardware and software.

 

Migrate each folder to a separate schema (users on Oracle)

If you choose this option, you’ll be migrating each of your company-specific directories to separate schemas (users in Oracle), in much the same way you’d be migrating to multiple databases or servers if you chose the option above. However, migrating to separate schemas on Oracle allows you to more granularly control user access.

Pros:

    • There is a clean distinction between “sets” of data.
    • You can assign different user credentials to each schema, more granularly controlling user access.
    • Using schema-based migration, you can maintain relatively small table lists in each schema.


Cons:

    • The additional schemas you create using this option adds additional administrative overhead.



Migrate each folder using a different table prefix or postfix

If you choose this option, you’ll be adding a unique prefix or postfix to each table you migrate, to note which are used by which directory. Then, you’ll be creating and updating mds.ini files to match this structure, as well as migrating certain .INT files, so your application functions as expected.


Pros:

    • This option is the easiest to implement, as there’s less administrative overhead and maintenance to handle after your migration.
    • This option is flexible and works the same across all supported SQL backends.

Cons:

    • If you have a lot of directories and tables within those directories, your table list can become very large after adding prefixes/postfixes.
    • If you select this option, it will be harder to assign users directory access than it would be if you use one of the other options.
    • If you’re using Oracle, the 30 character limit on table identifiers can cause issues when attempting to assign prefixes/postfixes.


How to use the BTR2SQL GUI

After you weigh out the pros and cons of your migration options the next step is to get started! To get the step-by-step procedures you need in order to perform a multi-company migration, refer to the Multi-company migration of a Btrieve application using the BTR2SQL GUI white paper. 

Originally published November 13, 2014, and updated August 15, 2018

Application Modernization 101: Ultimate Guide to Digital Transformation

Application Modernization 101: Ultimate Guide to Digital Transformation

Imagine breaking free from the constraints of old, monolithic systems and embracing the agility and innovation of cloud-based solutions.

Read More
Cloud Outages: Causes & Risks (and How to Handle Them)

Cloud Outages: Causes & Risks (and How to Handle Them)

This post has been co-authored with Matt Ledger.

Read More
A Comprehensive Guide to Enterprise Application Modernization

A Comprehensive Guide to Enterprise Application Modernization

In a world where technology often outpaces business strategy, enterprise application modernization emerges as a critical bridge. This strategic...

Read More