Sign In

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.

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:

mysql --host=API_KEY_SERVER_HOST --database=API_KEY_DATABASE_NAME --user=API_KEY_USER_ID --password=API_KEY_PASSWORD

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

mysql --host=API_KEY_SERVER_HOST --database=API_KEY_DATABASE_NAME --user=API_KEY_USER_ID --password=API_KEY_PASSWORD < myfile.sql