Inside Data 65

by Graham Keitch

Need to migrate your database? Graham Keitch looks at third party tools that can help.

HardCopy Issue: 65 | Published: February 27, 2015

Migration projects involving a database that supports mission critical applications can be complex, costly and risky. Most database vendors provide tools that help you migrate from a competitor’s platform, and we looked at this from the perspective of Oracle Database and Microsoft SQL Server in issue 62 (Spring 2014). A migration project generally involves a lot more than a simple export and import of data from one platform to another. It could involve changes to the hardware and software infrastructure, and impact on applications and other services within the computing environment. Third party tools can assist with the challenges that arise with complex migrations.

ERStudio image

Embarcadero ER/Studio Enterprise contains various tools that assist with database migrations.

Database creep is a common ailment which affects legacy systems that get extended over time, resulting in something that no longer resembles the original form. Indeed the suboptimal performance that can result may have triggered the migration project in the first place. A particular problem with legacy systems relates to incomplete documentation and insight. Skill shortages are not helping as an earlier generation of database professionals with legacy knowledge move on or retire. One of the first challenges is to get a grip on the source data. What is the nature of the animal we’re dealing with? Is any of it redundant, and do we need to knock it into shape before we start? Embarcadero is a leading vendor of third party cross-platform database tools and their ER/Studio helps with some of these preparatory steps. Its reverse engineering capabilities provide architects with a physical and logical model of the source database.

While ER/Studio Enterprise Team Edition focuses on database architecture and design, the tools provided with Embarcadero DB PowerStudio XE6 deal with the development, optimisation and administrative tasks that are likely to accompany a cross-platform migration project. The primary tools for this are DBArtisan, DB Change Manager and DB Optimizer. These help validate the new database and will continue to be useful post-migration too.

DB Artisan XE6 is a cross-platform database management tool which allows you to move schema, tables and data across the same or different platforms. The process begins with registering and connecting to both the source and target data sources. The connection information can be saved locally or in a data source catalogue for use by other Embarcadero products. The Migration Wizard allows you to identify the objects which are to be migrated, such as a single database object, all objects owned by a specific user, or an entire database.

Once the migration has been completed, other PowerStudio tools assist with validation. DB Change Manager allows you to compare both environments and generate a migration report. It can be used to compare subsets of the data, preferably less than a million rows as this is a very intensive CPU and RAM operation.

The migration provides an opportunity for you to fine tune the SQL code so as to optimise performance on the new platform. DB Optimizer captures real-time performance metrics and provides profiling reports that can be used to locate and diagnose problematic SQL code and bottlenecks. The profiler also enables you to investigate execution and wait time event details for individual stored routines. SQL Tuner can help detect and modify the execution paths used during data retrieval, allowing you to improve under-performing SQL code. Once the migration has been validated, DBArtisan’s Schema Publication Wizard can produce an HTML report documenting the schema, databases and objects in the new environment.

There are a number of other third party providers that can help with different aspects of the migration, and during the rest of the data lifecycle. Redgate has a number of tools for Microsoft SQL Server, SQL Azure, Oracle and MySQL. These focus on database code and managing change. SQL Compare handles schema comparisons while Data Compare deals with the data itself. Database changes, whether part of the migration or otherwise, should be subject to source control, and Redgate’s Source Control takes care of that.

Dell is another useful source of tools for database migrations. The company’s Toad solutions come in a number of different editions for the common database platforms, and multiplatform support can be added as an option. Toad can generate a physical data model from a schema, and compare the results with another version. It is also possible to reverse engineer a physical data model into a platform agnostic conceptual data model. The facility to round-trip engineer from database or DDL between different platforms is particularly relevant.

Database tools are generally packaged according to IT discipline such as architect, developer and administrator. Product choice can become complex once we add a multiplicity of database platforms to the mix, so please call us to help determine the most appropriate solution for your project.