Saturday, April 30, 2011

Schema and Data comparison between two databases

I have been looking into an interesting new requirement.

Requirements:
1) Consider there are two host machines - Host_A and Host_B. An older version of our product is installed in Host_A. And a newer version of our product is installed in Host_B. From a Data tier perspective, there are lots of schema differences between old and new version of our product. Also, there could be lots of instance data persisted in the databases of each product installation. I am looking at writing a tool which would migrate instance data from the older instance of the product into the newer instance of the product.
2) In the first test case, the databases in old product instance and new product instance are same. (Homogeneous database comparison for schema and data). Later, this would be extended to consider heterogeneous databases - ie, the database types could be different in the old and new product instances.

Eg: Old product instance uses Oracle database. New product instance uses Microsoft SQL Server.

As a first step, we would require a tool which would do the following:
1) compare schema between older product instance and the newer product instance
2) compare data between older product instance and the newer product instance


I evaluated quite a few products that are currently available. They are DBDiff, Red Gate and TOAD. I am yet to evaluate Golden Gate.

The tools that I have evaluated were great so far. At the end of the comparison, the tools produce a SQL script which could be used to migrate from the old to the new produce instance.

So far, all these tools are Windows tools. That is, they run on Windows platforms. I am looking for Linux tools. If I find anything, I'll update this post.

No comments: