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. Note that MariaDB is largely compatible with MySQL, so most tools that support MySQL will work with MariaDB.

Export Your Existing Database

There are many tools available that can do this but we suggest using RazorSQL. Its key feature is the ability to connect to almost any existing database (e.g. MS SQL, Postgres, MS Access, etc.) and generate SQL export statements that are compatible with MariaDB. What you should end up with is a .sql file that contains human-readable text that can be used to recreate the db in AppSynergy.

Tweak the Exported .sql File

If you are working with multi-GB .sql files then common text editors like Notepad and Notepad++ will not be able to edit the file. We suggest using EmEditor if your .sql files are large.

The following datatype tweaks are typically needed for compatibility with AppSynergy:

• Columns longer than VARCHAR(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.

Import the .sql File

You can send the .sql file to AppSynergy support and request an import, or you can do the import yourself.

To import the file yourself you will need to install the MariaDB command line tools on your local machine. They can be downloaded here.

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

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

After importing you will need to modify your AppSynergy database as follows:

• Numeric Primary Keys. You must convert numeric primary keys to the AutoKey datatype using the AppSynergy UI. AutoKey columns (unlike AUTO_INCREMENT columns) support key value reservations and are central to AppSynergy functionality. (AutoKeys are similar to SEQUENCEs but implemented differently.)
• Numeric Foreign Keys. You must convert numeric foreign keys to the AutoKey datatype using the AppSynergy UI. You must also link foreign keys to their primary key via the AppSynergy UI to enforce referential integrity and enable critical UI functionality. AppSynergy is unusable without properly defined foreign keys.
• BOOLEANS. Any column defined as TinyInt, SmallInt, MediumInt, Int, or Integer that actually only contain the value 0, 1, or NULL should be changed to the CheckBox datatype via the AppSynergy UI.
• 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 itself. 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. We have automated bulk import tools.