A Seamless Network Database Migration Tool for Insititutions in Zambia

The objective of the research was to efficiently manage migration process between different Database Management Systems (DBMS) by automating the database migration process. The automation of the database migration process involved database cloning between different platforms, exchange of data between data center and different clients running non-identical DBMS and backing up the database in flexible format, such as eXtensible Markup Language (XML). This approach involved development of a “Database Migration Tool”. The tool was developed on a windows platform using Java EclipseTM with four non-identical dummy Relational Databases (Microsoft Access, MySQL, SQL Server and Oracle). The tool was run in a controlled environment over the network and databases were successfully migrated from source to targeted destination option specified. The developed tool is more efficient, timely, as well as highly cost effective. Keywords—Database management system; database migration; database structure; database migration toolkits and database cloning


I. INTRODUCTION
Advancements in technology usually result in database migration, and an example would be for the National Pension Scheme Authority (NAPSA).A database (DB) is a persistent, logically coherent collection of inherently meaningful data, relevant to some aspects of the real world [1].A collection of these databases is what forms the Database Management Systems (DBMS).
Database Management Systems perform a wide variety of roles such as allowing concurrency, controlling security, maintaining data integrity, providing for backup and recovery, controlling redundancy, allowing data independence, providing a non-procedural query language as well as performing automatic query optimization.
Migrating a database involves migrating the tables and records from one database management system to another.The Transvive white paper, 2014 defines the term "Migration" as the movement of technology from older, or proprietary systems to newer, more versatile, feature-rich and cost-effective applications, and operating systems [2].Data migration is usually undertaken for the purpose of replacing, upgrading server or storage equipment for a website consolidation, so as to conduct server maintenance or to relocate a data center.
However, because different database management systems have different formats for storing the database, the exchange of database tables and records between different database systems usually results in compromising the quality, or authenticity of the data in the transformation process.
According to an Oracle White paper, 2011, up to 75% of new systems fail to meet expectations, often because of flaws in the database migration process, which in turn result in data that is not adequately validated for the intended task [3].Some of the challenges associated with database migration processes include data loss particularly in a case of Poor Legacy Data Quality, having Wrong Data Migration Tools, inadequate knowledge in using the precise Data Migration Tools, failure to Test and validate Data Migration Process, and Absence of Data Governance Policies [4].www.ijacsa.thesai.orgCurrently, there is no comprehensive system to comportment the data migration process.The existing procedure for migration is semi manual, and involves fragmentary procedures, which entails using different tools in order to achieve a comprehensive process.As such, maintaining the structure of a database when migrating it from one database management system to another is quite a challenging task for most Organizations.Often times, organizations have to design a new database for the different database management system it wants to adopt.This current system of database migration is not only costly, but it"s also ineffective, and may sometimes result in the loss of essential data in the migration process.This is because this system involves hiring a database designer every time the Organization has to switch to a different database management system.Therefore, by developing an automated database migration process, the challenges being experienced with the current migration process required to be addressed and eliminated.
This study intended to explore ways in which data migration process could be improved through the development of a new Seamless Database Migrator.This was expected to help overcome challenges associated with the network database, and deliver the data with such accuracy.Specifically, the new database migrator was expected to.
1) Eliminate the need for script writing when transferring tables in the database with the records.
2) Prompt the user to select the destination and source Database Management System, and specify what to migrate i.e. either the entire database with structure and data or just structure or data, thereby allowing different database management systems to exchange database tables and records in them, without any loss of data details in the transformation process.
The paper is organized as follows: Section 2 deals with literature review, which covers existing tools as well as the theoretical literature bordering on policy issues regarding database migration.The methodology is presented in Section 3. Section 4 brings out the results and the discussion of the baseline study conducted to identify challenges in the database migration process.System testing is presented in Section 5, and the last Section 6 contains the conclusion.

II. LITERATURE REVIEW
In order to cope with a fast changing business environment, it is necessary to update the technological infrastructure constantly, and database migration is a routine part of this technology.Barron.C et al. suggested that the core reason for the need of database migration is mainly to upgrade the existing system into a developed system that conforms to the Industry requirements [5].
The tasks of a migration workflow are diverse and complicated, executing all these processes manually requires plenty of time, as well as a highly experienced migration team in both the source, as well as the target system.In a paper, reviewing database migration strategies, tools and techniques, Elamparithi, M and Anuratha, V singled out relational database migration (RDM) as an example.The authors stated that relational database migration was always a complex, time consuming, and magnified process due to heterogeneous structures and several data types of relational database [6].
This gives rise to certain risks and challenges in the data migration process.
The Arbutus Software Whitepaper summarized the risks of database migration as follows: Unrealistic estimates of data quality, inaccurate, missing or out of date source system documentation, as well as the inability to reconcile the target systems data to the source system [7].
To counter the above challenges of database migration, businesses have seen the need to develop effective methodologies of migrating databases.Several migration tools and strategies have since been developed in the software industry [8].However, finding the effective methodologies for database migration still remains a challenge, and many current approaches to data migration suffer from a consistently low success rate.Arbutus Software White Paper approximates that between 70 and 90% of data migration projects either fail outrightly, or run over budget, with an average cost overrun of 10 times the original estimate [9].This is mainly due to the unplanned issues that often occur at the later stages of a project.
Several researches have since been undertaken to address some of the problems associated with database migration, although no absolute solution has come forth.For example, Joseph R. Hudicka, provided a complete solution of data www.ijacsa.thesai.orgmigration methodology, which deals with row counts, column counts and related statistics to the source databases [10].
However, the problem with this methodology is that it does not migrate null and numeric values and error occurs for key constrain keys.(NOAA), outlining the criteria that need to be considered when evaluating a DMT [13].However, while the criteria outlined by these authors may be adequate for the complex project of developing DMT, the complexity of a general extract, transform, and load (ETL) system may go beyond what these criteria can evaluate.Still the investigations are needed on dealing with complex files [14].
Jutta Hortsmann, J. suggested some examples of database migration tools as shown in Table I.
The data migration process under goes through several stages, which include planning, designing, cleansing, loading as well as verifying of the data.Fig. 1 shows the Data migration process.

III. METHODOLOGY
The method of analysis for this project was divided into two categories, guided mainly by the objectives of the study.
The first method of analysis involved getting expert opinion from IT personnel that were purposefully selected from the Applications and Database Administration sections of three institutions namely NAPSA, ZANACO and ZAMTEL.
These IT experts were meant to capture as much qualitative data as possible regarding the existing database migration procedures, as well as identifying the challenges associated with it.
The second method of analysis involved designing the modules that made up the system.The overall purpose of this system design was to provide efficiency and effectiveness in data migration process.To achieve this process, the Java Programming Language and Database Management Systems technologies were used.
This approach involved developing the tool "Database Migrator Tool".The tool was developed on a windows platform using Java Eclipse with four non-identical dummy The tool was run in a controlled environment over the network.
The following java support tools were used to support the technologies used in the system: 1) MySQL connector (mysqlconnector.jar);which was used to connect MySQL database management system from java.www.ijacsa.thesai.org2) SQL server (sqljdbc.jar);which was used to connect SQL server database management system from java, and 3) Jackcess (jackcess.jar);which was used to connect SQL server database management system from java.
4) Oracle connector (OJDB5.jar);this java library was used to connect Oracle database Management system from Java.
The Database Management Systems used included: 1) Microsoft SQL Server is a database management system whose primary function in this case was to store the database in SQL server format (.mdf) and retrieve data as requested by other software applications.
2) MySQL database management system was used to store the database in MySQL format (.frm).This database management system stored data in separate tables whose structures were organized into physical files.
3) Microsoft Access Database Management System combines the relational Microsoft Jet Database Engine with a graphical user interface and software-development tools.It is a member of the Microsoft Office suite of applications, included in the Professional and higher editions.Microsoft Access was used to store database data in its own format (.accdb) based on the Access Jet Database Engine.
4) Oracle database Management system was used to store and retrieve related information.Oracle database management system was used to store database data in its Oracle database format (.dbf)

IV. RESULTS AND IMPLEMENTATION OF NEW SYSTEM
This segment presents the results obtained from the baseline study as well as development and testing of system prototype.In order to confidently and significantly address the challenges associated with the old system, baseline study was conducted and proposed prototype application was developed.
The data collected from the baseline study was analyzed using descriptive statistics and the results were presented in form of charts.From the responses obtained from the respondents, 58% admitted that the tools that were currently being used for database migration were wrong tools.42% said the tools were not wrong per ser.However, 71% of the responses indicated that the old system had no provision to test and validate the data migration process.Additionally, 87% had experienced data loss in the process of migrating the database when using the old system.93% of the respondents admitted that because of the technical challenges associated with the old database migration process, such as constant data loss, failure to test and validate the migration process, engaging a consultancy every time the need arises, etc., the old system was too expensive to manage.88% further stated that there was inadequate knowledge among the users on the precise data migration tools to use.55% of the respondents also stated that there were no existing data governance policies, a situation that made it difficult to manage this system.Fig. 2 shows the summary of the results from the baseline study.

A. The Architectural Design
This Database migrator was developed using a Top-Down approach.This approach involved decomposing the system into individual smaller modules, aimed at achieving the required detail.Fig. 3 shows the Internal Logic Design.

C. The Interface Design
Database migrator is a desktop application and the interface enables the user to use the system without any difficulties; Java Graphical User Interface (GUI) was used to design and develop the interface.The interface design comprises of the main interface, sub interface and the graphical interface.

D. The Main Interface
The main interface is the home interface for the system and appears when the system runs.It consists of buttons used for running the migration process where the user selects the source and destination Database management system.Fig. 5 show the Main Interface

E. Sub Interface
The sub interface is used for the actual conversion from one database system to the other.Upon specifying the details of the source database, the sub interface converts the source database system into the destination database system.In case of an error, the sub interface has a provision for cancelling the process.
All the four sub modules were converted to XML file, which in turn acts as a common ground for converting the database from one system to another.Once the design specification and project design was approved, system coding commenced.Fig. 6 shows the sub module.

F. Graphical User Interface
The graphical user interface allows the user to interact with the system; it was developed using Eclipse java (jdk 1.6.0).The user interface provided the user with a point and click interface which reduced user"s errors because the user was not prompted to enter any information.

G. The Migration Process
The process involves selection of the source and destination Database Management System using the main interface by the system users.The sub interface then converts from one system to the other.Fig. 7 shows the source and destination interface.Enter the table names Cancel Convert www.ijacsa.thesai.orgOnce the user selects the source and destination databases, the tool prompts the user to specify credentials for the source and destination databases as shown in Fig. 8.
Upon authenticating the source database credentials, the tool prompts the users with options to specify what needs to be migrated, i.e. data, table or entire database.Fig. 9 shows the tables from source database.
Ramaswamy, V.K. argued that effective and efficient migration of data is one of the cornerstones for the success of the process[11].He further emphasized the fact that significant planning needed to be done before the actual process of data migration commences.He outlined a strategy for data migration in which he listed down the type of data to be migrated, timing of the data load, templates and tools for use in the migration process.Sait S.A. et al. on behalf of Amazon Web Services (AWS) provided comprehensive strategies for migrating Oracle databases in which they stated that there is no absolute formula for migrating databases but that there are certain factors one needed to put into consideration before undertaking database migration [12].These factors include the size of the database, the network connectivity between the source server and the target service, the version and edition of oracle database software, the database options, tools and utilities that are available, as well as the time available for the migration process.Based on the above factors, the authors divided the migration process into two methods namely the One Step migration, which is ideal for small databases, and the two-step migration, which can be used for any size of the database.Currently, a number of prototypes and tools have been developed to facilitate the migration of relational databases (RDBs) into target databases.Senior researchers Bin Wei, and Tennyson X. Chen, developed Data Migration Tool (DMT) for US National Oceanic and Atmospheric Administration
databases (Microsoft Access, MySQL, SQL Server and Oracle).The automation of the database migration process involved database cloning between different platforms, exchange of data between data center and different clients, running non-identical DBMS and backing up the database in flexible format such as eXtensible Markup Language (XML).

TABLE II .
TESTING OF CLASSES FROM XML FILE TO DATABASE