Chapter 5. Database Migration Challenges and Solutions
From a relational database perspective, the migration of one relational database to another is easier than migrating from a non-relational database to a relational database. However, this does not mean that this type of migration is a simple process, because of the impact on the ecosystem of which these databases are a critical part. In many respects, relational databases are quite similar, for example: data storage concepts, data management techniques, data access APIs and so on. However, they also differ significantly in the actual implementation of these common features, such as:
Data migrations between relational databases are easier when compared with migration from legacy databases. The reason being that relational databases are similar in many data management aspects, such as handling different...
Database schema migration challenges
Relational databases differ in naming conventions for tables, columns, and other objects. They also differ in how the object names are stored in the database and how they can be referred to in SQL queries and transactions. All database migration tools currently in use by customers, including SQL Developer, do a pretty good job of converting non-Oracle database schemas to Oracle. The most common issues that show up during a database migration to Oracle are discussed in the following sections.
Database object naming/definition issues
The Oracle database has placed certain restrictions on what is allowed for naming/defining objects in the database and how these objects are subsequently accessed. Broadly, the object naming issues can be classified into four categories:
Use of special characters in object names
Use of reserved words in object names/definitions
Case-sensitive object names
Length of object names
SQL Developer assists in identifying, resolving these...
Database migration case studies
The following sections are few case studies showcasing successful database migrations from different types of databases such as DB2, Sybase, and Legacy Mainframe databases to Oracle, as well as data integration projects executed by Oracle and partners over the years. These case studies highlight the tools and technologies used in these migration projects and also the business problems that they tried to solve with migration to Oracle. These case studies highlight Oracle partner eco-system proficient in database migrations that have the necessary skill sets and expertise, not only to manage the migration project, but also have subject matter expertise in migrations in general.
Case Study #1: DB2/400 migration to Oracle using Oracle DRDA Gateway for DB2
Customer: Leading business and financial management solutions company for individuals, small businesses, and finance/tax professionals.
Source Environment: IBM system i (AS/400) with DB2.
Challenges: The legacy...
Regardless of how standards-compliant/database-independent an application is, when the underlying database is migrated, changes need to be made to the application. In this chapter, we covered common challenges that come up during database migrations and how best to address them. Sufficient time needs to be allocated for the analysis and design phase in a database migration project, especially for schema design, issues such as null values handling, case sensitivity, and globalization requirements as they affect the application. Having a good understanding of all these issues will prepare you for challenges that may come up in a migration project. It is worth noting that all database migration efforts may not run into all the challenges mentioned earlier, but readers can have peace of mind knowing that they can anticipate challenges that may lie ahead.
Careful planning and awareness of potential issues and challenges go a long way in executing a database migration project successfully...
