Monday, November 2, 2009

Database Schema Comparison

In our announcement of version 2010 last week I mentioned over 70 new features that we have added based on customer feedback. As I was talking with journalists through the course of the week, I came to quickly realize that people automatically assume that if it is 70 features in one release then those must be fairly small features. But in each case my conversation partner got more and more excited, as they realized that we are really talking about over 70 substantial new features.

So let me begin this series of individual feature articles by talking about database schema comparison, which we’ve added to both DatabaseSpy and DiffDog in version 2010.

We had originally introduced database comparison or differencing in version 2009 of our product line, but originally thought that users would primarily want to compare the actual data being stored in two separate databases. And people did indeed like that new feature and used it to synchronize data between production and development servers or between different types of database servers, and they were very excited that we allowed them to match columns with a drag&drop mapping interface in those cases where the tables were not entirely identical. One of the most frequent requests, however, that quickly emerged was to add the ability to also compare different database structures or schemas themselves.

This led to the new Database Schema Comparison feature of version 2010. Just like before the first step for a database schema comparison is to connect to two databases and select the tables that you want to compare:

We do, of course, automatically map any columns that have matching names, but you can also change the mapping and designate that columns are equivalent even if their names differ or follow other naming conventions e.g. due to a server migration.

Once you have connected the right columns and are ready to start the comparison process, click the compare button in the toolbar. The results will be displayed right away, and in this example DatabaseSpy is highlighting the differences with respect to data types as well as columns that are missing:

In addition to visually displaying the differences between the database schemas, DatabaseSpy also allows you to generate a merge script that will synchronize the schemas between the different databases, and you can choose if you want to merge the changes from left to right or vice versa.

Once you have selected one of the options, a new script window opens and shows you the SQL statements needed to make the changes, and you can execute them directly from within DatabaseSpy.

The same process can also be used to compare databases running on different database servers, which is ideal when you want to migrate from one database backend server platform to another.

And this is just one of the over 70 exciting new features in our version 2010 product line…

No comments: