Migrating from non-Mertech Drivers

Flex2SQL includes features that allow users of DAW (Data Access Worldwide) Connectivity Kits (CK) to switch to Mertech drivers. Why switch to Mertech drivers? Mertech drivers:

  • Overwhelmingly outperform DAW’s drivers in real world environments.

  • Have an intelligent data fetching mechanism that determines the optimal number of rows and columns to fetch, boosting performance while reducing network traffic.

  • Can handle recovery from connection issues.

  • Include optimized queries that eliminate driver cache files, which can result in stale structure caching.

  • Keep all opened file information in memory for faster switching between application views.

  • Provide the ability to store large amounts of data in LOB columns and to fetch it in pieces to bypass the 16K field limit in DataFlex.

  • Support SQL Server partitioning, which can speed up SQL Server performance when there is a large number of concurrent users.

Two options are available for switching to Mertech drivers:

  • Performing a complete migration, re-creating tables for Mertech drivers.

  • Simply regenerating the .INT files and replacing the DAW CK dll with Mertech’s database driver. This option is available for users who want to switch from DAW’s CK for MS SQL to Mertech’s MS SQL driver.

Performing a Complete Migration

The Flex2SQL Classic Migration Utility uses the DAW CK driver to read the current data files and migrates the data to the selected target SQL database. The key point to remember is that both the DAW CK driver and the Mertech drivers use INT files to store information. In order to do a safe migration, you have to ensure that your current INT files are properly backed up.

Important

Before starting the migration, make sure the .INT files currently in use with the DAW CK are backed up properly, so they are not overwritten by the .INT files created by Flex2SQL. If the DAW CK .INT files are over written you cannot revert back to your old system.

The example below shows the steps required to migrate from an existing MS SQL database to a new MS SQL database using Mertech’s Flex2SQL driver. These same steps can be used to migrate from Pervasive.

Setup

Before starting the migration, copy the DAW CK dlls and license files for MS SQL or Pervasive into Mertech’s Flex2SQL bin directory. This ensures that Mertech’s Flex2SQL tool is able to load these drivers and read the source database correctly.

Migration Steps

  1. From the Flex2SQL Classic Migration Utility, log into your target database server using any of Mertech’s Flex2SQL drivers for that target database server.

  2. Open your DataFlex filelist (File > Open).

Your tables have an Mssqldrv: or DFBTRDRV: prefix since they were migrated with the DAW CK.

NonMertech Migration1.png

  1. Select the Migrate Non-DataFlex Files checkbox at the bottom of the Filelist dialog box.

  2. Checking this option enables the .INT File Directory text box. Browse to the location where your current .INT files reside.

  3. Double-click a table in the Filelist dialog box to verify that the setup and license files are correctly installed.

The table should open and you should be able to view the data. If there are any errors in loading the DAW CK or with licensing, the file will not open correctly and an error will be generated.

  1. Select all the tables you want to migrate.

  2. Click Convert Database.

  3. If you’re migrating to SQL Server, a notification message displays recommending you use the Generate INT from DAT option Click OK, you can ignore this message.

  4. The Convert DAT to Table dialog box displays. Modify the default settings if desired, and then click Convert All.

After the migration, the prefix in the Filelist dialog changes to a Mertech driver name.

  1. Double-click a migrated table to browse the data. You can also view the migration report (Reports > Migration Report) to verify that the migration was completed successfully.

Note

You can also use this option to migrate between SQL Servers using Mertech Drivers, for example, Oracle to PostgreSQL.

Switching MS SQL Drivers without Migration

You can switch the MS SQL database driver in two ways:

  1. By doing a complete migration as described in the previous section.

  2. By simply regenerating the .INT files and replacing the DAW CK dll with Mertech’s database driver. Mertech’s drivers will continue to work with DAW CK specific features such as computed columns and their table structures.

The second option is the simplest. You will see immediate performance gains by just switching to the Mertech drivers. This option is good for situations where downtime is unaffordable.

The first option is listed because you can see added performance gains (15-20%) if the tables are created using Mertech’s Flex2SQL tool. The reason for this is that there are some slight differences in the data types that are chosen and how the tables are created.

If using MSSQL and your table has IMAGE or TEXT columns, it is recommended to change them to VARBINARY(MAX) and VARCHAR(MAX), respectively, since Microsoft has marked the original types as deprecated.

Important

Before you begin, back up the.INT files that were created from DAW tools. Mertech’s migration tool needs the DAW driver to read the database information and generate its own INT files. If the files are not backed up or not put at a different location in the path, then the old INT files are overwritten, causing you to lose your overlap and relationship information.

To use this feature, you need the Microsoft SQL Server Connectivity Kit from Data Access Corporation. Flex2SQL will generate new .INT files from the DAW .INT files, so you can use the data you previously migrated using DAW CK.

Migration Steps

  1. Perform steps 1 through 6 in the previous section to select all the tables you want to migrate.

  2. Select Tools > Generate .INT >Generate .INT from .DAT.

The Create Intermediate File dialog box displays.

Create Intermediate File.png

This dialog box is similar to the Convert DAT to Table dialog box except it only generates INT files, it does not migrate the tables. Options selected here are added to the INT files.

  1. Click Generate All.

Once the process is completed, the prefix in the Filelist dialog changes to a Mertech driver name.