View blogs | Login

How to Perform Database Migration?

Relational databases are an integral part of any company’s software stack and are widely used in the IT industry. There is hardly any company that does not use a relational database to store important data. It is a $32 billion industry that keeps growing every year.

Db migration

Most applications that use a relational database to store data are often very tightly integrated with the backend, making it challenging to migrate them to a different vendor. This article will help you learn how to overcome this challenge and make database migration between various RDBMS a breeze. It is divided into three sections.

  1. Understanding the problem - The first segment talks about the coding practices or errors that make it difficult to migrate to another database in the future.
  2. Learning a Better Approach To Database Programming For Easy Migration - The best practices for writing applications that make future database migrations easy.
  3. Database Migration - Step By Step Guide & Tools Required - In this segment, we will learn in detail - how to migrate from one RDBMS to another step by step and the tools required to do so efficiently.

Part 1 - Understanding the Problem

It is very easy to overlook long-term extensibility when developers have a deadline to meet for their current project. As a result, many developers write code that adheres to every functional requirement of the project but cannot decouple the database in the future. This practice makes database migration a highly complex task.

The following are several examples of what not to do when writing queries against a database.

Problem 1 - Merged Tiers

Consider the following pseudo-code written in a typical web application that accepts input from a web form and inserts data into a table within the database.

Set firstName = $FormValue(“fn”)
	Set lastName = $FormValue(“ln”)
	Set query = “INSERT INTO WebUser values(?, ?, GetDate())
	query.prepare(firstName, lastName)
	query.execute();

In the above example, a SQL query is being executed in the same part of the code that fetches the values from the HTML form. There are several problems with this code:

  • Repetitive code - Since the query to insert data is written in the same location, this code will be repeated in other areas with similar logic.
  • Data cleansing - It is often necessary to modify or clean the data before inserting it into the database. Usually, you don’t have to perform this cleanup in the beginning. However, a requirement may arise later as users start using the system. You will have to modify several locations if such a need appears later down the road.
  • Proprietary queries - Notice the use of the GetDate() method in the example above, which inserts the current date into the row. Every database does not support this syntax. Therefore, you will have to change every occurrence of this insert statement if you decide to migrate to a different database in the future.

Problem 2 - Merged Objects

This is similar to the merged tier problem discussed above but with a more granular insight. Consider the following billing object definition as an example:

class Billing(
			+ fullName
			+ address
			+ email
			+ sendMonthlyNewsletter()
			+ processPayments()
		)
		

The class above is responsible for two things:

  • Representing the data from a table.
  • Performing logical functions such as generating reports and processing payments.

Notice how the business logic is coded in the exact location where the data resides. As a result, it is very likely that server SQL queries with complex JOIN statements are also hardcoded inside the two example methods.

Mixing logical code with static data objects makes future migration very difficult, particularly when SQL queries are hardcoded inside the method. Imagine you switch from a database that is case insensitive to a different RDBMS where case matters. In such instances, you will have to modify and test all of your business logic to ensure the system’s integrity.

Problem 3 - Triggers and Stored Procedures

Triggers and Stored Procedures can be easy to implement and often render better performance. However, they tie you up with a specific RDBMS. The SQL dialect used across multiple vendors is often quite different from each other. Therefore, porting code from one backend to a different one is not easy when you need to migrate to a different database.

Part 2 - Learning A Better Approach To Database Programming For Easy Migration

It is important not to mix the code that fetches and modifies data with any other business logic in order to keep your system portable and extensible. One common practice to enforce such a rule is to use Object-relational mapping (ORM) tools. These tools facilitate mapping between tables and classes of any object-oriented language.

There are two types of ORM:

Dynamic

Tools that offer such mapping create dynamic objects that can be either queried through reflection or abstract methods. Consider the following example demonstrating a pseudo-code that uses such a library.

fn = db.getTable(“customer”).fetchPk(10).getField(“fn”)

In the above example, the variable fn gets its value from the customer table matching the specified primary key. The SELECT statement required to fetch the data is hidden from the caller and is generated dynamically by the ORM tool. These tools are often self-adjusting if the backend design changes. For example, there is no need to change existing code when a new column is added to a table.

Such tools are often embedded into third-party libraries, which could have licensing restrictions when deployed. Additionally, there may be a slight performance penalty at runtime, mainly if reflection is used extensively.

Several companies offer such tools, many of which are available in the open-source community. Here is a list of some of them, along with their supported language.

Tool Language
ActiveJDBC Java
Apache Cayenne Java
Dapper C#
NHibernate C#
CakePHP PHP
Django Python
DataMapper Ruby

Static

This approach involves generating code for classes based on the back-end design. The generated code is typically not tied to any library and does have any dependencies. You will have to create the code if the back-end design changes.

Since the generated code is static, typically, there are no runtime library requirements or licensing issues.

One such tool that enables automatic code generation is WinSQL, which can generate classes in different languages based on the tables.

Part 3 - Database Migration - Step By Step Guide & Tools Required

Switching the backend database to a different RDBMS becomes relatively more straightforward if your application is designed in a portable way, as described above. The following section lays out a step-by-step guide that you can follow to migrate from one database to another effortlessly. Migrating a database involves the following steps:

  1. Creating schema (tables and views)
  2. Creating security objects, such as users and permissions
  3. Creating triggers, stored procedures, and any custom objects
  4. Moving data

1. Creating Schema

The SQL language is about 90% similar across every database. However, the remaining 10% makes migrating from one database to another a tedious task. Many RDBMS offer reverse engineering the schema of an existing database.

The output of these tools is often a file containing SQL statements. Unfortunately, it is not always possible to run the exact script against a different database because of:

  • Datatype name mismatch - for example, Text verses CLOBS
  • Max size mismatch - for instance, max size of varchar in SQL server is 8000 whereas it is 32K in DB2
  • Data precision mismatch - for example, dates in some databases are specified in milliseconds while others go down to nanoseconds.

2. Creating Security Objects

Translating security objects is a tedious task, and unfortunately, there are very few migration tools. Although you could generate generic statements like GRANT permissions from the source database and run it against the target, no database will allow you to capture user passwords in a script. Therefore, creating user accounts on the target database usually requires human intervention.

3. Creating Triggers and Stored Procedures

Converting SQL dialect from one vendor from a different back-end is the most tedious step. The dialect of TransactSQL used in MS SQL Server is utterly different from PL/SQL in Oracle.

It is this step that prevents companies from switching the backend database to a different vendor. If possible, avoid writing complex stored procedures unless you are confident you will not be migrating to a different RDBMS in the future.

4. Migrating Data

Several tools are available in the market that can help you move the actual data from a database. There are two types of migrations:

  • Connected - You connect to both source and target databases simultaneously and pump data in the target while gradually reading it from the source.
  • Disconnected - You export data from the source onto a temporary medium such as a CSV (comma-separated values) or any custom format and then insert this data into the target system at a later time.

    Many RDBMS come with built-in mechanisms to import and export data to and from CSV files. This method works with simple data types but does not work with CLOBs, BLOBs, images, and other binary types.
WinSQL supports both connected as well as disconnected data migration.

Getting Help From WinSQL

WinSQL, besides being an excellent querying tool, helps programmers and DBA when performing data migration tasks. On one hand, It can help programmers adhere to best practices when writing their code. On the other hand, it could help administrators move schema and data.

Generating Code

Programmers can generate thousands of lines of code with just a few mouse clicks. This generated code represents static objects within a database increasing productivity as well as facilitating future database migration projects. The code generation feature in WinSQL generates 3 classes for every table:

  • ValueObject - This class contains field names and their getter and setter methods.
  • DataAccessObject - This class contains methods that allow changing the data, such as INSERT, UPDATE, DELETE.
  • ExtendedObject - This class acts as a placeholder for any additional logic that is applied to this table.

If the database design change in the future, all you have to do is to recreate the ValueObject and DataAccessObject. The following image display a class diagram of these classes.

Migrating Data

When it comes to data migration, WinSQL can not only help administrators migrate schema but also the actual data. Additionally, there are several methods of migration:

  • Offline Backup/Restore
  • - This feature allows you to backup a database and restore it to a different RDBMS. This will handle the data type conversions.
  • Connected Export
  • - The built-in Export Wizard allows you to connect to one database, run a SELECT query, and export the results to another table in a different RDB.
  • Disconnected Export
  • - Export data to a text delimited file, such as CSV. This exported file could later be imported into another table in a different RDBMS.

Summary

Migrating the backend RDBMS system is often a time-consuming and expensive task. However, with careful software design and the right tools, this task can become more manageable and save days worth of work.

Tools like WinSQL can help developers write tedious code and help move the actual data with proper data conversion. It can generate thousands of lines of code within seconds that are royalty-free and can be used in any application.


Created on: May 6, 2021
Last updated on: Jun 11, 2021

LEAVE A COMMENT

Your email address will not be published.

Navigation

Social Media

Powered by 10MinutesWeb.com