Thursday, February 5, 2009

Database Diff: Compare and Merge

When I mentioned our v2009 product launch before, I promised to write in more detail about some of the exciting new features we included. One of my favorite new functions that I use almost on a daily basis is actually available in two products, depending on your needs: it is the new database differencing module with full database compare and merge capabilities in both DiffDog 2009 and DatabaseSpy 2009.

Here is how it works: to compare data between different tables you select one or more tables for the "left side" of the comparison and one or more tables for the "right side":

However, unlike straight text file comparisons, where the differencing process would now start right away, for databases we recognize that the table structure is rarely the exact same, because you typically want to compare data between different servers (such as production vs. development) and one may have new columns, indexes, and the like.

So we give you the ability to define in detail how the tables should be compared. The system is, of course, intelligent enough to automatically match column names that just differ by capitalization or column position, but you can visually create a comparison map to match up other columns with this easy-to-use interface:

Once you run the differencing operation based on the comparison map you've defined, the result is displayed in this unique and intuitive table diff result view:

This view gives you the option to show or hide columns that exist in one database only, identical columns, as well as hiding rows that are completely equal or rows that exist only in the database on the left or the right. This allows you to drill down into those differences that you consider essential.

In addition to finding these differences, we do, of course, also allow you to complete the data migration and reconcile those differences:

You can choose to either merge data from left to right, right to left, or you can create a SQL script for manual review that will contain all the commands necessary to compete the merge operation (DatabaseSpy only):

If you are concerned about keeping a record of the data before the merge, you can even create a restore script for either database that lets you revert the changes made by the merge script if you ever need to do that (DatabaseSpy only).

The database diff feature supports any of the following database server platforms:

  • Microsoft® SQL Server® 2000, 2005, 2008
  • Oracle® 9i, 10g, 11g
  • Sybase® 12
  • MySQL® 4, 5
  • PostgreSQL 8
  • IBM DB2® 8, 9; IBM DB2 for iSeries® v5.4; IBM DB2 for zSeries® 8, 9
  • Microsoft Access™ 2003, 2007

And the coolest thing is that you can compare tables between connections to different database servers, which makes it an excellent tool for supporting a migration from one server platform to another that lets you verify that all data was moved properly.

As always, you can download a fully functional 30-day free trial to test this new feature yourself...

No comments: