Command-line Migration Scenarios¶

First-time migration¶

First-time migration with either of the command-line tools is accomplished in a matter of hours without the need to recompile or rewrite code. You can run a full migration:

MdsMigrateTable AllTables Full /ddf c:\myapp\MyDDFs /server mssql\sql2008 /database MyAppDB /schema c001 /dll sql_btr.dll

MigrateUniPaasData Full –dll sql_btr.dll -datalocation c:\myapp\data -magicini c:\myapp\magic.ini -server mssql\sql2008 -database MyAppDB -schema c001

Or, you can break up the procedure to allow the database administrator more control over the migration process.

Separating the Steps with MdsMigrateTable

1. Create the tables on the server:

MdsMigrateTable AllTables CreateTable /ddf c:\myapp\MyDDFs /server mssql\sql2008 /database MyAppDB /schema c001 /dll sql_btr.dll

Or, you can export an SQL script to be run on the server directly. This method is usually preferred by database administrators.

MdsMigrateTable AllTables ScriptOnly /ddf c:\myapp\MyDDFs /server mssql\sql2008/database MyAppDB /schema c001 /dll sql_btr.dll

2. Create the intermediate (INT) file for each Btrieve file that is to be converted to the target database. The intermediate file contains information used at runtime by the Mertech database driver.

MdsMigrateTable AllTables IntOnly /ddf c:\myapp\MyDDFs /server mssql\sql2008 /database MyAppDB /schema c001 /dll sql_btr.dll

3. Load the data on the server:

MdsMigrateTable AllTables CopyData /ddf c:\myapp\MyDDFs /server mssql\sql2008 /
Database MyAppDB /schema c001 /dll sql_btr.dll

Or, another process can be used to load an initial set of data for deployment.

4. Save a copy of the INT files and take a snapshot of the database. This becomes version 1 of your deployment and will be used in the future for updates.

Separating the steps with MigrateUniPaasData

1. Create the tables on the server:

MigrateUniPaasData CreateTable –dll sql_btr.dll -datalocation c:\myapp\data -magicini c:\myapp\magic.ini -server mssql\sql2008 -database MyAppDB -schema c001

Or, you can export an SQL script to be run on the server directly. This method is usually preferred by database administrators.

MigrateUniPaasData ScriptOnly –dll sql_btr.dll -datalocation c:\myapp\data -magicini c:\myapp\magic.ini -server mssql\sql2008 -database MyAppDB -schema c001

2. Create the intermediate (INT) file for each Btrieve file that is to be converted to the target database. The intermediate file contains information used at runtime by the Mertech database driver.

MigrateUniPaasData INTOnly –dll sql_btr.dll -datalocation c:\myapp\data -magicini c:\myapp\magic.ini -server mssql\sql2008 -database MyAppDB -schema c001

3. Load the data on the server:

MigrateUniPaasData CopyData –dll sql_btr.dll -datalocation c:\myapp\data -magicini c:\myapp\magic.ini -server mssql\sql2008 -database MyAppDB -schema c001

Or, another process can be used to load an initial set of data for deployment.

4. Save a copy of the INT files and take a snapshot of the database. This becomes version 1 of your deployment and will be used in the future for updates.

Schema update¶

A common scenario is for a developer to make changes to the database on his own machine and commit the changes to the SQL server in the development environment. The problem is how to deploy the changes to customer sites so no data is lost and everything remains in sync. The developer must send the following information to a customer site before updates can go live:

• His new application

• Updated INT file(s)

• SQL script(s) for restructuring the database

Schema Update with MdsMigrateTable

1. Migrate the updated schema to a v2 database in the development environment.

MdsMigrateTable AllTables CreateTable /ddf c:\myapp\MyDDFs /server mssql\sql2008 /database MyAppDB /schema c001 /dll sql_btr.dll /config c:\myapp\MyApp.xml

2. Use an SQL Diff tool to compare the v1 and v2 databases and to create a script that can update the v1 database to the v2 database (for instance, to add new tables, columns, indexes, etc.)

3. Generate new INT files.

MdsMigrateTable AllTables IntOnly /ddf c:\myapp\MyDDFs /server mssql\sql2008 /database MyAppDB /schema c001 /dll sql_btr.dll

4. Deploy the new application and updated INT files to the customer site and run the SQL script to update the server.

Schema Update with MigrateUniPaasData

1. Migrate the updated schema to a v2 database in the development environment.

MigrateUniPaasData CreateTable –dll sql_btr.dll -datalocation c:\myapp\data -magicini c:\myapp\magic.ini -server mssql\sql2008 -database MyAppDB -schema c001

2. Use an SQL Diff tool to compare the v1 and v2 databases and to create a script which can update the v1 database to the v2 database (for instance, to add new tables, columns, indexes, etc.)

3. Generate new INT files.

MigrateUniPaasData INTOnly –dll sql_btr.dll -datalocation c:\myapp\data -magicini c:\myapp\magic.ini -server mssql\sql2008 -database MyAppDB -schema c001

4. Deploy the updated INT files to the customer site and run the SQL script to update the server.

How the command-line tools can work together

You can use the MigrateUniPaasData utility to generate an XML script to pass to MdsMigrateTable, which can do the work later. Separating the steps (schema extraction and migration) allows you to tweak the configuration or perform the data migration without access to the uniPaaS source. For example:

1. Use MigrateUniPaasData to export an XML definition of the database.

MigrateUniPaasData XML MyApp.xml -datalocation c:\myapp\data -magicini c:\unipaas\users\me\magic.ini

2. Modify the XML file if you want (to perhaps trim down the list of tables in the file).

3. Use MdsMigrateTable tool to continue the migration, creating the tables and INT files and copying the data.

MdsMigrateTable AllTables CreateTable /server mssql\sql2008 /database MyAppDB /schema c001 /dll sql_btr.dll /config MyApp.xml

4. Use MdsMigrateTable tool to copy the data.

MdsMigrateTable AllConfig CopyData /server mssql\sql2008 /database MyAppDB /schema c001 /dll sql_btr.dll /config MyApp.xml