Practical Guide to
Large Database
Migration
Preston Zhang
A SCIENCE PUBLISHERS BOOK
p,
A SCIENCE PUBLISHERS BOOK
p,
CRC Press
Taylor & Francis Group
6000 Broken Sound Parkway NW, Suite 300
Boca Raton, FL 33487-2742
© 2019 by Taylor & Francis Group, LLC
CRC Press is an imprint of Taylor & Francis Group, an Informa business
No claim to original U.S. Government works
Printed on acid-free paper
Version Date: 20181226
International Standard Book Number-13: 978-1-1383-9162-8
(Hardback)
is book contains information obtained from authentic and highly regarded sources. Reasonable eff orts have been
made to publish reliable data and information, but the author and publisher cannot assume responsibility for the
validity of all materials or the consequences of their use.  e authors and publishers have attempted to trace the
copyright holders of all material reproduced in this publication and apologize to copyright holders if permission to
publish in this form has not been obtained. If any copyright material has not been acknowledged please write and let
us know so we may rectify in any future reprint.
Except as permitted under U.S. Copyright Law, no part of this book may be reprinted, reproduced, transmitted,
or utilized in any form by any electronic, mechanical, or other means, now known or hereafter invented, includ-
ing photocopying, microfi
lming, and recording, or in any information storage or retrieval system, without written
permission from the publishers.
For permission to photocopy or use material electronically from this work, please access www.copyright.com
(http://www.copyright.com/) or contact the Copyright Clearance Center, Inc. (CCC), 222 Rosewood Drive, Danvers,
MA 01923, 978-750-8400. CCC is a not-for-profi
t organization that provides licenses and registration for a variety
of users. For organizations that have been granted a phot
ocopy license by the CCC, a separate system of payment
has been arranged.
Trademark Notice:
Product or corporate names may be trademarks or registered trademarks, and are used only for
identifi cation and explanation without intent to infringe.
Library of Congress Cataloging-in-Publication Data
Visit the Taylor & Francis Web site at
http://www.taylorandfrancis.com
and the CRC Press Web site at
http://www.crcpress.com
Names: Zhang, Preston, author.
Title: Practical guide to large database migration / Preston Zhang,
University of Georgia, Database Administrator Watkinsville, Georgia, USA.
Description: Boca Raton, FL : CRC Press, Taylor & Francis Group, [2019] |
Includes bibliographical references and index.
Identiers: LCCN 2018060369 | ISBN 9781138391628 (hardback : acid-free paper)
Subjects: LCSH: Systems migration. | Database management.
Classication: LCC QA76.9.S9 Z53 2019 | DDC 005.74--dc23
LC record available at https://lccn.loc.gov/2018060369
Preface
The success of any modern business relies on its ability to adapt to fast changing business
environments: for example, IT infrastructure, database version and servers need to be
upgraded every few years.
When upgrading existing hardware or servers or to a new system, a common task for an
IT team is how to migrate database from old system to new system. We need to make sure
that there is low business downtime during the migration. We also need to test new system
to avoid application failure.
As a database administrator I have done database migrations many times. For example,
migrating a MySQL database from a developer server to a production server or migrating
a SQL Server database from an older server to a new server. Usually, migrating data
within the same database system is not a difficult task. However, it is a challenging when
migrating database from one system to a different system. For example, migrating a SQL
Server database to Oracle database system.
I have tried to find a book about database migration in Oracle, SQL Server and MySQL
database systems, but I only found books about Oracle or AWS database migration. I
would like to share database migration experience with database developers and DBAs.
All the migration examples in this book use large databases in Oracle, SQL Server and
MySQL. This book is written in easy to read style with step-by-step examples.
Who This Book Is For
This book is for intermediate database developers, database administrators. If you are not
familiar with SQL syntax and installation of Oracle/SQL Server/MySQL please read my book
“Practical Guide to Oracle SQL, T-SQL and MySQL”. The SQL code in this book is fully tested
in Oracle 11g, Oracle 12c, SQL Server 2012, SQL Server 2016, MySQL 5.5 and MySQL 5.7.
How to Use this Book
To run the examples from this book you need to install the following database systems and
development tools:
Oracle 11g, Oracle 12c
Oracle SQL Developer
SQL Server 2012, SQL Server 2016
SQL Server Management Studio 2012 or above
MySQL Server 5.5, MySQL Server 5.7
MySQL Workbench 6.3
All the above software can be download from Oracle.com, Microsoft.com and MySQL.com
Contents
Preface iii
Chapter 1 Introduction to Database Migration 1
1.1 What is Database Migration 1
1.2 Database Migration Stages 1
1.2.1 Migration Preparing Stage 1
1.2.2 Data Migration Stage 2
1.2.3 ETL (Extract, Transform and Local) Stage 3
1.2.4 Database and Application Testing Stage 3
1.3 Database Migration Paths 3
1.4 Database Migration Tools 4
1.4.1 ESF Database Migration Toolkit 5
1.4.2 Data Loader 5
1.4.3 MySQL Workbench Database Migration Wizard 6
1.4.4 SQL Server Migration Assistant Tools 7
1.5 Sample Large Databases 8
1.5.1 SQL Server Chinook Sample Database 8
1.5.2 SQL Server AdventureWorks2012 Sample Database 8
1.5.3 MySQL Employees Sample Database 9
1.5.4 MySQL Sakila Sample Database 9
1.5.5 Oracle Human Resources (HR) Sample Database 10
1.5.6 Oracle Sales History (SH) Sample Database 10
1.5.7 Microsoft Access MonthlySalesReports Sample Database 11
Summary 11
Chapter 2 SQL Server Database Migration 12
2.1 SQL Server to MySQL Migration Example 12
2.1.1 Using ESF Database Migration Toolkit 12
2.1.2 Using MySQL Workbench Migration Wizard 21
2.2 SQL Server to SQL Server Migration Example 29
2.3 SQL Server to Oracle Migration Example 36
Summary 42
Chapter 3 More About SQL Server Management Studio 43
3.1 Visual Database Design 43
3.2 SQL Server Query Designer 47
3.3 Generating Scripts 49
3.4 Activity Monitor 54
3.5 Query Options 55
vi Contents
3.6 Template Browser 56
3.7 Database Engine Tuning Advisor 57
3.8 Disk Usage Report 61
3.9 Database Properties 62
3.10 SQL Server Backup 63
3.10.1 Using T-SQL 63
3.10.2 Using Agent Job 63
3.10.3 Using Maintenance Plan 65
Summary 74
Chapter 4 MySQL Database Migration 75
4.1 MySQL to MySQL Migration Example 75
4.1.1 Using MySQL Export Commands and Import Wizard 75
4.1.2 Using MySQL Workbench Schema Transfer Wizard 80
4.2 MySQL to SQL Server Migration Example 86
4.2.1 Using Microsoft SQL Server Migration Assistant for 86
MySQL Tool
4.2.2 Using ESF Database Migration Tool 92
4.3 MySQL to Oracle Migration Example 99
Summary 108
Chapter 5 More About MySQL Workbench 109
5.1 MySQL Database Modeling 109
5.1.1 Visual Database Design 109
5.1.2 Reverse Engineering 114
5.1.3 A Visual Query Builder for MySQL & Other Databases 118
5.2 MySQL Server Management 121
5.2.1 Server Status 121
5.2.2 Server Logs 122
5.2.3 Server Performance Dashboard 123
5.3 Online Backup 123
5.4 MySQL Synchronization 125
5.5 Unique MySQL INSERT IGNORE Statement 130
Summary 130
Chapter 6 Oracle Database Migration 131
6.1 Oracle to MySQL Migration Example 131
6.2 Oracle to SQL Server Migration Example 137
6.3 Oracle to Oracle Migration Example 146
6.3.1 Using Oracle Data Dump Export and Import in 146
SQL Developer
6.3.2 Using ESF Database Migration Toolkit 159
Summary 168
Chapter 7 More About Oracle SQL Developer and Oracle EM 169
7.1 Oracle Query Builder 169
7.2 Oracle SQL Developer DBA Tool 171
7.3 Oracle 12c Enterprise Manager Database Express 173
Summary 179
Chapter 8 Microsoft Access Database Migration 180
8.1 Microsoft Access to MySQL Migration Example 180
8.2 Microsoft Access to SQL Server Example 184
8.3 Microsoft Access to Oracle Migration Example 188
Summary 194
Index 195
About the Author 197
Contents vii
Chapter 1
Introduction to Database Migration
1.1 What is Database Migration
Database migration is the process of moving a database from a vendor to another or
upgrading current version of database software. There are many reasons why organizations
would want to migrate their databases:
• Highcostoftraditionaldatabaseplatformownershipandmaintenancecost
• Replacinglegacyservers
• Updatingstorageequipment
• Companymergers
• Movingdatatoacloudprovider
• Applicationmigration
AccordingtoDB-Engines,inJune2018,themostwidelyusedsystemsareOracle,MySQL,
Microsoft SQL Server, PostgreSQL, IBM DB2, Microsoft Access, and SQLite. All the
migrationexamplesinthisbookuseSQLServer,MySQL,OracleandAccessbecausethose
are the top databases in the world.
Database migration is not an easy task. For example, it’s quite a challenge to migrate
Oracledatabase.Thisbookprovidesstepbystepguidesformigratingdatabasesbetween
SQLServer,MySQL,OracleandMicrosoftAccess.Ihopethatthisbookcanhelpdatabase
developersandDBAstomakedatabasemigrationeasier.
1.2 Database Migration Stages
Database migration usually involves several stages:
• MigrationPreparingStage
• DataMigrationStage
• ETLStage
• DatabaseandApplicationTestingStage
1.2.1 Migration Preparing Stage
• Identifythesourcedatabasetables,views,storedproceduresanddata
• Identifyapplicationanddatabaseusersonthesourcesystem
ALL FIGS. IN BW
• Identifyprivilegesandpermissionsonthesourcesystem
• Findapplicationsoftwarethatusesthesourcedatabase
• Estimatemigrationrisksandcost
• Checkhardwarecapacity
• Evaluatemigrationtools
1.2.2 Data Migration Stage
Schemamappingbetweensourcedatabaseandtargetdatabase.Datatypemappingiseasy
ifthereisnochangeindatabaseformat.Forexample,databasemigrationfromMySQLto
MySQL.However,datatypemappingisadifculttaskforlargetableswhenmigrating
fromonedatabaseformattoanotherdatabaseformat.Forexample,databasemigration
fromSQLServertoOracle.
Database migration tools can help originations to accomplish migration projects. The tools
usuallymigrateviewstotables.It’shardtondatoolthatcanmigratetriggersandstored
procedures.DBAsordatabasedeveloperscancreateviewsintargetdatabasebyusingthe
migratedtables.TheyalsocangenerateSQLcode for views fromsourcedatabasethen
applythecodetotargetdatabase.FortriggersandstoredproceduresDBAsanddatabase
developersneedtogetSQLcodefromthesourcedatabasethenconverttheSQLcodefor
the target database.
Belowisadatatypesmappingtable.Youcanusethistabletoconvertdatatypesmanually
or verify data types after database migration. In later chapter you will see that database
migration tools can map date types between a source and a target database automatically.
Table 1-1 Data types mapping table
SQL Server Type MySQL Type Oracle Type Access Type
INT INT
NUMBER(10) LONGINTEGER
TINYINT TINYINT NUMBER(3) BYTE
SMALLINT SMALLINT NUMBER(5) INTEGER
BIGINT BIGINT NUMBER(20)
BIT TINYINT(1) NUMBER(3) YES/NO
FLOAT FLOAT FLOAT(53) DECIMAL
REAL FLOAT FLOAT(24) SINGLE
NUMERIC DECIMAL NUMBER(p,s) CURRENCY
DECIMAL DECIMAL NUMBER(p,s) CURRENCY
MONEY DECIMAL NUMBER(19,4) CURRENCY
SMALLMONEY DECIMAL NUMBER(10,4)
CHAR CHAR CHAR
NCHAR CHAR/LONGTEXT NCHAR
VARCHAR VARCHAR VARCHAR2 MEMO
NVARCHAR VARCHAR NVARCHAR TEXT
Table 1-1 contd. ...
2 Chapter 1 Introduction to Database Migration
1.2.3 ETL (Extract, Transform, and Load) Stage (Optional)
• Sometimesorganizationswanttoextractdatafromseveralsourcesandtransformthe
data based on business rules then load the data into target databases.
• Transformingprocessmayincludesorting,joiningdataandcleaningdata,etc.
• Loadingprocessincludesloadingthetransformeddataintotargetdatabasesordata
warehouses.
1.2.4 Database and Application Testing Stage
• Verifythemigrateddataontargetdatabase
• Createthesameapplicationanddatabaseusersonthetargetsystem
• Createthesameprivilegesandpermissionsonthetargetsystem
• Testviews,triggers,storedproceduresandfunctionsonthetargetdatabase
• Testthedatabaseandapplicationbytheenduserstomakesuretherearenoerrors
1.3 Database Migration Paths
YouwillseedatabasemigrationexamplesfromSQLServertoMySQL,SQLServertoSQL
ServerandSQLServertoOracleinChapter2.WewilluseSQLServerChinook database
in this book.
SQL Server Type MySQL Type Oracle Type Access Type
DATE DATE
DATETIME DATETIME DATE
SMALLDATETIME DATETIME DATE DATE/TIME
TIME TIME
TIMESTAMP TIMESTAMP TIMESTAMP
BINARY BINARY RAW BINARY(SIZE)
VARBINARY VARBINARY RAW
TEXT VARCHAR LONG MEMO
NTEXT VARCHAR LONG
IMAGE LONGBLOB LONGRAW
XML TEXT LONG
... Table 1-1 contd.
Figure 1-1 SQLServermigrationpaths
Chapter 1 Introduction to Database Migration 3
YouwillseedatabasemigrationexamplesfromMySQLtoMySQL,MySQLtoSQLServer
andMySQLtoOracleinChapter4.WewilluseMySQLEmployees database in this book.
Figure 1-2 MySQLdatabasemigrationpaths
YouwillseedatabasemigrationexamplesfromOracletoMySQL,OracletoSQLServer
andOracletoOracleinChapter6.WewilluseOracleHR or SH database in this book.
Figure 1-3 Oracledatabasemigrationpaths
You willsee databasemigration examples from MicrosoftAccessto MySQL,Microsoft
AccesstoSQLServerandMicrosoftAccesstoOracleinChapter5.WewilluseMicrosoft
AccessMonthlySalesReports database in this book.
Figure 1-4 Accessdatabasemigrationpaths
1.4 Database Migration Tools
There are a lot of database migration tools available. I have tested the following tools and I
will use those tools to demonstrate large database migration in this book.
• ESFDatabaseMigrationToolkit
• DataLoader
• MySQLWorkbenchMigrationWizard
4 Chapter 1 Introduction to Database Migration
• SQLServerMigrationAssistantforMySQL
• SQLServerMigrationAssistantforOracle
• SQLServerMigrationAssistantforAccess
1.4.1 ESF Database Migration Toolkit
Website:https://www.easyfrom.net
ESFDatabaseMigrationToolkitcanhelpcompaniesmigratedatabetweenOracle,MySQL,
MariaDB,SQLServer,PostgreSQL,IBMDB2,SQLite,MicrosoftAccess,etc.Itcanmigrate
most database objects except procedure, function and trigger. Database view will be
migratedtotable.Thetrialversionhasamigrationlimitfor50,000rowspertableandit
willaddanextraeldintables.Thatisgoodenoughfortestingdatabasemigration.
ESFDatabaseMigrationToolkitisveryeasytouse.Itsavesdatabaseadministratorsalot
of time for database migration projects.
1.4.2 Data Loader
Website:https://dbload.com
• DataLoaderworkswellfordatabasemigration,butthetrialversionhas50rowslimit
pertable,that’swhyIwillnotuseitasamigrationtoolinthisbook.
• DataLoaderStandardEditionhas100,000rowslimitpertable.
• DataLoaderProfessionalEditionhas1,000,000rowslimitpertable.
• DataLoaderEnterpriseEditionhasunlimitedrowsper.
DataLoader supports MySQL,Oracle,MS SQLServer,MSAccess, Excel, FoxPro/DBF,
CSV/textles:
Figure 1-5 DataLoaderdifferentversions
Chapter 1 Introduction to Database Migration 5
Figure 1-6 DataLoaderdatabasemigrationpaths
1.4.3 MySQL Workbench Database Migration Wizard
TheMySQLWorkbenchMigrationWizardallowuserstoconvertanexistingdatabaseto
MySQLinfewsteps.ThesourcedatabasemightbeMySQL,SQLServer,PostgreSQLand
MicrosoftAccess,etc.
1.4.4 SQL Server Migration Assistant Tools
MicrosoftSQLServerMigrationAssistant(SSMA)isatooltohelpdatabasemigrationtoSQL
ServerfromMySQL,Oracle,MicrosoftAccess,DB2andSybaseDB.WewilluseSQLServer
MigrationAssistantforMySQLandSQLServerMigrationAssistantforOracleinthisbook.
6 Chapter 1 Introduction to Database Migration
Figure 1-7 MySQLWorkbenchDatabaseMigrationWizard
Figure 1-8 SQLServerMigrationAssistantforMySQL
Chapter 1 Introduction to Database Migration 7
Figure 1-9 SQLServerMigrationAssistantforOracle
Figure 1-10 SQLServerMigrationAssistantforAccess
1.5 Sample Large Databases
1.5.1 SQL Server Chinook Sample Database
TheChinookDatabaseisasampledatabaseforSQLServer,Oracle,MySQLetc.Itisbeing
used for application prototypes and website backend database. It includes the following
tables:Artists,Albums,Employee,Track,Playlist,Playlisttrack,Invoice,Invoiceline,Genre,
CustomerandMediatype.Themaximumrowsis8,715rows(Playlisttracktable).
Download the Chinook Database below:
https://github.com/jimfrenette/chinook-database
8 Chapter 1 Introduction to Database Migration
1.5.2 SQL Server AdventureWorks2012 Sample Database
TheAdventureWorkssampledatabaseisveryusefulfortestingandlearningSQLServer
database.BelowisAdventureWorks2012tablenamesandrecords(Figure1-12):
Figure 1-11 Tables in Chinook database
Figure 1-12 TablesinAdventureWorks2012database
1.5.3 MySQL Employees Sample Database
TheEmployeessampledatabaseprovidesalargedatabasewithsizeabout160MB.Ithas
4millionrecordsintotal.
1.5.4 MySQL Sakila Sample Database
The Sakilasample databaseincludes MySQL new features.It isgood for tutorials and
examples.Figure1-17showsviews,storedproceduresandfunctionsinSakiladatabase.
Chapter 1 Introduction to Database Migration 9
Figure 1-13 SamplerecordsfromMySQLEmployeesdatabasesalariestable
Figure 1-14 Sakiladatabasestructures
1.5.5 Oracle Human Resources (HR) Sample Database
HumanResources(HR) database is useful for introducing fundamental database topics.
Figure1-15showsthatdept_emptablehas331,613rows.
1.5.6 Oracle Sales History (SH) Sample Database
Thesampledatabasehasmanyreportsthatevaluatepastdatatrends.Thereareannual,
quarterly, monthly, and weekly sales reports. The database also has data for sales by
geographical area.
10 Chapter 1 Introduction to Database Migration
Figure 1-15 OracleHRschemadept_emptablerecords
Figure 1-16 OracleShschematables
1.5.7 Microsoft Access MonthlySalesReports Sample Database
Figure1-17showsthattblOrderDetailshas121,317records.
Chapter 1 Introduction to Database Migration 11
Summary
Chapter 1 covers the following:
Databasemigrationdenition
Database migration stages including Migration Preparing Stage, Database Migration
Stage,ETLStageandDatabaseandApplicationTestingStage
Database migration sample paths
DatabasemigrationtoolsforOracleSQLServer,MySQLandMicrosoftAccessdatabases
DatabasemigrationsampledatabasesinOracle,SQLServer,MySQLandMicrosoftAccess
Figure 1-17 TablesinAccessMonthlySalesReportsdatabase
Chapter2
SQLServerDatabaseMigration
ThemaintopicsinthischapterareillustratedinFigure2-1:SQLServerdatabasemigration
to MySQL, SQL Server database migration to SQL Server and SQL Server database
migrationtoOracle.
It’s important to understand the source database before migration. You can open SQL
Serverdatabaseandviewallthetables,views,storedproceduresandfunctions.Adecision
needs to be made weather your organization want to keep current database objects or
change them.
2.1 SQL Server to MySQL Migration Example
WewilluseESFDatabaseMigrationToolkitandMySQLWorkbenchMigrationWizardin
this section.
Figure 2-1 SQLServerdatabasemigrationpaths
2.1.1 Using ESF Database Migration Toolkit
Prerequisites: WindowsXP/Vista/7/8/8.1/10
SQLServer6.5andabove
MySQL3.23andabove
Source Server and Database: SQLServer2016Chinookdatabase
Target Server and Database: MySQL5.7Chinookdatabase
Migration Tool: ESFDatabaseMigrationToolkit
Database Transform: Yes