When organizations choose to replace commercial software with open-source alternatives, their IT departments often face the task of database conversion. This procedure involves transforming data, meta-objects, stored procedures, functions, and triggers from the source format of database management system (DBMS) to the target. This process typically involves modifying the application layer, mapping data types, and adjusting SQL statements.

There are several reasons why organizations opt for database either manually or using the appropriate database converter:

• Cost of Ownership: Commercial platforms have expensive license fees and strict licensing terms. Most of extensions and ad-ons are subject of extra charges. Open-source systems provide similar capabilities for free.
• Customization: Open-source products come with a lot of extensions, frameworks, and APIs for database management and development purposes. Most of these items are available free of charge.
• Flexibility: Open-source DBMS can be used with on-premises or Database-as-a-Service (DBaaS) providers like Amazon AWS or Google Cloud that dependencies on particular vendor.

Manual migration between DBMS having different features and SQL syntax can be a challenging and time-consuming task. The best practices for database conversion involve the following steps:

• Assessment: This phase focuses on evaluating the compatibility of the source and target systems and analyzing the necessary architectural modifications.
• Migration of schemas and meta-objects: In this phase, the database schemas and related meta-objects, such as tables, views, indexes, and constraints, are transferred to the target DBMS.
• Functional and performance testing: Once the schema migration is complete, thorough testing is crucial to ensure that the migrated database functions correctly and meets the required performance standards.
• Data Migration: This step involves transferring the data from the source database to the target database, including mapping data types, handling data conversions, and ensuring data integrity.

To simplify those phases IT teams usually use the special database converter that is able to automate most of the required actions. To choose the most suitable database converter among many alternatives, it is important to understand specific of the most important processes involved in the migration.

Converting Database Meta-objects

Database meta-objects include schemas, table definitions, constraints, indexes, and relationships between tables (also known as foreign keys) and this is extremely important part of the migration. The database meta-objects are analyzed to determine the necessary changes for the target environment. Migration scripts must be tested on development or staging environment before releasing in the production to ensure they accurately reflect the desired table definitions and do not cause any data loss or inconsistencies. After testing migration scripts is succeeded, these statements may be run against the target database to create the all table definitions, constraints, indexes, foreign keys and other meta-objects.

Once the migration is completed, all database meta-objects are validated in the target environment to ensure they meet the functional requirements and are integrated with the application layer properly. Database converter must carefully handle type mapping, and preserving properties of all meta-objects being converted in order to guarantee full compatibility of the source and resulting databases.

Converting the Data

For data migration, various methods are available: snapshot, parallel snapshot, and change data replication (CDR).

The essence of snapshot method is data migration from the source to the target DBMS through the single operation. However, this approach prevents write operations on the source database during the snapshot process and may cause significant overhead due to bulk data reading.

The parallel snapshot approach splits the source data into fragments and migrate those fragments simultaneously. Although this technique reduces the total time required for snapshot generation and the downtime window for the source database, some downtime is still necessary.

Besides the two approaches specified above, change data replication techniques can be used for database migration in order to reduce downtime of the source system as much as possible. This technique is based on real-time monitoring changes in the source database and applying them to the target. Basically, there are two popular implementations of CDR: trigger-based and transaction log. The trigger-based approach creates triggers on insert, update, and delete operations to capture the changes and propagate them to the target database. The transaction log approach relies on analyzing the transaction log to discover all changes in the source database.

In contrast to snapshot methods, CDR techniques of data migration reduce the overhead on the DBMS and network traffic by capturing and applying only the changed data instead of reading and transferring bulk data.

Advanced database converter must implement either parallel snapshot or change data replication approach to decrease required downtime of the original database management system.

Robert Kyles