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

Product Information

Web:

http://www.lockwoodtech.com/index_sqldiff.htm
Developer:

LockwoodTech Software
Pricing: $399 (1 Developer License)