DevCity.NET - http://devcity.net
SQL Delta : Product Review
http://devcity.net/Articles/207/1/article.aspx
David Jeavons
I have been programming database applications for almost 12 years starting out with VB3 and using every major version since then. I currently work for a retailer analysis company writing ASP.NET code for our ever evolving web site. I also enjoy helping other programmers with Visual Basic and .NET and am a moderator at vbCity. 
by David Jeavons
Published on 3/15/2006
 

SQL Delta is a SQL Server database comparison tool that allows you to analyse databases on the same server or on two different servers.

David Jeavons put it through its paces to assess its usefulness and usability.


Introduction

SQL Delta is a SQL Server database comparison tool that allows you to analyse databases on the same server or on two different servers. Currently (version 3.1) SQL Delta supports both SQL Server 7 and 2000 databases and even allows you to compare an SQL Server 7 database against an SQL Server 2000 database and vice versa. Any functionality differences between the two versions will be ignored by SQL Delta. Support for SQL Server 2005 databases is currently being worked on.

 

Download and Installation

For this review I decided to download the 14 day evaluation version (available here). The download was only 3.3Mb and has no functionality disabled giving you a good opportunity to work with it and see if it meets your needs.

Installation was painless and took less than two minutes. The first time I fired it up, I got the usual trial period screen as to be expected, but what I liked about this was the option to register there and then or to simply trial it, instead of those annoying time delayed start ups and the like. There is also the option to register the product from the File menu which displays the same trial period screen.

 

Setting Up

The first task you need to do when starting SQL Delta for the first time is to specify the connections for both the source and target databases. This is easily accomplished by selecting the Manual Connection tab on the start-up screen and entering the connection details for both databases.

 

 

    You also have the opportunity to specify the options for the comparison between the two databases at this point by selecting the Options button. You can always set these or fine tune them later from the Preferences option under the Options menu. There is a whole host of options available for ensuring that you get the type of comparison that you want, from simple “What do you want to compare” options (tables, stored procedures etc.) to more advanced options such as the ability to ignore comments within stored procedures, and whether or not data comparisons should be case sensitive.

 

Structure Comparisons

Once you have specified your connection options, simply press the Compare Structures button and you will be presented with all objects from both databases and the differences (if any) between them. This is where SQL Delta really shines. The output is presented in two panes, one above the other.

 

 

The top pane shows all objects that have been chosen for comparison and gives you a status for each (identical, missing, additional and different). The bottom pane shows more detailed information for the selected object in the top pane. For example, I compared the Northwind database against a new database (without any objects) and the structure comparison showed all objects that were missing from the target database.

Looking at the detailed view of the Employees table shows 9 tabs each pertaining to a specific area of the table. The first tab displays a summary showing how many columns, keys (primary and foreign); permissions etc. are missing or different. Then each tab thereafter goes into more detail. The Columns tab for example shows two grids, one for the source and the other for the target and details each column that is missing along with their data types and attributes.

The last tab called Script shows the SQL script that is required for both the source and target databases to build the Employees table, create indexes and grant permissions. This also shows the differences between the two scripts. For example, if the only difference was a missing index then the source script would show the creation of the index while the target script would not.

From the list of objects that are not identical, SQL Delta automatically assigns an action against each object. These actions determine what should happen to the object and usually refer to the target database. So still comparing the Northwind database to my blank database, SQL Delta has assigned an action of “Create on (local).myDatabase”, however, you can change the action for individual objects by selecting an appropriate entry from the Action menu.

So if you wanted to drop the Employees table from the Northwind database then simply change the Action to “Drop from (local).Northwind”. When you have gone through all of the differences you then simply press the Script button and SQL Delta will present you with a dialog showing the list of objects that will be acted upon and the script for each object. At this point you can then decide whether you want to save the script for use later or run it immediately. I chose to run the script immediately and was presented with a warning that I should backup my databases before continuing. Of course, I heeded the warning and pressed Ok straight away, but in all seriousness, you should of course back up any important data before running the script. SQL Delta does not have any options for backing up databases due to the differences in organisations on how they manage their backups so it is left to the user to perform these tasks themselves.

Once the script was run, the comparison window was updated and all objects showed as being identical. Being the sceptic that I am, I opened Enterprise manager and confirmed this for myself and was pleasantly surprised.

 

 

Data Comparisons

Much like the Structure Comparison, you can have SQL Delta compare the data in your tables for any differences and missing records. To compare the data you simply press the Compare Data window and SQL Delta presents you with a list of all tables that are available for comparison, of course, if any tables are missing between the two databases then these tables will not be displayed in the list.

SQL Delta uses the primary key of the table for comparison but if your table does not have a primary key or you wish to use a different column or columns then you can define your own “Comparison key”. In fact, if your table does not have a primary key then you must specify your own for SQL Delta to perform the comparison. Once you have selected the tables you wish to compare, you have choices:either compare the data straight away or by pressing the Next button you can enter filter criteria for limiting the number of records that will be compared.This is done by entering a simple WHERE clause (although you do not need to specify the WHERE keyword, for example “YourFieldName=YourValue”).

Once you have selected the tables and entered any filters press the Compare button and SQL Delta will analyse each table. Much like the structure comparison, you will be presented with the tables in the top pane showing how many records are identical, different, missing or additional and selecting a table will show you a grid in the bottom pane with all of the data and each records status.

 

 

You can then look at only different, missing or additional records by selecting one of the appropriate tabs and even select which records should not be updated.

Again, much like the structure comparison you can choose what you want to do for each table. Update target or source database or even choose to export the differences to a CSV file (maybe for further analysis). Once you have decided on the various actions simply press the Update button for SQL Delta to then synchronise the two databases.

 

 

Summary

Pros and Cons

As with most software products there are usually elements that you really like and some that you don’t. What I really liked about SQL Delta was its ease of use. I found it to be a very intuitive piece of software and didn’t need to look into the help documentation at all although I can assure you it is there and is very well laid out.

Another neat little feature that I appreciated was that SQL Delta did actually crash on me once during my testing, but rather than crash and quit, it displayed an Application Problem window with the details of the problem and two further fields for me to fill out. One was my email address and the other was any comments I wished to make. I didn’t actually take the time to fill out the details but after pressing the close button on this window, SQL Delta was still up and running and did not appear to be affected by the problem. I simply refreshed the comparison and all was right with the world. If only other vendors took the time to ensure their software was as user centric as this.

SQL Delta also has a nice little tool known as “Update * Views”. When you create a view using the SQL syntax “Select * FROM …”, SQL Server stores an internal list of columns that are matched to the view. If you then change the structure of the table, the view is not subsequently updated which results in the view failing. SQL Delta will go through all of your views and update them accordingly so that the internal list is synchronised with your views.

My only gripe with the product was its user interface although this does not detract from the usefulness and intuitiveness of the product. Certain dialogues have the same functionality (such as select all/Deselect all objects) either placed at the top of the window or at the bottom rather than always being in the same general location.

Conclusion

If you find that you are often looking for differences between two databases (structure or data) then I would highly recommend SQL Delta. Not only is it fast, accurate and very easy to use, it is also very competitively priced.