Data Migration Guide

If you have an existing database that you want to migrate to AppSynergy, this guide is for you. If you require ongoing read/write integration with another system we recommend that you explore AppSynergy's Federated Data Integration capabilities instead. If you only need to import a few CSV files please see File > Import Data....

AppSynergy uses MariaDB for its SQL engine (with various enhancements added, like AppSynergy's Federated features). MariaDB is largely compatible with MySQL, so most tools that support MySQL will work with MariaDB.

Migration Overview

The sections below cover the following steps in detail:

1. Export Your Existing Database into MySQL Compatible SQL Statements
2. How To Edit Large SQL Files
3. Optimal Datatype Mappings for AppSynergy
4. Importing the Edited SQL File Into AppSynergy

Export Your Existing Database into MySQL Compatible SQL Statements

There are many tools available that can do this, such as MySQL Workbench's Migration Wizard, but RazorSQL is a more flexible choice. The key feature is the ability to connect to almost any type of existing database (e.g. Sybase, Oracle, MS SQL, MS Access, plus many others... see site for details) and generate SQL statements using MySQL compatible syntax (regardless of the source syntax). RazorSQL can do this.

You will inevitably want to edit the .sql file that is generated to tweak a few things before importing into AppSynergy; see the following section for details.

How To Edit Large SQL Files

If you are working with a large SQL database you will quickly find that editors like Notepad and Notepad++ are not able to open or edit large .sql files. One editor that can cope with multi-GB .sql files is EmEditor. This editor will allow you to open very large .sql files and tweak the syntax as needed.

Optimal Datatype Mappings for AppSynergy

We suggest that you edit the .sql file before import and tweak the datatypes for optimal compatibility with AppSynergy. Some of these changes you can also make post-import using the AppSynergy UI.

• Numeric Primary Keys. A numeric primary key should be defined as BIGINT without AUTO_INCREMENT. You must also later convert the column to AutoKey (an AppSynergy subtype of BIGINT) using the AppSynergy UI. AutoKey columns (unlike AUTO_INCREMENT columns) support key value reservations and are central to AppSynergy functionality. You can do this after import if you prefer.
• Numeric Foreign Keys. A numeric foreign key should be defined as BIGINT (to match its primary key). You must also later convert the column to AutoKey (an AppSynergy subtype of BIGINT) using the AppSynergy UI. You can do this after import if you prefer.
• VARCHAR Columns Longer than 255. Any column longer than VARCHAR(255) should be defined with the TEXT datatype. AppSynergy renders the TEXT datatype as a scrollable Notes field. We recommend that you do this before import so as to avoid potential excess column length errors.
• BOOLEANS. Any column defined as TinyInt, SmallInt, MediumInt, Int, or Integer that actually only contain the value 0, 1, or NULL should be defined with the BOOLEAN datatype. AppSynergy renders the BOOLEAN and TINYINT datatypes as CheckBox fields. You can do this after import if you prefer.
• Binary datatypes. Not supported by AppSynergy. You can store them in the database but AppSynergy can't use them. AppSynergy has Document Fields, Image Fields and Signature fields, but these datatypes store their binary data in a cloud storage bucket external to the database and store only a reference to that file in the database. If you need to bulk import a large number of images or documents and link them to Image or Document fields in your database please contact AppSynergy support for assistance.

Importing the Edited SQL File Into AppSynergy

First, you will need to install the MariaDB command line tools on your local machine. They can be downloaded from here. You may also use the MySQL tools if you prefer (the commands and syntax are essentially identical).

Second, create an AppSynergy API Key via Tools > API Keys... and assign the API Key user the Administrator role via Tools > Database Users...

Third, test that you can connect to your AppSynergy database and issue basic SQL commands:

Finally, you can import the .sql file you created above as follows: