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:

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