Website Migration Project

Website Migration Project

Introduction

Every day on web development or server-related forums someone asks a question about how to move databases across different servers or server versions. The answers are not straight and simple, but rather diverse and dependent on each particular case. While working with databases, almost every database developer or DBA faces the task to migrate database structure and data (Yarger & Reese: 1999). It is very common in database development life cycle, but still remains challenging. This paper examines the use cases of database migration and challenges that accompany it. The main attention is devoted to solutions used to remove the complexity and bottlenecks of database migration. The paper introduces Devart's Schema and Data Compare tools tailored to facilitate any database migration tasks and reduce time and effort.

Common Cases and Challenges of Migrating Databases

The reasons why one would want to move MySQL databases are different. Here are several most frequent ones:

Moving to a new MySQL Server version

Moving to another server machine (changing a web hosting provider or replacing server equipment)

Moving a local MySQL database to a remote hosting provider server

Creating a copy of a live database and its further support (required for testing new technologies and a new architecture)

Moving to a New MySQL Server Version

This task can be considered in two aspects: moving to a newer server version (upgrading) or some prior version (downgrading). In the first case, the problems may happen as a new server version does not support prior structures or handles them differently (Schneider, 2005). In the second case, the trouble worsens as the prior version may lack not only new structures, but the whole object types (for example, MySQL 4.0 does not support procedures, neither MySQL 5.1.9. supports partitioning) and data types. As a result, simple statements execution may fail or a MySQL server may execute them in an unexpected way. Either outcome is unfavorable. Suppose, your database consists of few objects and you can fix queries to execute them on a new server, but what if you are moving data, and some data types mismatch? It is usually followed by hours and hours of manual and intensive values converting.

Moving to another Server Machine

This commonly happens when changing a web hosting provider or replacing server environment. In the simplest cases, it takes to backup a database and restore it on a new server machine. However, as it happens in the life, what is originally planned as a backup/restore turns into a disaster (Litchfield, 2005). The usual situations during changing a web hosting provider is that a new server is configured in a different mode as the old server, has different default values for some parameters (for example, by default it has InnoDB engine for tables instead of MyISAM one) or lacks the necessary functionality (for example, partitioning support is turned off). To illustrate the case, suppose a backup file was created without a table engine specified, then the default engine will be used while creating the tables, meanwhile all the settings of the prior ...