Scenarios using the GUI Migration Utility¶

What happens during runtime?¶

When you launch the BTR2SQL Migration Utility, a Login dialog box prompts for a server name, user name and database name. These entries determine the target backend for the migrated files.

Similarly, when the operator runs the application, a Server Login dialog box displays to again obtain the target backend information. When the application issues a B_OPEN to open a file (for example MARKETING.MKD), the Mertech driver locates the associated INT file (MARKETING_MKD.INT) in the data directory and uses the login information along with the table structure stored in the INT file to connect to and open the converted SQL table.

Creating tables on the fly

In addition to using the INT file to connect to and open a converted table, the Mertech driver also uses the INT file to create tables at runtime.

When an application issues a B_CREATE, the driver looks to see if an INT file already exists for the file. If there is an existing INT file, the driver compares its contents with the index definitions in the new create structure. If they look like a match, the driver uses the definitions in the INT file to create the table on the server. This allows you to have pre-existing INT files (with full field definitions) for temp tables for instance.

There may be times when you do not want an existing INT file to be used, in that case you can set the keynum value for B_CREATE:

B_CM_OVERWRITE_IGNORE_INT_FILE = -6

Opposite this, perhaps you want the driver to always use the definition in the INT. Two more flags force this behavior and the FILESPEC in the dataBuffer is completely ignored:

B_CM_OVERWRITE_FORCE_INT_FILE  = -99
B_CM_NOOVERWRITE_FORCE_INT_FILE = -100

To prevent an INT file from being deleted when a table is dropped, you can set the PERMANENT_INT token in the INT file to YES (the default setting is NO):

PERMANENT_INT YES

Common folder layouts¶

It is common in the Pervasive environment to create files with the same name residing in different folders. For example the C:\MyApp\DATA directory might contain subdirectories for the central, east and south regions of a company. Each subdirectory might contain the marketing, purchasing and sales person information for that region.

C:\MyApp\DATA
  CENTRAL\
    FIELD.DDF
    FILE.DDF
    INDEX.DDF
    MARKETING.MKD
    PURCHASING.MKD
    SALESPEOPLE.MKD
  EAST\
    FIELD.DDF
    FILE.DDF
    INDEX.DDF
    MARKETING.MKD
    PURCHASING.MKD
    SALESPEOPLE.MKD
  SOUTH\
    FIELD.DDF
    FILE.DDF
    INDEX.DDF
    MARKETING.MKD
    PURCHASING.MKD
    SALESPEOPLE.MKD

The problem when migrating these files to the SQL side is that different tables with the same names will collide if stored in the same database. Mertech provides several ways to resolve this problem. You can:

1. Migrate each folder to a different database on the same server (refer to Distributing Tables to a Different Database or Schema).

   serv.db1.dbo.purchasing

   serv.db2.dbo.purchasing

2. Migrate each folder to a different schema name, or user on Oracle (refer to Distributing Tables to a Different Database or Schema).

   serv.db1.user1.purchasing

   serv.db1.user2.purchasing

3. Migrate all folders to the same database and schema but attach a custom prefix to the table names (refer to Using a Custom Prefix or Postfix to Distinguish Tables).

   serv.db1.dbo.cust1_purchasing

   serv.db1.dbo.cust2_purchasing

Other options¶

The BTR2SQL Migration Utility allows you an easy way to distribute an MS SQL database by selecting separate Filegroups for tables, indexes, and text storage (Convert File dialog box | MSSQL tab). Choosing separate Filegroups can streamline administration and increase data access speed.

The BTR2SQL Migration Utility also allows Oracle users to choose different tablespaces for index and data tables (Convert File dialog box | Indexes tab).

You can also choose to migrate some tables to one tablespace and other tables to another tablespace using the scripting option:

1. Use the BTR2SQL Table | Generate | SQL Script for Creating Tables option.

2. Modify the generated script as needed.

3. Run the modified script to create the tables.

4. Use BTR2SQL Table | Copy Data option to copy the data.

Specifying the path to the INT files¶

In the scenario mentioned above, where a similar set of data files is duplicated to multiple subfolders, the INT files in each folder would be the same. While this is not a problem, it can be desirable to consolidate the files for easier maintenance.

When you migrate to SQL, you would deploy this structure (plus your application and Mertech driver):

C:\MyApp\DATA
  CENTRAL\
    mds.ini
    MARKETING_MKD.INT
    PURCHASING_MKD.INT
    SALESPEOPLE_MKD.INT
  EAST\
    mds.ini
    MARKETING_MKD.INT
    PURCHASING_MKD.INT
    SALESPEOPLE_MKD.INT
  SOUTH\
    mds.ini
    MARKETING_MKD.INT
    PURCHASING_MKD.INT
    SALESPEOPLE_MKD.INT

Or, you can add an INT-Folder entry to the mds.ini file to tell the driver where to find the common set of INT files, and instead deploy something like this:

C:\MyApp\DATA
  INTFILES\
    MARKETING_MKD.INT
    PURCHASING_MKD.INT
    SALESPEOPLE_MKD.INT
  CENTRAL\
    mds.ini
  EAST\
    mds.ini
  SOUTH\
    mds.ini

What this has done is eliminate the duplicated INT files. You now only have one folder to maintain for the INT files. You still need each data folder as you would in Btrieve, but you just need the mds.ini file in it with the appropriate INT-Folder and Table-Prefix entries.

[SQL_BTR]
INT-Folder=..\INTFILES

Table-Prefix=CENTRAL_

MdsSetSetting("C:\MyApp\DATA\CENTRAL","INT_Folder","..\INTFILES")
MdsSetSetting("C:\MyApp\DATA\CENTRAL","Table-Prefix","CENTRAL_")

What happens at runtime?¶

When an application issues the B_OPEN API to open a file (for example MARKETING.MKD), the Mertech driver opens the mds.ini file in the data folder and uses the path defined by the INT-Folder entry to locate the associated INT file (..\INTFILES\MARKETING_MKD.INT). The driver reads the INT file and uses the information to connect to and open the converted SQL table.

Migrate each folder to a different database¶

PROs

• An easy way to perform load balancing by distributing data across different databases or hard disks.

• You can assign different user credentials to each database.

• Clean separation of data sets at the database level.

CONs

• Transactions will not cross databases since the driver maintains a unique connection per database.

• More administrative overhead associated with backing up and maintaining multiple databases.

• The driver must have a separate set of connections open for each database and must switch context each time a file from a different database is accessed. This can add performance overhead depending on the order of calls and how frequently the switch is made.

Migrate each folder to a different schema¶

PROs

• Clean distinction between “sets” of data while keeping everything in one database.

• You can assign different permissions to each schema.

• The list of tables in each schema is as small as possible.

• No connection context switching since all schemas reside in the same database.

CONs

• There is a little more administrative overhead maintaining the additional schemas.

• On some backends, cross-schema queries may require additional permissions.

Migrate each folder using a different table prefix or suffix¶

PROs

• This option is the easiest to implement.

• The same approach works in all backends.

• No connection context switching and no additional schemas to maintain.

CONs

• The table list can become very large. For example, 300 data files across four customer folders results in 1,200 tables.

• It is harder to assign separate user access control.

• Oracle’s 30 character limit on identifiers can severely hinder this option.

Prefix vs. suffix: A prefix (e.g., EAST_Hours) groups tables by region, so all tables for a given region sort together. A suffix (e.g., Hours_EAST) groups tables by name, so all versions of the same table sort together. Choose whichever grouping makes the table list easier to navigate in your database management tools.

Consolidate the tables during migration¶

serv.db1.dbo.Hours

PROs

• You have the fewest number of tables to maintain.

• The same approach works in all backends.

CONs

• The application code must be updated to merge data into shared tables.

• There is no separate user access control.

Summary¶

Adding a table prefix or suffix during migration is the easiest and most commonly used option. However, if you have a large number of tables in each folder, the list on the server can become overwhelming.

If your application requires different credentials for different customer sets, separating the tables by schema is a good choice that avoids the connection overhead of multiple databases.

If load balancing or strict data isolation is important, using separate databases provides the strongest separation at the cost of additional administrative overhead and connection context switching.