Transcription
(IJACSA) International Journal of Advanced Computer Science and Applications,Vol. 9, No. 1, 2018A Seamless Network Database Migration Tool forInsititutions in ZambiaMutale KasondeSimon TemboDepartment of Electrical and Electronic EngineeringDepartment of Electrical and Electronic EngineeringUniversity of ZambiaUniversity of ZambiaLusaka, ZambiaLusaka, ZambiaAbstract—The objective of the research was to atabasemaintaining data integrity, providing for backup and dence,Management Systems (DBMS) by automating the databaseproviding a non-procedural query language as well asmigration process. The automation of the database migrationperforming automatic query optimization.process involved database cloning between different platforms,Migrating a database involves migrating the tables andexchange of data between data center and different clientsrecords from one database management system to another. Therunning non-identical DBMS and backing up the database inTransvive white paper, 2014 defines the term “Migration” asflexible format, such as eXtensible Markup Language (XML).the movement of technology from older, or proprietaryThis approach involved development of a “Database MigrationsystemsTool”. The tool was developed on a windows platform using Javacost-effective applications, and operating systems [2]. DataEclipse with four non-identical dummy Relational Databasesmigration is usually undertaken for the purpose of replacing,(Microsoft Access, MySQL, SQL Server and Oracle). The toolupgrading server or storage equipment for a websitewas run in a controlled environment over the network andconsolidation, so as to conduct server maintenance or todatabases were successfully migrated from source to targetedrelocate a data center.destination option specified. The developed tool is more efficient,timely, as well as highly cost ever, because different database management systemshave different formats for storing the database, the exchangeKeywords—Database management system; database migration;database structure; database migration toolkits and databaseof database tables and records between different databasesystems usually results in compromising the quality, orauthenticity of the data in the transformation process.cloningAccording to an Oracle White paper, 2011, up to 75% of newI.INTRODUCTIONsystems fail to meet expectations, often because of flaws in theAdvancements in technology usually result in databasemigration, and an example would be for the National Pensiondatabase migration process, which in turn result in data that isnot adequately validated for the intended task [3].Scheme Authority (NAPSA). A database (DB) is a persistent,Some of the challenges associated with database migrationlogically coherent collection of inherently meaningful data,processes include data loss particularly in a case of Poorrelevant to some aspects of the real world [1]. A collection ofLegacy Data Quality, having Wrong Data Migration Tools,these databases is what forms the Database Managementinadequate knowledge in using the precise Data MigrationSystems (DBMS).Tools, failure to Test and validate Data Migration Process, andDatabase Management Systems perform a wide variety ofAbsence of Data Governance Policies [4].roles such as allowing concurrency, controlling security,191 P a g ewww.ijacsa.thesai.org
(IJACSA) International Journal of Advanced Computer Science and Applications,Vol. 9, No. 1, 2018Currently,thereisnocomprehensivesystemII. LITERATURE REVIEWtocomportment the data migration process. The existingIn order to cope with a fast changing businessprocedure for migration is semi manual, and involvesenvironment, it is necessary to update the technologicalfragmentary procedures, which entails using different tools ininfrastructure constantly, and database migration is a routineorder to achieve a comprehensive process. As such,part of this technology. Barron. C et al. suggested that the coremaintaining the structure of a database when migrating it fromreason for the need of database migration is mainly to upgradeone database management system to another is quite athe existing system into a developed system that conforms tochallenging task for most Organizations. Often times,the Industry requirements [5].organizations have to design a new database for the differentThe tasks of a migration workflow are diverse anddatabase management system it wants to adopt. This currentcomplicated, executing all these processes manually requiressystem of database migration is not only costly, but it‟s alsoplenty of time, as well as a highly experienced migration teamineffective, and may sometimes result in the loss of essentialin both the source, as well as the target system. In a paper,data in the migration process. This is because this systemreviewing database migration strategies, tools and techniques,involves hiring a database designer every time theElamparithi, M and Anuratha, V singled out relationalOrganization has to switch to a different database managementdatabase migration (RDM) as an example. The authors statedsystem. Therefore, by developing an automated databasethat relational database migration was always a complex, timemigration process, the challenges being experienced with theconsuming, and magnified process due to heterogeneouscurrent migration process required to be addressed andstructures and several data types of relational database [6].eliminated.This gives rise to certain risks and challenges in the dataThis study intended to explore ways in which datamigration process.migration process could be improved through the developmentThe Arbutus Software Whitepaper summarized the risks ofof a new Seamless Database Migrator. This was expected todatabase migration as follows: Unrealistic estimates of datahelp overcome challenges associated with the networkquality, inaccurate, missing or out of date source systemdatabase, and deliver the data with such accuracy. Specifically,documentation, as well as the inability to reconcile the targetthe new database migrator was expected to.systems data to the source system [7].1) Eliminate the need for script writing when transferringtables in the database with the records.2) Prompt the user to select the destination and sourceDatabase Management System, and specify what to migratei.e. either the entire database with structure and data or juststructure or data, thereby allowing different database management systems to exchange database tables and records inthem, without any loss of data details in the transformationprocess.To counter the above challenges of database migration,businesses have seen the need to develop effectivemethodologies of migrating databases. Several migration toolsand strategies have since been developed in the softwareindustry [8]. However, finding the effective methodologies fordatabase migration still remains a challenge, and many currentapproaches to data migration suffer from a consistently lowThe paper is organized as follows: Section 2 deals withsuccess rate. Arbutus Software White Paper approximates thatliterature review, which covers existing tools as well as thebetween 70 and 90% of data migration projects either failtheoretical literature bordering on policy issues regardingoutrightly, or run over budget, with an average cost overrun ofdatabase migration. The methodology is presented in10 times the original estimate [9]. This is mainly due to theSection 3. Section 4 brings out the results and the discussionunplanned issues that often occur at the later stages of aof the baseline study conducted to identify challenges in theproject.database migration process. System testing is presented inSection 5, and the last Section 6 contains the conclusion.Several researches have since been undertaken to addresssome of the problems associated with database migration,although no absolute solution has come forth. For example,Joseph R. Hudicka, provided a complete solution of data192 P a g ewww.ijacsa.thesai.org
(IJACSA) International Journal of Advanced Computer Science and Applications,Vol. 9, No. 1, 2018migration methodology, which deals with row counts, columnnot migrate null and numeric values and error occurs for keycounts and related statistics to the source databases [10].constrain keys.However, the problem with this methodology is that it doesTABLE I.DEVELOPED DATA MIGRATION TOOL [15]OperatingS. NoNameCompanySourceFromToSystemOracle, SyBase,Windows, Linux,1OSDM ToolkitApptilityOpenInformix, DB2, MS Access,PostgreSQL & MYSQLUnix & Mac OSMS SQL2DB MigrationAkcessClosedOracle & MS SQLPostgreSQL & MYSQLWindows3Mssql2 PgsqlOS ProjectOpenMS SQLPostgreSQLWindowsMySql ABOpenMS Access & OracleMySQLWindowsMySQLWindowsAny RDB*OS IndependentPostgreSQL, MySQL &Linux &IngresWindowsIngresOS igrationConvertorsMS Access, MS SQL, Dbase5Closed& OracleToolkit6Open DBcopyPuzzle ITCOpenAny RDB*7Progression DBVersoraOpenMS SQL8Shift2IngresOS ProjectOpenOracle & DB2Linux,Real Soft9SQLPorterOracle, MS SQL, DB2 &ClosedStudioMySQLMac OS dAll Relational DatabasesPostgreSQL & MySQLOracle, DB2, MS SQL,MySQLWindowsSwisSQL Data Migra11tionWindowsSybase & MaxDBToolSwisSQLOracle, MSSQL, DB2,12SQLOneAdventNetClosedPostgreSQL & MySQLWindowsSQL Server, DB2, MS AccessWindows, Linux && OracleMac OSInformix & SybaseConsoleSQL Server, DB2, MS13MapForceAltovaClosedAccess, MySQL &PostgreSQLSQL Server, DB2, MSSQL Server, DB2, MS Access,Access, MySQL &MySQL &PostgreSQLPostgreSQLOracle, DB2, SQLite,Oracle, DB2, SQLite,MySQL, PostgreSQL, MSMySQL, PostgreSQL, MSAccess & FoxproAccess & FoxproCenterprise14AsteraClosedWindowsData IntegratorDB15DBConvertClosedWindowsConvert193 P a g ewww.ijacsa.thesai.org
(IJACSA) International Journal of Advanced Computer Science and Applications,Vol. 9, No. 1, 2018Ramaswamy, V.K. argued that effective and efficientmigration of data is one of the cornerstones for the success ofthe process [11]. He further emphasized the fact thatsignificant planning needed to be done before the actualprocess of data migration commences. He outlined a strategyfor data migration in which he listed down the type of data tobe migrated, timing of the data load, templates and tools foruse in the migration process.Fig. 1. Data migration process [16].Sait S.A. et al. on behalf of Amazon Web Services (AWS)provided comprehensive strategies for migrating Oracledatabases in which they stated that there is no absoluteformula for migrating databases but that there are certainIII. METHODOLOGYThe method of analysis for this project was divided intotwo categories, guided mainly by the objectives of the study.factors one needed to put into consideration beforeThe first method of analysis involved getting expertundertaking database migration [12]. These factors include theopinion from IT personnel that were purposefully selectedsize of the database, the network connectivity between thefrom the Applications and Database Administration sections ofsource server and the target service, the version and edition ofthree institutions namely NAPSA, ZANACO and ZAMTEL.the oracle database software, the database options, tools andThese IT experts were meant to capture as much qualitativeutilities that are available, as well as the time available for thedata as possible regarding the existing database migrationmigration process. Based on the above factors, the authorsprocedures, as well as identifying the challenges associateddivided the migration process into two methods namely thewith it.One Step migration, which is ideal for small databases, andThe second method of analysis involved designing thethe two-step migration, which can be used for any size of themodules that made up the system. The overall purpose of thisdatabase.system design was to provide efficiency and effectiveness inCurrently, a number of prototypes and tools have beendata migration process. To achieve this process, the Javadeveloped to facilitate the migration of relational databasesProgramming Language and Database Management Systems(RDBs) into target databases. Senior researchers Bin Wei, andtechnologies were used.Tennyson X. Chen, developed Data Migration Tool (DMT) forThis approach involved developing the tool “DatabaseUS National Oceanic and Atmospheric AdministrationMigrator Tool”. The tool was developed on a windows(NOAA), outlining the criteria that need to be consideredplatform using Java Eclipse with four non-identical dummywhen evaluating a DMT [13]. However, while the criteriadatabases (Microsoft Access, MySQL, SQL Server andoutlined by these authors may be adequate for the complexOracle). The automation of the database migration processproject of developing DMT, the complexity of a generalinvolved database cloning between different platforms,extract, transform, and load (ETL) system may go beyondexchange of data between data center and different clients,what these criteria can evaluate. Still the investigations arerunning non-identical DBMS and backing up the database inneeded on dealing with complex files [14].flexible format such as eXtensible Markup Language (XML).Jutta Hortsmann, J. suggested some examples of databaseThe tool was run in a controlled environment over the network.The following java support tools were used to support themigration tools as shown in Table I.The data migration process under goes through severalstages, which include planning, designing, cleansing, loadingas well as verifying of the data. Fig. 1 shows the Datatechnologies used in the system:1) MySQL connector (mysqlconnector.jar); which wasused to connect MySQL database management system fromjava.migration process.194 P a g ewww.ijacsa.thesai.org
(IJACSA) International Journal of Advanced Computer Science and Applications,Vol. 9, No. 1, 20182) SQL server (sqljdbc.jar); which was used to connectSQL server database management system from java, and3) Jackcess (jackcess.jar); which was used to connect SQLserver database management system from java.4) Oracle connector (OJDB5.jar); this java library wasused to connect Oracle database Management system fromJava.too expensive to manage. 88% further stated that there wasinadequate knowledge among the users on the precise datamigration tools to use. 55% of the respondents also stated thatthere were no existing data governance policies, a situation thatmade it difficult to manage this system. Fig. 2 shows thesummary of the results from the baseline study.The Database Management Systems used included:1) Microsoft SQL Server is a database management systemwhose primary function in this case was to store the database inSQL server format (.mdf) and retrieve data as requested byother software applications.2) MySQL database management system was used to storethe database in MySQL format (.frm). This database management system stored data in separate tables whose structureswere organized into physical files.3) Microsoft Access Database Management System combines the relational Microsoft Jet Database Engine with agraphical user interface and software-development tools. It is amember of the Microsoft Office suite of applications, includedin the Professional and higher editions. Microsoft Access wasused to store database data in its own format (.accdb) based onthe Access Jet Database Engine.4) Oracle database Management system was used to storeand retrieve related information. Oracle database managementsystem was used to store database data in its Oracle databaseformat (.dbf)Fig. 2. Baseline study results.A. The Architectural DesignThis Database migrator was developed using a Top-Downapproach. This approach involved decomposing the systemIV. RESULTS AND IMPLEMENTATION OF NEW SYSTEMThis segment presents the results obtained from theinto individual smaller modules, aimed at achieving therequired detail. Fig. 3 shows the Internal Logic Design.baseline study as well as development and testing of systemprototype. In order to confidently and significantly address thechallenges associated with the old system, baseline study wasconducted and proposed prototype application was developed.The data collected from the baseline study was analyzedusing descriptive statistics and the results were presented inform of charts. From the responses obtained from therespondents, 58% admitted that the tools that were currentlybeing used for database migration were wrong tools. 42% saidthe tools were not wrong per ser. However, 71% of theresponses indicated that the old system had no provision to testand validate the data migration process. Additionally, 87% hadexperienced data loss in the process of migrating the databasewhen using the old system. 93% of the respondents admittedthat because of the technical challenges associated with the olddatabase migration process, such as constant data loss, failureto test and validate the migration process, engaging aFig. 3. Internal logic design.consultancy every time the need arises, etc., the old system was195 P a g ewww.ijacsa.thesai.org
(IJACSA) International Journal of Advanced Computer Science and Applications,Vol. 9, No. 1, 2018B. Conversion ModulesEnter the database NameA prototype was developed and a database migrator waseffectively implemented using the technologies elaborated in .the project methodology. Fig. 4 below shows the conversionmodule from XML to Oracle.Enter the table namesXML to Oracle .Create Oracle DBCreate Oracle TablesSub Main ModuleFig. 4. XML to Oracle.ConvertC. The Interface DesignCancelDatabase migrator is a desktop application and theFig. 6. Sub module.interface enables the user to use the system without anydifficulties; Java Graphical User Interface (GUI) was used todesign and develop the interface. The interface designcomprises of the main interface, sub interface and thegraphical interface.E. Sub InterfaceThe sub interface is used for the actual conversion fromone database system to the other. Upon specifying the detailsof the source database, the sub interface converts the sourceD. The Main Interfacedatabase system into the destination database system. In caseThe main interface is the home interface for the systemand appears when the system runs. It consists of buttons usedof an error, the sub interface has a provision for cancelling theprocess.for running the migration process where the user selects theAll the four sub modules were converted to XML file,source and destination Database management system. Fig. 5which in turn acts as a common ground for converting theshow the Main Interfacedatabase from one system to another. Once the designDatabase Migratorspecification and project design was approved, system codingcommenced. Fig. 6 shows the sub module.F. Graphical User InterfaceSourceDestinationThe graphical user interface allows the user to interactwith the system; it was developed using Eclipse java (jdk1.6.0). The user interface provided the user with a point and Access Access MySQL MySQL SQL server SQL serverclick interface which reduced user‟s errors because the userwas not prompted to enter any information.G. The Migration Process Oracle OracleThe process involves selection of the source anddestination Database Management System using the maininterface by the system users. The sub interface then convertsOKFig. 5. The Main Interface.from one system to the other. Fig. 7 shows the source anddestination interface.196 P a g ewww.ijacsa.thesai.org
(IJACSA) International Journal of Advanced Computer Science and Applications,Vol. 9, No. 1, 2018Fig. 7. Source and destination of DM.Fig. 9. Tables selected for migration process.TABLE II.TESTING OF CLASSES FROM XML FILE TO DATABASETestingActualExpected ResultsScenariosResultsXML file was conXML file to1Access DBverted toMicrosoft AccessAccess DBXML file was con2XML file to M
Centerprise Data Integrator Astera Closed SQL Server, DB2, MS Access, MySQL & PostgreSQL SQL Server, DB2, MS Access, MySQL & PostgreSQL Windows 15 DBConvert DB Convert Closed . for data migration in which he listed down the type of data
