Database migration is the process of transferring databases across different storage types, formats, or platforms. Businesses and organizations may need to implement database migration for a variety of reasons, including server or storage equipment replacement, maintenance or upgrade, application transfer, website consolidation, and data center relocation. This procedure involves transferring all database objects and related properties, including table definitions, data, indexes, constraints, views, triggers, stored procedures and processes, as well as user accounts, roles, and permissions from source DBMS to the destination.
Databases may be moved manually or programmatically using specialized software referred to as database migration tools. Typically, a manual method entails the following steps:
- SQL statements are used to export table definitions, indexes, and constraints from the source database.
- translate these statements to the target database’s format and import them
- export data from the source database to a temporary storage location, such as comma separated values (CSV) files.
- convert the data to the target format and load it into the target database
- extract views, stored procedures/functions, and triggers in the form of SQL statements and code from the source database
- translate these statements and code to the target database’s format and load them
Immediately after loading to the new program, the completed data must be examined to see if the database was transferred successfully, is complete, and has the necessary support for the new system’s programs. Verification process may require both the source and destination database systems simultaneously to identify areas of discrepancy and avoid data loss or damage.
Approaches to Data Migration
Main challenge of data migration is reducing the required downtime of the source database to achieve tolerance of the particular business model. Of course, the more high loaded system the less downtime frame is acceptable. This section explores 3 most common approaches to data migration with their own cons and pros: snapshot, parallel snapshot and change data replication.
- Snapshot method is the most straight forward one. It means taking a snapshot of the entire source table state and replicating it to the target database. All the source data is tranferred to the target table at once, and no WRITE operations are permitted on the source table while the snapshot is processing.
- The parallel snapshot method means splitting the data into chunks and snapshots of each chunk are made and trasferred simultaneously though parallel running threads. This approach significantly reduces the downtime window, yet it still exists and may not be acceptable for high-loaded environments.
- Change Data Replication (CDR) is a technique that tracks and stores incremental changes to a source database to replicate those changes to other databases in near real-time. There are two techniques of change data capture: trigger-based and transaction log. First one is supposed to create triggers on insert, update and delete for each table being migrated. Those triggers track changes of the source tables in special journal, then all changes are replicated into the destination table. Transaction log CDR uses special transaction logs of the source DBMS for designed for backup and recovery purposes. Those logs contain all required information about changes in the source database. Therefor, CDR database migration software can extract and replicate all changed data into the target database. CDR has become the ideal solution for low-latency efficient database migration and synchronization between relational on-premises or cloud databases in high-load environments.
Database Migration Software
For large and complex database migration projects, it is better to do them programmatically in order to create an automated process flow and relieve human resources of difficult work. It is critical to choose the appropriate technology to migrate the database within the specified timeframe and without data loss or corruption.
The following is a list of major functionalities that may be enabled with the usage of superior database migration tools:
- All latest versions of the source and destination database management systems are supported
- All major database objects such as table definitions, indexes, constraints and views are processed
- For future usage, conversion settings must be saved in a profile.
- The ability to change type mapping, conversion rules, and so forth
- Command-line support is a valuable advantage to script and schedule the migration
- Detailed manuals and a 24-hour support service
Numerous software companies provide specialized resources for automating database migration. Intelligent Converters is one of these firms, having been in the database conversion, migration, and synchronization business since 2001. They provide a range of conversion tools that facilitate database migrations across popular database management systems such as PostgreSQL, MySQL, Oracle, SQL Server, Azure SQL, Microsoft Access, FoxPro, and SQLite.
Intelligent Converters software uses efficient low-level techniques of reading and writing the data combined with parallel chunks multi-threading algorithms specified above. This allow to read high performance about 10 millions of rows (500 megabytes of data) per second on an average modern hardware platforms. Besides, the option to filter migrated data through SELECT-queries makes the database migration even more flexible since it allows to exclude or rename columns, transform the data and merge data from multiple tables into a single one.