Inside Data 62
by Graham Keitch
Sooner or later you are going to have to migrate your database to a more up-to-date platform. Graham Keitch examines your options.
HardCopy Issue: 62 | Published: February 25, 2014
Database migration projects can be a serious challenge. The processes involved can be complex and can threaten business continuity should they go wrong. Maintaining the status quo can be risky too if outdated systems no longer meet the demands of the business, or the software is no longer supported. While these concerns apply across the entire software spectrum, they are most acute at the data layer. Changes to the database or anything it interacts with, such as applications or platform, can disrupt the system with potentially devastating consequences. In the case of migrations, the database, its schema, code and the actual data itself will most likely have to be worked on, and this can be a risky undertaking. As we will see later, there are tools to assist with this.
It isn’t surprising that many database applications are quite dated. For a lot of users this is fine. Why tamper with things if everything is working as it should and adequately serving the business? Database systems are often subject to ‘creep’ when the original purpose and specification have been extended over time. The full workings of a database that’s been in service for a long time can become obscure. Its interaction with applications, and hence the impact of any changes, may be very difficult to assess unless the documentation is thorough.
Leading database vendors such as Oracle, Microsoft, Sybase and others have tools and resources to assist with this. They can provide tools to help you migrate to their platform, but you will most likely need third party products for cross-platform flexibility, and to address some of the deeper issues such as unravelling the schema and code. Let’s take a look at some of the tools vendors provide with their database, and some third party tools that go deeper.
Both Oracle Database and Oracle MySQL ship with tools that allow you to import a third party database. Oracle SQL Developer is free and supports the migration of legacy Oracle and non-Oracle database technologies to Oracle Database. Resources include documents that will help you estimate timeframes, risks, roles and responsibilities.
Those wishing to upgrade from earlier editions of Oracle Database can use the Oracle Database Upgrade Assistance (DBUA). Alternatively there are plenty of documents available to guide you through the manual command line upgrades which will be necessary if you’re migrating to a different hardware or operating system platform. For safety, upgrades and patches are now installed into a new Oracle Home which ensures the current production environment isn’t tampered with.
Oracle MySQL also has migration tools, some aimed at Microsoft users. Microsoft Access is used by many third party ISV and OEM products. For those requiring multi-platform support and greater scalability, MySQL Embedded Server is an ideal Open Source alternative. MySQL Workbench 6.0 has a Wizard to handle migrations from Microsoft SQL Server, Sybase ASE, Sybase SQL Anywhere and others, including earlier editions of MySQL. The Wizard includes facilities to select and map data and objects for migration.
Microsoft’s free SQL Server Migration Assistant (SSMA) is a graphical tool that provides project assessment and automates schema and SQL statement conversion, data migration and testing. The SSMA for Oracle handles Oracle 7.3 or later migration to all versions of SQL Server from 2005 to 2012. There are also versions of SSMA for Oracle MySQL, Sybase and for Microsoft’s own Access.
Migration to the high end Sybase Adaptive Server Enterprise is likely to involve high performance OLTP systems, a task that is more complex and could require expert involvement. Nevertheless, SAP does provide technical documentation and guidance on its website.
A number of third party products are available that can help with different aspects of the project. A good example is Embarcadero’s ER/Studio XE4 which contains design tools for probing an existing database to model its physical and logical structure. Embarcadero’s DB PowerStudio contains tools such as DBArtisan which can be used for migration projects and ongoing database management.
The Red Gate portfolio likewise addresses the needs of Oracle Database, MySQL, Microsoft SQL Server and Azure users. Their Schema Compare and Data Compare products are useful for both upgrade and migration. SQL Compare creates a migration script so you can update a database in the staging or production environment with changes from a later build. Dell Software’s SharePlex can be helpful for testing purposes. This is a replication tool for Oracle and other databases which provides a near real-time copy of production data without impacting on the live system.