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.

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.

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.

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.

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.

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.

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.