Migrating a Database? Consider These Factors First

Migrating one database to another actually involves migrating the schemas, tables, and data. The software itself is not migrated. 

Database migration could be homogeneous or heterogeneous. Homogeneous migration of a database is migration from one platform to another, such as from a local platform to a cloud platform. Heterogeneous migration is migration to a completely different database engine, such as from PostgreSQL to MySQL, or from a relational database to a NoSQL database.

There are several reasons you’d migrate a database: 

• To create a staging database for testing and development
• To consolidate multiple source databases to a single database
• To avail the benefits of cloud based database as a managed environment that does not require a user to provision the infrastructure (and, depending on the level of support provided by the cloud service, a user may not need to manage database installation, configuration, and administration, either)
• To take advantage of features a new database provides—for example, one database could support some data types (such as JSON, BLOB, and CLOB) that another doesn't

Whatever the reason, there are several things you should take into consideration first.

Migrating DDL and DML

One of the most important choices in migrating a database is that what type of database objects (tablespaces, schemas, tables) are to be migrated. When migrating complete tablespaces, schemas, and tables, the DDL (Data Definition Language) for each database needs to generated. If data is to be migrated too, the DML (Data Manipulation Language) for tables also needs to be generated. 

Mapping SQL Syntax

SQL Syntax, both for DDL and DML statements, could be slightly different between the source and target databases. The DDL and DML generated on one database engine may not run as such on another database engine, so the SQL statements need to be mapped from one to the other.

Mapping Data Types

Data types for source and target databases could be different. For example, MySQL does not support a Boolean data type. When mapping a Boolean or bool data type from another database engine, the equivalent in MySQL is TINYINT(1). Numeric and fixed data types are mapped to DECIMAL in MySQL.

Transforming Tablespaces, Schemas, Tables, and Columns

Tablespace, schema, table, and column names may need to be transformed if one database supports case-sensitive names and another doesn’t. For example, MySQL converts all table names to lowercase on storage and lookup. Other transformations that could be applied are renaming a database object (schema, table, or column), adding or dropping a table column, and adding a prefix or suffix to a database object name.

Target Tables

Another migration consideration is preparation of the target tables. Several options could be used if target tables already exist:

• Keep the same: Data and metadata of the target tables are not changed
• Drop tables on target: Existing tables, if any, are dropped and new tables are created
• Truncate: Tables are truncated, which means the table data is deleted but the table metadata is not

Database migration is typically performed with a migration tool or service. But before you start migration, explore your options and take these considerations into account.