By 
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.
Comments