Database Migration

Database migration is the process of transferring schemas, data and other entries between storage types, formats, or computer systems. Companies and organizations migrate their databases for a variety of reasons:

Server or storage equipment replacements
Server maintenance or upgrades
Application migration
Website consolidation
Data center relocation.

The database migration involves transfer of all major objects and categories:

Table definitions
Data
Indexes
Constraints
Views
Triggers
Stored procedures
Functions
User accounts, roles and permissions.

Database migration can be implemented manually or programmatically by using special software tools.

Manual Database Migration

Manual approaches to database migration usually consists of these steps:

Exporting table definitions along with indexes and constraints from the source database in form of SQL statements
Conversion of these statements into the destination format and importing to the target database
Exporting data from the source database into an intermediate storage like comma separated values (CSV) files
Transforming the data according to the destination format and loading it to the target database
Extracting views, stored procedures/functions and triggers from the original database in form of SQL statements and code
Conversion of these statements and code into the destination format and loading into the target database

Data Verification

After loading into the new system, results are subjected to data verification in order to determine if the data has been accurately translated, it is complete and supports processes in the new system. To verify the results of database migration, it is suggesteda parallel running both systems to identify areas of disparity and erroneous data loss.

Programmatic Database Migration

For large and complex projects,database migration is usually performed programmatically to achieve an automated process flow, freeing up human resources from tiresome tasks. It is very important to select the appropriate tool to migrate the database in the required time frame without data loss or corruption.

Here is list of main features that must be provided by high quality database migration tools:

Support for the most popular versions of source and destination DBMS
Table definitions, indexes and constraints are converted with all necessary attributes
Migration settings are stored into a profile to simplify next use
Full Unicode support
Options to customize types mapping, conversion rules, etc
Command line support
Comprehensive documentation and 24/7 support service

There are a large number of software companies that offer dedicated tools to automate database migration. Intelligent Converters specializes in database conversion, migration and synchronization since 2001 and has been developing wide range of conversion tool to migrate databases between all popular database management systems like MySQL, PostgreSQL, SQL Server, Azure SQL, Oracle, Microsoft Access, FoxPro, SQLite.

Best Practices of Database Migration

There are number of prerequisites suggested by the database experts. The following steps allow to protect the data and make migration smooth and safe:

Backup the source database before migration. You will need this restore point in case something goes wrong.
Spend essential time for database assessment. This step may help you to avoid migrating redundant data or logic (stored functions, procedures, triggers) as well as optimize overall performance of the resulting database.
De-duplicate the data before migration, it will help to reduce amount of data to transform and transfer.
Keep appropriate order of tables and views during the database migration to avoid conflicts on dependencies.
Validate the migrated database thought extensive performance and functional testing.