Advanced migration of schema and data across multiple databases

Abstract

Migration of existing databases is an unavoidable task in modern technical and business environment. Most of the time, the DBAs are forced to work with a tight budget to choose the ideal software to accomplish the migration requirement of their organization and complete the process within a limited time period. When considering the commercial and freely available data migration tools we found a number of disadvantages. Some of the tools are very expensive. Therefore the management of small scale companies may not approve purchasing such software unless they and are convinced it's a necessary expenditure as well as a good investment for the future. Even though they offer many features, some of the tools are suitable only for cloud based migrations. Some of the tools can only perform the migration process, inwards. Some tools are not available as standalone programs, so the customer has to purchase the whole software package which the required tool is included. Some tools don’t provide GUI for the users. Therefore the user must have good programming knowledge in order to work with it. In order to overcome these issues, I. M. Wijewardana presented a solution which offers an open and extensible migration process with attractive and user-friendly interfaces. However there are some important features yet to be implemented, including the features which have been suggested as future work by Wijewardana and colleagues. With this research, we have extended the work of I.M. Wijewardana and implemented the following features. Handling the effects of primary key change of a table in between two data migration sessions. Informing the relevant details to the user or take action based on user preferences in an event of existing data replacement. Incremental update of data: Facility to update tables column-wise as well as row-wise, without causing any loss of data. Handling changes when the number of columns in a table (in source database / target database) gets increased / decreased. Handling changes when the constraints of table columns get changed. Facility to migrate the parent tables of a table which were initially selected to migrate based on relationships between the tables. Handling table and column name changes, column mapping, data type mapping. Migrating data based on different criteria (Data selection via SQL queries). Rollback database to a stable / pre-migration state in an event of unsuccessful data migration