Best Practices of Database Migration

Database migration is an extremely important strategic step ensuring the data infrastructure aligns with evolving requirements of the application layer. The most common reasons for database migration are:

• Performance. As a business or organization grows, database performance requirements may change. Migration to another database management system may improve performance and overall scalability of the database.
• Total Cost of Ownership. Many companies are trying to reduce their expenses on IT infrastructure. Database migration from commercial systems to advanced open-source DBMS such as MySQL and PostgreSQL is a solutions for that task.
• Capabilities. Different database management systems offer unique feature sets. For instance, two most popular open-source DBMS MySQL and PostgreSQL have cons and pros making each of them more suitable for some specific tasks than other one. Read article MySQL vs PostgreSQL for more information.
• Vendor Lock-In. Some proprietary DBMS like Oracle and SQL Server are limited in using 3rd party tools and solutions. Database migration to open-source alternatives helps to avoid that vendor lock-in.

Regardless of the particular reason for a database migration, the overall goal is always to enhance performance and reliability. This article exposes some of the best practices of database migration that can help you to make the process smooth and safe.

When planning the database migration, try to choose the latest version of the target database (if the migration project does not explicitly require old version). Here are the key reasons for this suggestion:

• Database vendors often release new versions addressing recently discovered security threats and vulnerabilities. By migrating to the latest version, you minimize the risk of data breaches and other security incidents.
• Each new release contains new features and capabilities and so you can take advantage of these new functionality by migrating to the latest version.
• Most software companies are constantly improving the performance of their products. Following this way, it is expected that the latest version has better performance than previous releases. You can take benefits of these performance improvements when migrating to the latest version.

Assess the source database. The assessment helps you to better understand what exactly is migrated and how well it is complied with the target system:

• Number of tables with rough size classification (large, medium, and small)
• Need of handling data type and character-set conversions
• How many tables contain large object (LOB) data
• Rough estimation of the time required for migration

If you find any issues (rows duplication, outdated data, etc), it must be resolved before the database migration. This step may require special software tools due to huge scale of the work.

Read the documentation. Go through the documentation for the source and target DBMS before migration. This can help identify the prerequisites and limitations of the project as well as troubleshoot potential issues.

Chose the migration tool that fits your project. There are many tools that can automate (completely or partially) the database migration. Consider the key points of the migration project when choosing the automation tool:

• acceptable downtime for the source system
• size and complexity of the database
• need of migrating the database logic (stored procedures, functions, triggers), etc.

Intelligent Converters offers efficient and reliable database migration tools for all popular RDBMS: PostgreSQL, MySQL, Oracle, SQL Server, IBM DB2, MS Access as well as cloud platforms like Azure, Amazon RDS, Google Cloud.

Run the proof of concept. Test the migration on small subset of the data. Run sufficient performance and functional tests to make sure the target database works properly. This approach helps to find potential issues at the very early stage of database migration.

In conclusion, there are some technical recommendations for the database migration itself:

• Backup the data before migration. If something goes wrong, you will not lose the data. Make sure that backed up copy is up-to-date.
• Schema migration. Make sure you properly convert all the source data types into the equivalents for the target database based on the range of acceptable values.
• Do not migrate tables with their foreign keys (FKs), constraints, and indexes. All these database entries slow down importing data into the target database. You can create all these elements after the data has been migrated.

Have questions? Contact us