Data migration with SQLWays: A Step-by-Step Guide

Data migration is the transfer of data from one location to another. While it sounds simple, there are many complex processes behind the scenes.Many customers are unaware of what they will have to deal with when starting a data migration project.

Today, organizations manage enormous amounts of data. Modern databases and data warehouses provide several features that improve data management, contribute to decision-making, provide valuable insights, and enhance customer experience.

As the variety of databases is endless, there is often a need to transfer data from one storage to another. This is where data migration comes into play.

In this article, we will examine data migration using SQLWays, considering its features, how the tool simplifies the migration process, and its advantages.

What is SQLWays?

SQLWays is a comprehensive solution for automated database migration. It enables businesses to seamlessly move from outdated databases to modern platforms, consolidate multiple systems, or easily switch between database vendors. The product covers every stage of the migration process: migrating data and database schema, including the transition of stored procedures, functions, triggers, etc. SQLWays ensures a smooth and efficient transition with minimal manual effort.

The tool provides the flexibility organizations need to migrate without compatibility concerns, as it supports more than 40 relational database management systems (RDBMS), including Oracle, Microsoft SQL Server, PostgreSQL, MySQL, IBM Db2, Google Cloud, Microsoft Azure, and more.

SQLWays helps businesses accelerate digital transformation and focus on growth rather than technical challenges. Whether moving to a cloud-based environment, consolidating databases, or upgrading to a more efficient system, SQLWays delivers a reliable, risk-free migration experience.

SQLWays: Key features for data migration

Let’s review the main features of SQLWays that make data migration smooth and seamless:

• Support for all data types. The tool fully supports all data types, including columns containing large volumes of data (LOBs).
• Conversion of indexes, primary and foreign keys by default.
• Granular data migration. Supports table and data migration separately and partial data migration using queries, allowing organizations to transfer only the necessary records.
• Flexible data mapping. Provides default data type conversion, allowing users to review how each type is mapped and modify conversions as needed.
• Advanced query migration. If a customer needs to modify the table structure in the target database, SQLWays enables table adjustments or generates queries to select specific tables for migration.
• Migration in Command Line Mode. Provides flexibility for advanced users to automate and script migration tasks, enhancing efficiency.
• Multithreaded migration. SQLWays makes it possible to run several streams of data migration to streamline the process even more.
• Data integrity ensured. SQLWays guarantees that data remains accurate and consistent throughout the migration process, preventing data corruption or loss.

Step-by-step Guide on using SQLWays for data migration

For this test migration, we have selected some related objects: two tables and one stored procedure that depends on the data from these tables. The procedure in question has a unique feature: it returns a result set consisting of multiple rows.

SQLWays contains a large number of settings to make migration as efficient as possible. However, the tool can migrate data with default settings.

To demonstrate SQLWays’ flexibility and its capabilities, we will define a set of specific requirements for the migration process:

• Ensure that objects are placed in the appropriate schemas in the target database.
• Preserve the case sensitivity of identifiers as they appear in the source database.
• Apply selective renaming to certain identifiers, such as table or column names.
• Modify data type mappings, for example, converting BIT and TINYINT to INTEGER.
• Retain the stored procedure as a procedure in PostgreSQL while acknowledging that an additional INOUT REFCURSOR parameter will be introduced due to PostgreSQL’s procedural handling of result sets.
• Enable a comparison between the original source code and the converted code before executing the migration.

Migration process

To implement these requirements, we will configure SQLWays as follows:

1. Procedure Conversion: In the Target Options page, within the advanced settings, select the method for converting procedures that return multiple rows via INOUT REFCURSOR (the default setting converts them to functions with RETURNS TABLE, but we will set it to Refcursor).



2. Global Schema Mapping abd Trace Mode activation: Define a global schema name for all objects. Enable trace mode to compare the original source code with the converted output.



3. Custom Schema Mapping: Since some objects need to be assigned to different schemas, apply local schema mapping for those specific objects.



4. Data Type Mapping: Adjust type mappings to ensure BIT and TINYINT are converted to INTEGER.



5. Case Preservation: To maintain case sensitivity in object names, as well as parameter names in procedures or functions, enclose identifiers in double quotes.
6. Execute the Migration: Run the conversion process with the specified settings.

Let’s open the conversion results and evaluate them:

• The converted file includes the source code as comments. It simplifies the comparison and verification of the converted code.

• Preserved case sensitivity. All identifiers are quoted to maintain case-sensitive naming conventions.

• Schema assignment. Schemas have been globally applied to all objects. Additionally, schemas have been correctly set for specific exceptions.
• Optimized data types. Data types have been adjusted based on predefined settings.

• Comprehensive type mapping. Global type mapping has been extended to dependent objects, particularly within procedures when declaring variables.

• Intelligent object renaming. Object renaming has been applied to the primary objects and all dependent objects that reference them.

Note: This process is most effective when dependent objects are converted together, maintaining the integrity of relationships within the database.

With these enhancements, the converted objects fully align with the required specifications. Success!

Data migration: Best practices

Data migration checklist

1. Preparation

• Assess the current database. Evaluate the existing database performance
• Define migration goals. Clearly defined goals are the backbones of any successful migration project. Determine why you are migrating the database. Is it about cost-efficiency, performance improvement or leveraging new features?
• Choose the right migration tools. Do research to choose the best migration tool for your needs. Remember that the tool you seek should support all the necessary data formats and database types (SQL or NoSQL). The data migration requires flexible data mapping, advanced query capabilities, and multithreaded migration to make the most out of migration with significant time savings.

2. Migration execution

• Backup the existing database. Security measures such as database backup are necessary to make the migration process secure and prevent data losses.
• Set up the new database environment. Verify that it is compatible with the existing database schema, data types, and other critical elements. Compatibility checks help prevent data corruption and loss during the migration process.
• Data transfer. Monitor the migration to detect errors, interruptions, or performance bottlenecks.

3. Post-migration tasks

• Test and validate. Conduct thorough testing of the migration results. Such tests allow you to ensure that all data has been migrated accurately and completely to the new environment. Also, check the performance of the new database to ensure that it meets the required benchmarks.
• Optimize. Adjust the database performance settings to ensure optimal performance. This may include tuning queries, indexing, and adjusting configuration settings.
• Update documentation. Since the database architecture changes, documentation should contain all the modifications implemented after migration. This may include schema changes, configuration settings, and any other processes that have been implemented.

Wrapping up

Data migration is a challenging process that requires an in-depth understanding, weighing pros and cons, and evaluating the risks involved. This article contains a comprehensive guide on using SQLWays for your data migration project.

SQLWays enables automating tasks, streamlining the entire data migration, and ensuring a smooth data transfer. Ready to start your data migration journey? Book a demo with the Ispirer experts and discuss the project roadmap.