Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Review of SQLDiff by Lockwood

By Christoffer Hedgate,

Introduction

SQLDiff is a tool for analyzing the differences of objects in one SQL Server database to those in another database. It can also compare the data between tables, which according to the developers makes it a unique product. It then lets you view these differences, in a grid in the main application, as an HTML report or by overviewing them in a script for each database. After comparing the differences it can then create a script to merge them together.

Environment

To test SQLDiff I simply created a copy of Northwind and used it on my laptop. I had both databases on the same SQL Server instance, but SQLDiff of course supports comparing databases on different servers. I then made some minor changes to these databases and tested how SQLDiff showed these. SQLDiff runs on most Windows systems (NT4, 2000, XP and 2003), and the only requirements are MDAC (2.6+) and the SQLDMO object library (sqldmo.dll) which is installed with SQL Server client tools.

Installation

Installation was no trouble at all, a standard installation by clicking Next a couple of times and I was done. I downloaded an evaluation version from the product site (link at the end of this review). The version I tried was 2.7.69, but judging from the changelog it seems the product is updated quite often. There is also a built-in menu option to check for updates, but since I already had the latest version I couldn't try it other than checking that just that. The evaluation version is the complete product, there is no functionality that is blocked or anything, but it only works for two weeks. You can purchase a license directly from the product (or the splash screen that welcomes you and tells you that you're using an unregistered version), but it just takes you to the purchase web page of the product site.

Using SQLDiff

After acknowledging that I want to evaluate the product I get to a login dialog (image 1). Except for the fact that there are two servers to enter login information for it is a standard login window that supports both Windows and SQL Server authentication.


Image 1: Login dialog box

Using SQLDiff is pretty straightforward. Although it comes with some documentation you don't really need to read it, at least if you know SQL Server well. The graphical interface is very nice, a modern kind of standard Windows-style, with it's own icons for buttons though. I especially like the icon for the button to show differences in triggers (a pistol), a little humor from the developers I guess. When you've logged in to the server(s) you come to the main window of the application and a dropdown box lists the available databases (system databases are hidden by default) from the first server. As soon as you choose a database from each server a comparison of the two databases starts. This behavior, along with which objects you want to compare by default (image 2), is of course configurable.


Image 2: Objects to compare

The product page at the developers site states that SQLDiff is very fast, much faster than competitors. I haven't done any exact measuring or comparisons, but I can definitely say that I agree, it is very fast. On my laptop a complete comparison takes only seconds, either when I use Northwind and Northwind2 (my copy of Northwind), other larger databases or even two completely different databases. When I tested the functionality to stop an ongoing comparison (just to see what happened) I had to try some times because the process finished before I could click the button. When the comparison is finished a grid that is the central part of SQLDiff shows the result of the comparison (image 3).


Image 3: Grid with results

The grid shows every object from the two databases, grouped by object type. The last three columns describes the status of the object. A check in the Master-column means that the object exists in the first database (I think the term Master database might be confusing), and a check in the Target column means it exists in the second database. Between these columns are a column of icons. An equals sign means that the objects structure is the same in both databases, and a crossed over equals sign means they differ in some way. A blue arrow means that the object will be added to the second database and a red arrow means it will be deleted from it. Note that at the moment SQLDiff only supports merging from Master to Target (but you can of course change which one you want to have as Master and Target), but the list of changes 'coming soon' states that this will change soon.

Double clicking a row in the grid opens up a new window (image 4) showing two create scripts for the object, one for each database side by side. If it doesn't exist in one of the databases that part of the window is of course empty.


Image 4: Comparison of scripts for object

You can use these scripts to fix the difference manually if you want, but this window is mainly for showing what the difference is. Instead you can use SQLDiff to create a merge script that will update the target database so that it is an exact copy of the master database, or just the changes you decide. The first column contains a checkbox, and all objects that you check this box for will be included in the merge script. SQLDiff lets you decide if you want to create the merge script directly in Query Analyzer and connect to the target server, or you can create the script in the included LockwoodTech Editor (image 5), a simple script editor where you can save the script for later execution or copy-paste into QA.


Image 5: LockwoodTech Editor with merge script

Data differences
As I said earlier, SQLDiff not only analyzes structure differences between objects in SQL Server, it can also analyze data differences between two databases. In the Outlook-bar on the left you click Data and a comparison of the data in all tables of the two databases are started. The results are shown in a new grid (image 6).


Image 6: Data differences

I had a little trouble finding out how to work with this part, even after consulting the documentation. Apparently this analysis simply checked the number of rows in each table and presented it in this grid. I knew however that the Customers table had differences, they had the same amount of rows but they each contained one row that did not exist in the other table. And if I checked the box in the first column and then chose to create a merge script, I did get the following statements in the script:

DELETE FROM [dbo].[Customers] WHERE ([CustomerID]='HEDGA')
INSERT INTO [dbo].[Customers] ([CustomerID], [CompanyName], [ContactName], [ContactTitle], [Address], [City], [Region], [PostalCode], [Country], [Phone], [Fax])
VALUES ('TESTT', 'Test Test', 'Test Test', 'Tester', 'Test', 'Test', NULL, '1234', 'Test', '14', NULL)

Finally I found that if I checked the box in the first column and then clicked Compare Data in the left bar, the grid would show some more info (image 7).


Image 7: Data differences 2

Now the grid shows that there are 91 equal rows, one row in the master database that will be added to the target database and finally one row that will be removed from the target database because it does not exist in the master.

Technical Support

When using SQLDiff I did encounter a couple of minor errors. I say minor because none of them had any impact on the functionality of the application, I could do all tests and the important functions, such as creating the merge script, still worked well. The errors where mostly 'cosmetic', for instance in the HTML report it always says that it was created in January, but with the correct day (like 4 Jan 2003 when creating a report 2003-10-04). One thing I really liked was that if a runtime error occurred it was always caught (application didn't crash) and a nice error information window was shown (image 8). From here I can send the error info by email directly to the developers.

 
Image 8: Error information

I didn't use the technical support so I can't say anything about how much help it is, but it seems to be very good. At the product site there is a support forum where all questions and requests are answered by the developers very quickly, and the product information states that support is always free for the product, including future upgrades. Updates seem to be released quite often so I guess any issues reported to the developers will be fixed quickly.

Conclusions

I liked SQLDiff very much. It is easy to use and very fast and have got a couple of extra features that gives it 'that extra'. Although I can't say that I would need it in my everyday job, for one big job where it would help, for instance when updating several servers from a master server, it would quickly pay off. And if you can pay more you can buy SQLDiff as a part of LockwoodTech's SQL Studio and get a nice set of applications that could probably be a real help in your job.

Ratings

Ease of Use 4 Except for the slight trouble I had with the data differences part everything was self-explanatory.
Feature Set 5 Does what it sets out to do, and maybe just a little more. Looking at the 'Coming soon'-list there are also some interesting features to look forward to, such as XML export of reports.
Lack of Bugs 3 Although the product is updated often and bugs seem to be fixed quickly, it also seems to create new bugs in new versions and I did encounter a couple of errors or bugs.
Value 4 The price of a license is not very high, as I said above I think it would pay off pretty quickly if you need it in a project.
Technical Support NA Not tested, but from the way it looks at the support site I think it would easily rate a 4 or even 5 if needed.
Documentation 3 Standard documentation format (.chm), not entirely complete but I didn't find any errors in it.
Performance 5 As good as advertised. It wasn't put to any extreme tests however, but I have no reason to believe that it will not be fast.
Installation 5 Absolutely no problems at all.
Learning Curve 4 No problem starting to work with the application directly without reading any documentation.
Overall 4 Overall a good product that solves the problems it sets out to do, at a reasonable price. There are some issues and missing features but most of these will probably be solved in future version, and lifetime upgrades are included! 

Product Information

Web: http://www.lockwoodtech.com/index_sqldiff.htm
Developer: LockwoodTech Software
Pricing: $399 (1 Developer License)

Total article views: 5897 | Views in the last 30 days: 4
 
Related Articles
FORUM

Scripting Objects Including Permissions

Scripting Objects Including Permissions

FORUM

Generating Scripts for Database Objects

Creating Seperate Scripts Per Object

FORUM

Script to check for last updates in database objects

Script to check for last updates in database objects

FORUM

Generate script for all objects in a database

Generate script for all objects in a database

FORUM

Script difference between two databases

Compare two databases and create script to use to update one database

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones